Not able to retrieve data from stored procedure

Question

Hi I need to get a List that is being returned by a stored procedure from DBContext. But I am getting the error below:

System.InvalidOperationException: The required column 'VehicleFilterId' was not present in the results of a 'FromSql' operation

Below is the Controller:

 [HttpGet("reportFilterValues/{vehicleId}/{vehicleName}")]
  public async Task<ActionResult> GetVehicleValues(string vehicleId, string vehicleName)
  {            
     var vehicleReportValues = -vehicleDataAccess.GetVehicleReportHelper(vehicleId, vehicleName);
     return Ok(vehicleReportValues);
  }         

DataAccessLayer:

public class VehicleDataAccess : IVehicleDataAccess {

    private readonly IConfiguration _configuration;
    private readonly IServiceProvider _provider;
    public ReportDataAccess(IServiceProvider provider, IConfiguration configuration)
        {
            _provider = provider;
            _configuration = configuration;
        }
    
      private readonly IConfiguration _configuration;       
      public async Task<List<VehicleFilter>> GetVehicleReportHelper(string vehicleId, string vehicleName)
        {
            int vehicleNumericId = 100;
            string vehicleTenant = "Kia";            
            
            using var scope = _provider.CreateScope();
            var dbContext = new DbContext(vehicleNumericId, vehicleTenant, _configuration);
            
            reportFilterValues = await dbContext.GetVehicleKeyValues(vehicleName, dbContext);                 
                        
            return reportFilterValues;
        }
    }
    

DbContext Class: The SQL query in the class below works fine in SSMS. But its giving the error stated above here.

   public partial class VehicleDbContext : DbContext { 

 public virtual DbSet<ReportFilter> ReportFilters { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<VehicleFilter>(entity =>
            {
                entity.HasKey(e => new { vehicleFilterId = e.VehicleFilterId });
                entity.Property(e => e.VehicleId).HasColumnType("int").IsRequired();
                entity.Property(e => e.VehicleFilterName).HasMaxLength(255).IsRequired();
                entity.Property(e => e.VehicleFilterTitle).HasMaxLength(255).IsRequired();
                entity.Property(e => e.SchemaEntityId).HasColumnType("int").IsRequired();
                entity.Property(e => e.ModelTableColumn).HasMaxLength(255).IsRequired();
                entity.Property(e => e.ModelTableColumn).HasMaxLength(255).IsRequired();
                entity.Property(e => e.VehicleFilterType).HasMaxLength(255).IsRequired();
            });

            modelBuilder.Entity<Vehicles>(entity =>
            {
                entity.HasKey(e => new { vehicleId = e.VehicleId });
                entity.Property(e => e.VehicleType).HasMaxLength(255);
                entity.Property(e => e.ParametersEnabled).HasColumnName("ParametersEnabled");
            });

            OnModelCreatingPartial(modelBuilder);
        }


 public async Task<List<ReportFilter>> GetVehicleKeyValues(string vehicleName, VehicleDbContext vehicleDbContext)
 {                
    var sqlCommand = $"EXEC [CCAdmin].[spName] @vehicleName = '{VehicleName}'";
    var vehicleFilterValues = await vehicleDbContext.VehicleFilters.FromSqlRaw(sqlCommand).ToListAsync();
    return vehicleFilterValues;
 }      
}

Model:

[Table("VehicleFilters", Schema = "ABC")]
    public class VehicleFilter
    {
        public int VehicleFilterId { get; set; }

        public int VehicleId { get; set; }

        public string VehicleFilterName { get; set; }

        public string VehicletFilterTitle { get; set; }

        public int VehicleEntityId { get; set; }

        public string ModelTableName { get; set; }

        public string ModelTableColumn { get; set; }

        public string VehicleFilterType { get; set; }

        public VehicleFilter(VehicleFilterModel vehicleFilterModel)
        {
            VehicleFilterId = vehicleFilterModel.VehicleFilterId;
            VehicleId = vehicleFilterModel.VehicleId;
            VehicleFilterName = vehicleFilterModel.ReportFilterName;
            VehicleTitle = vehicleFilterModel.VehicleTitle;
            VehicleEntityId = vehicleFilterModel.VehicleEntityId;
            ModelTableName = vehicleFilterModel.ModelTableName;
            ModelTableColumn = vehicleFilterModel.ModelTableColumn;
            VehicleFilterType = vehicleFilterModel.VehicleFilterType;
        }    
        public ReportFilter()
        {
            
        }
    }

The

Can anyone please help me out? Thanks!

Answer 1

"I need to get a List that is being returned by a stored procedure from DBContext. But I am getting the error below:?"

System.InvalidOperationException: The required column 'VehicleFilterId' was not present in the results of a 'FromSql' operation

Yes its because you have define your VehicleFilter class with VehicleFilterId and probably you are not returning this property from your store procedure as you might know when use FromSqlRaw in EF you must return the domain class (VehicleFilters) property value which is non nullable

Another problem is your syntax for interpolated string ($"") "var sqlCommand = $"EXEC [CCAdmin].[spName] @vehicleName = '{VehicleName}'" which is not correct

As you might knwo while use interpolated string ($"") to pass parameter in store procedure you do not need to use @vehicleName this pattern which we use in SQL. You can check the official docs for how you can write the syntax accordingly here. You should try like below:

string VehicleName = "Hatchback";
var sqlCommand = $"EXEC GetVehicleByTile {VehicleName}";
var vehicleFilterValues = await _context.VehicleFilter.FromSqlRaw(sqlCommand).ToListAsync();

Demo SQL Store Procedure:

CREATE PROCEDURE [dbo].[GetVehicleByTile]
(
    
    @VehicletFilterTitle nvarchar(150)
)
AS

BEGIN

    SELECT  VehicleFilterId, VehicleId, VehicleFilterName, VehicletFilterTitle
    FROM VehicleFilter
    WHERE VehicletFilterTitle = @VehicletFilterTitle

END

EXEC GetVehicleByTile 'Hatchback'

Controller:

        [HttpGet]
        public async Task<IActionResult> GetAllFilter()
        {
            string VehicleName = "Hatchback";
            var sqlCommand = $"EXEC GetVehicleByTile {VehicleName}";
            var vehicleFilterValues = await _context.VehicleFilter.FromSqlRaw(sqlCommand).ToListAsync();
            return Ok(vehicleFilterValues);
        }

Final Output:

Note: Make sure the column you have defined at your DbSet<VehicleFilter> is provided from the store procedure accordingly.Other than you might be above error.