我正在尝试在 ef core 中执行 sp。
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
我已经为 sp 结果定义了一个数据模型类。
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; }
}
然后在DbContext.cs中，它被添加为上下文中的数据库集
public DbSet<GroupedCountResult> GroupedCountResults { get; set; }
然后我尝试在Core 2.2 MVC控制器中执行它。 这是我的弱点。 我正在尝试将结果切成字符串，然后在javascript中用于饼图。 硬编码值，直到我开始工作。
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();
}
从日志中。我更喜欢字符串内插版本，但这似乎更接近工作。
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
我已经处理了一些例外。 它现在在执行后抛出异常，暗示结果有问题。
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:a1sIx.NETSourceSystem.Interactive.AsyncSelect.cs:line 106
at System.Linq.AsyncEnumerable.AsyncIterator`1.MoveNext(CancellationToken cancellationToken) in D:a1sIx.NETSourceSystem.Interactive.AsyncAsyncIterator.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:a1sIx.NETSourceSystem.Interactive.AsyncSelect.cs:line 106
at System.Linq.AsyncEnumerable.AsyncIterator`1.MoveNext(CancellationToken cancellationToken) in D:a1sIx.NETSourceSystem.Interactive.AsyncAsyncIterator.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.
它是否忘记了列名称？ 我尝试将列转换为 varchar(100(。有关执行 sp 的所有信息都暗示它会比这更容易。
问题出在模型数据注释上。如果要为列指定确切的数据类型，则需要使用 [Column(TypeName = "varchar(100)")] 而不是[Column("varchar(200)")]，后者将映射列名而不是列类型
public class GroupedCountResult
{
[Key]
[Column(TypeName ="varchar(100)")]
public int Title { get; set; }
[Column(TypeName = "varchar(20)")]
public string Colour { get; set; }
}
指
https://learn.microsoft.com/en-us/ef/core/modeling/relational/data-types
https://learn.microsoft.com/en-us/ef/core/modeling/relational/columns
以下是我在 .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;
}
下面是输入对象：
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; }
}
下面是输出对象：
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; }
}
我没有将这些类添加到数据库上下文或过程添加到数据库上下文中。另外，我建议您注释掉代码，一次只获取一个结果变量。 查看从 SP 调用到对象的映射是否正常工作，然后添加另一个变量/参数。