Executing an SP in EF Core

问题:

I'm trying to execute an sp in ef core.

ALTER PROCEDURE [dbo].[usp_get_counts_for_event_type_for_single_date]
    @OrgCode varchar(5),
    @ProcessDate date
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @StartTime time = '00:00:00'
    DECLARE @EndTime time = '23:59:59'

    DECLARE @PeriodStart datetime = CONVERT(datetime, @ProcessDate) + CONVERT(datetime, @StartTime)
    DECLARE @PeriodEnd datetime = CONVERT(datetime, @ProcessDate) + CONVERT(datetime, @EndTime)

    -- Insert statements for procedure here
    SELECT CONVERT(VARCHAR(100), et.DisplayName) as Title, 
            et.DashboardColour as Colour,
            count(et.EventTypeId) as Count
    from EventType et
    join EventLog el on et.EventTypeId = el.EventTypeId
    WHERE el.StartTime BETWEEN @PeriodStart AND @PeriodEnd
    group by et.DisplayName, et.DashboardColour
END

I have defined a data model class for the sp results.

public class GroupedCountResult
{
    [Key]
    [Column("varchar(100)")]
    public string Title { get; set; }

    [Column("varchar(20)")]
    public string Colour { get; set; }

    [Required]
    public int Count { get; set; }
}

And then in DbContext.cs It's been added as a DbSet in the context

public  DbSet<GroupedCountResult> GroupedCountResults { get; set; }

Then I try to execute it in an Core 2.2 MVC Controller. This is my weak point. I'm trying to chop the results up into strings that are then used in javascript for a pie chart. Hardcoded values until I get this working.

public async Task<ActionResult> Index()
{
    StringBuilder sbLabels = new StringBuilder();
    StringBuilder sbColours = new StringBuilder();
    StringBuilder sbCounts = new StringBuilder();
    string OrgCode = "HVO";
    DateTime ProcessDate = new DateTime(2019, 08, 01); //.ToString("yyyyMMdd");

    IEnumerable<GroupedCountResult> results =  await _context.GroupedCountResults
                                .FromSql($"usp_get_counts_for_event_type_for_single_date @p0, @p1", OrgCode, ProcessDate)
                                .ToListAsync();

    foreach(GroupedCountResult result in results) <--exception here
    {
        sbLabels.AppendFormat("'{0}',", result.Title);
        sbColours.AppendFormat("'{0}',", result.Colour);
        sbCounts.AppendFormat("{0},", result.Count);
    }

    ViewBag.Labels = sbLabels.ToString().TrimEnd(',');
    ViewBag.Colours = sbColours.ToString().TrimEnd(',');
    ViewBag.Counts = sbCounts.ToString().TrimEnd(',');
    return View();
}

From the logs. I preferred the string interpolation version, but this seems closer to working.

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (31ms) [Parameters=[@p0='?' (Size = 4000), @p1='?' (DbType = DateTime2)], CommandType='Text', CommandTimeout='30']
usp_get_counts_for_event_type_for_single_date @p0, @p1

I've worked through a few exceptions. It's now throwing an exception after the execution, implying there's something wrong with the results.

Microsoft.EntityFrameworkCore.Query:Error: An exception occurred while iterating over the results of a query for context type 'Ctrack.Dashboard.Data.DbContext'.
System.InvalidOperationException: The required column 'varchar(100)' was not present in the results of a 'FromSql' operation.
   at Microsoft.EntityFrameworkCore.Query.Sql.Internal.FromSqlNonComposedQuerySqlGenerator.CreateValueBufferFactory(IRelationalValueBufferFactoryFactory relationalValueBufferFactoryFactory, DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.ShaperCommandContext.<NotifyReaderCreated>b__14_0(FactoryAndReader s)
   at Microsoft.EntityFrameworkCore.Internal.NonCapturingLazyInitializer.EnsureInitialized[TParam,TValue](TValue& target, TParam param, Func`2 valueFactory)
   at Microsoft.EntityFrameworkCore.Query.Internal.ShaperCommandContext.NotifyReaderCreated(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
   at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator`2.MoveNextCore(CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\Select.cs:line 106
   at System.Linq.AsyncEnumerable.AsyncIterator`1.MoveNext(CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\AsyncIterator.cs:line 98
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)


System.InvalidOperationException: The required column 'varchar(100)' was not present in the results of a 'FromSql' operation.
   at Microsoft.EntityFrameworkCore.Query.Sql.Internal.FromSqlNonComposedQuerySqlGenerator.CreateValueBufferFactory(IRelationalValueBufferFactoryFactory relationalValueBufferFactoryFactory, DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.ShaperCommandContext.<NotifyReaderCreated>b__14_0(FactoryAndReader s)
   at Microsoft.EntityFrameworkCore.Internal.NonCapturingLazyInitializer.EnsureInitialized[TParam,TValue](TValue& target, TParam param, Func`2 valueFactory)
   at Microsoft.EntityFrameworkCore.Query.Internal.ShaperCommandContext.NotifyReaderCreated(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
   at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator`2.MoveNextCore(CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\Select.cs:line 106
   at System.Linq.AsyncEnumerable.AsyncIterator`1.MoveNext(CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\AsyncIterator.cs:line 98
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
Exception thrown: 'System.InvalidOperationException' in System.Private.CoreLib.dll
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker:Information: Executed action Ctrack.Dashboard.Controllers.HomeController.Index (Ctrack.Dashboard) in 1375.9283ms
Microsoft.AspNetCore.Routing.EndpointMiddleware:Information: Executed endpoint 'Ctrack.Dashboard.Controllers.HomeController.Index (Ctrack.Dashboard)'
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware:Error: An unhandled exception has occurred while executing the request.

Has it lost track of the column name? I've tried converting the column to varchar(100). All the info on executing sp's implied it would be easier than this.

回答1:

The problem lies in your model Data Annotation.If you would like to specify an exact data type for a column, you need to use [Column(TypeName = "varchar(100)")] instead of [Column("varchar(200)")] which will map the column name not column type

public class GroupedCountResult
{
    [Key]
    [Column(TypeName ="varchar(100)")]
    public int Title { get; set; }

    [Column(TypeName = "varchar(20)")]
    public string Colour { get; set; }
}

Refer to

https://docs.microsoft.com/en-us/ef/core/modeling/relational/data-types

https://docs.microsoft.com/en-us/ef/core/modeling/relational/columns

回答2:

Here is how i am calling my stored procedure in .net core

public class DbUtilities
{
    private readonly ILogHandler _oisLogger;
    private readonly SB1_VVFContext _context;

    public DbUtilities(ILogHandler oislogger, SB1_VVFContext context)
    {
        _oisLogger = oislogger;
        _context = context;
    }

    public IEnumerable<VehicleSearchResultDTO> SearchVehicleRecords(VehicleSearchCriteriaDTO Criteria)
    {
        List<VehicleSearchResultDTO> result = new List<VehicleSearchResultDTO>();
        VehicleSearchResultDTO vehRec = null;

        try
        {
            //Define Command Object
            _context.Database.OpenConnection();
            DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();

            SqlParameter refnum = new SqlParameter("@RefNum", SqlDbType.VarChar, 10)
            {
                Direction = ParameterDirection.Input,
                Value = (!string.IsNullOrWhiteSpace(Criteria.RefNum)) ? Criteria.RefNum : string.Empty
            };

            //Define Input parameters
            SqlParameter vin = new SqlParameter("@Vin", SqlDbType.VarChar, 100)
            {
                Direction = ParameterDirection.Input,
                Value = (!string.IsNullOrWhiteSpace(Criteria.Vin)) ? Criteria.Vin : string.Empty
            };

            SqlParameter owner = new SqlParameter("@Owner", SqlDbType.VarChar, 200)
            {
                Direction = ParameterDirection.Input,
                Value = (!string.IsNullOrWhiteSpace(Criteria.RegisteredOwner)) ? Criteria.RegisteredOwner : string.Empty
            };

            SqlParameter plate = new SqlParameter("@Plate", SqlDbType.VarChar, 10)
            {
                Direction = ParameterDirection.Input,
                Value = (!string.IsNullOrWhiteSpace(Criteria.Plate)) ? Criteria.Plate : string.Empty
            };

            SqlParameter email = new SqlParameter("@Email", SqlDbType.VarChar, 200)
            {
                Direction = ParameterDirection.Input,
                Value = (!string.IsNullOrWhiteSpace(Criteria.Email)) ? Criteria.Email : string.Empty
            };

            SqlParameter VehicleReviewStatusId = new SqlParameter("@VehicleReviewStatusId", SqlDbType.Int)
            {
                Direction = ParameterDirection.Input,
                Value = Criteria.VehicleReviewStatusId
            };

            //Define properties for command object and execute the SP
            cmd.CommandText = "usp_Vehicle_Search_Result";
            cmd.Parameters.Add(refnum);
            cmd.Parameters.Add(vin);
            cmd.Parameters.Add(owner);
            cmd.Parameters.Add(plate);
            cmd.Parameters.Add(email);
            cmd.Parameters.Add(VehicleReviewStatusId);
            cmd.CommandTimeout = 10;
            cmd.CommandType = CommandType.StoredProcedure;

            DbDataReader reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                //Get Search Result
                while (reader.Read())
                {
                    vehRec = new VehicleSearchResultDTO
                    {
                        VehicleID = (Guid)reader["VehicleId"],
                        ReferenceNumber = reader["ReferenceNumber"].ToString(),
                        SubmitterEmail = reader["EmailAddress"].ToString(),
                        Status = reader["StatusName"].ToString(),
                        SubmittedDate = CheckNull<DateTime?>(reader["DateSubmitted"]),
                        VIN = reader["VIN"].ToString(),
                        Year = CheckNull<int>(reader["Year"]),
                        Make = reader["Make"].ToString(),
                        LicensePlate = reader["LicensePlate"].ToString(),
                        ROName = reader["ROName"].ToString(),
                        StatusColor = reader["StatusColor"].ToString()
                    };
                    result.Add(vehRec);
                }
            }
            else
            {
                result = null;
            }
        }
        catch (Exception ex)
        {
            _oisLogger.LogError(2205, $"ERROR SearchVehicleRecords", ex);
        }
        finally
        {
            _context.Database.CloseConnection();
        }
        return result;
    }

Here is the input object:

public class VehicleSearchCriteriaDTO
{
    public VehicleSearchCriteriaDTO()
    {
    }
    [Display(Name = "Ref#")]
    public string RefNum { get; set; }

    [Display(Name = "VIN")]
    public string Vin { get; set; }

    [Display(Name = "Reg Owner")]
    public string RegisteredOwner { get; set; }

    [Display(Name = "Plate")]
    public string Plate { get; set; }

    [Display(Name = "Email")]
    public string Email { get; set; }

    public int VehicleReviewStatusId { get; set; }
}

Here is the output object:

 public class VehicleSearchResultDTO
{
    [Display(Name = "Owner")]
    public string ROName { get; set; }

    [Display(Name = "RefNum")]
    public string ReferenceNumber { get; set; }

    [Display(Name = "VIN")]
    public string VIN { get; set; }

    [Display(Name = "Plate")]
    public string LicensePlate { get; set; }

    [Display(Name = "Year")]
    public int Year { get; set; }

    [Display(Name = "Make")]
    public string Make { get; set; }

    [Display(Name = "Submitter Email")]
    public string SubmitterEmail { get; set; }

    [Display(Name = "Submitted Date")]
    public DateTime? SubmittedDate { get; set; }

    [Display(Name = "Status")]
    public string Status { get; set; }

    public Guid VehicleID { get; set; }
    public string StatusColor { get; set; }
}

I did not add these classes to the dbcontext or the proc to the dbcontext. Also, i would suggest you comment your code out to only fetch one result variable at a time. See if the mapping to the object from the SP call works well, then add another variable/parameter.