c# - EF Core 3.1:序列不包含任何元素,使用 FromSqlRaw 查询存储过程
问题描述
我尝试使用 Entity Framework Core 3.1 查询存储过程。我做了一个非常简单的测试项目。
存储过程:
ALTER PROCEDURE [dbo].[prcTest]
AS
BEGIN
SET NOCOUNT ON;
select tbl_Postalcode.PST_T_PostalCode AS Postalcode
from tbl_Postalcode
END
实体:
public class Test
{
string Postalcode { get; set; }
}
我使用在 Stackoverflow 上找到的扩展方法,它稍微封装了整个事情。但没什么特别的:
public static class DbContextExtensions
{
public static IList<T> SqlQuery<T>(this DbContext context, string sql, params object[] parameters) where T : class
{
using (var dbcontext = new ContextForQueryType<T>(context.Database.GetDbConnection()))
{
return dbcontext.Set<T>().FromSqlRaw(sql, parameters).AsNoTracking().ToList();
}
}
public static async Task<IList<T>> SqlQueryAsync<T>(this DbContext context, string sql, params object[] parameters) where T : class
{
using (var dbcontext = new ContextForQueryType<T>(context.Database.GetDbConnection()))
{
return await dbcontext.Set<T>().FromSqlRaw(sql, parameters).AsNoTracking().ToListAsync();
}
}
private class ContextForQueryType<T> : DbContext where T : class
{
private readonly System.Data.Common.DbConnection connection;
public ContextForQueryType(System.Data.Common.DbConnection connection)
{
this.connection = connection;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(connection, options => options.EnableRetryOnFailure());
base.OnConfiguring(optionsBuilder);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<T>().HasNoKey();
base.OnModelCreating(modelBuilder);
}
}
}
然后我执行这样的查询:
var result = await _myContext.SqlQueryAsync<Test>("Exec prcTest");
我得到这个错误:
AggregateException: One or more errors occurred. (Sequence contains no elements)
System.Threading.Tasks.Task.ThrowIfExceptional(bool includeTaskCanceledExceptions)
InvalidOperationException: Sequence contains no elements
System.Linq.ThrowHelper.ThrowNoElementsException()
System.Threading.Tasks.Task.ThrowIfExceptional(bool includeTaskCanceledExceptions)
System.Threading.Tasks.Task<TResult>.GetResultCore(bool waitCompletionNotification)
System.Threading.Tasks.Task<TResult>.get_Result()
System.Text.Json.JsonPropertyInfoCommon<TClass, TDeclaredProperty, TRuntimeProperty, TConverter>.GetValueAsObject(object obj)
System.Text.Json.JsonSerializer.HandleEnumerable(JsonClassInfo elementClassInfo, JsonSerializerOptions options, Utf8JsonWriter writer, ref WriteStack state)
System.Text.Json.JsonSerializer.Write(Utf8JsonWriter writer, int originalWriterDepth, int flushThreshold, JsonSerializerOptions options, ref WriteStack state)
System.Text.Json.JsonSerializer.WriteAsyncCore(Stream utf8Json, object value, Type inputType, JsonSerializerOptions options, CancellationToken cancellationToken)
Microsoft.AspNetCore.Mvc.Formatters.SystemTextJsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
Microsoft.AspNetCore.Mvc.Formatters.SystemTextJsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|29_0<TFilter, TFilterAsync>(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext<TFilter, TFilterAsync>(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
谁能告诉我具体问题是什么?
解决方案
找到了解决方案。错误消息没有帮助而且非常混乱。
解决方案:
公开财产。这很明显,但错误消息让我感到困惑。
public class Test
{
public string Postalcode { get; set; }
}
推荐阅读
- python - 是否可以在linux中模拟串口的响应?
- docker - 为独立容器创建覆盖网络时出错
- javascript - 使用多个组件重用导航栏组件
- ios - Flutter video_player 插件使人像相机视频静音
- python - Pygame 创建无限数量的敌人
- c# - Post 方法中的映射无法正常工作
- javascript - 点击浏览器后退按钮时如何阻止 React 组件在加载时重新获取数据
- flutter - DateTime 类参数应该是 const 但 DateTime 不支持这个
- android - 如何从 Firestore 云或实时数据库中检索数据,如自定义排序
- javascript - 如何使用 JQuery 实现星级评分