Я пытаюсь выполнить sp в ядре ef.
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
он был добавлен как DbSet в контексте
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:\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.
Он потерял имя столбца? Я попытался преобразовать столбец в varchar (100). Вся информация о выполнении sp подразумевала, что это будет проще, чем это.