调用MSSQL存储过程返回空数组问题求助

本地MSSQL存储过程在SSMS执行能返回1条记录，但通过Swagger调用时代码进入catch块返回空数组，报错信息如下：

System.InvalidOperationException: Sequence contains no elements
at System.Linq.ThrowHelper.ThrowNoElementsException()
at System.Linq.Enumerable.MaxInteger[T](IEnumerable1 source) at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression) at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block) at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node) at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitExtension(Expression extensionExpression) at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.ProcessShaper(Expression shaperExpression, RelationalCommandCache& relationalCommandCache, IReadOnlyList1& readerColumns, LambdaExpression& relatedDataLoaders, Int32& collectionId)
at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitShapedQuery(ShapedQueryExpression shapedQueryExpression)
at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.VisitExtension(Expression extensionExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitExtension(Expression extensionExpression)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<c__DisplayClass12_01.<ExecuteAsync>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetAsyncEnumerator(CancellationToken cancellationToken) at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable1.GetAsyncEnumerator()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at Project.Infrastructure.Repositories.SupplierDashboardRepository.GetORdersListAsync(String externalUID) in C:.path\Project.Infrastructure\Repositories\SupplierDashboardRepository.cs:line at Project.Application.Services.Supplier.Analytics.SupplierDashboardService.GetSubscriptionsListAsync(String externalUID) in C:.path\Project.Aplication\Services\Supplier\Analytics\SupplierDashboardService.cs:line 55 at Project.WebApi.Controllers.Supplier.Dashboard.General.SubscriptionController.GetSubscriptionsListAsync(String externalUID) in C:.path\Project\Controllers\Supplier\Dashboard\General\SubscriptionController.cs:line 31 at lambda_method5(Closure, Object) at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

请求头信息 #

Accept: text/plain 
Accept-Encoding: gzip, deflate, br
Accept-Language: en-US,en 
Connection: close 
Host: localhost:44385
Referer: https://localhost:44385/swagger/index.html 
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36 
sec-ch-ua: "Chromium";v="118", "Brave";v="118", "Not=A?Brand";v="99"
sec-ch-ua-mobile: ?0 
sec-ch-ua-platform: "Windows" 
sec-gpc: 1
sec-fetch-site: same-origin 
sec-fetch-mode: cors 
sec-fetch-dest: empty

相关代码 #

Repository层 #

public class AnalyticsRepository : IAnalyticsRepository
{
    public AnalyticsRepository(GuacamoleContext context)
    {
        Context = context;
    }

    public async Task<List<AnalyticsOrder>> GetOrderListAsync(string externalUID)
    {
        try
        {
            var OrderList = await Context.AnalyticsOrders.FromSqlInterpolated($"EXEC usp__OrderList @externalUID = {externalUID}").ToListAsync();

            return OrderList;

        }
        catch (Exception e)
        {
            Console.WriteLine($"Error {e.Message}");
            return new List<AnalyticsOrder>();
        }
    }

    public GuacamoleContext Context { get; }
}

Service层 #

public class SupplierAnalyticsService : ISupplierAnalyticsService
{
    private readonly ISupplierAnalyticsRepository _supplierAnalyticsRepository;

    public SupplierAnalyticsService(ISupplierAnalyticsRepository supplierAnalyticsRepository)
    {
        _supplierAnalyticsRepository = supplierAnalyticsRepository;
    }

    public async Task<List<AnalyticsOrder>> GetOrdersListAsync(string externalUID)
    {
        return await _supplierAnalyticsRepository.GetOrderListAsync(externalUID);
    }
}

Controller层 #

public class OrdersListController : ControllerBase
{
    private readonly ISupplierAnalyticsService _supplierAnalyticsService;

    public OrdersListController(ISupplierAnalyticsService supplierAnalyticsService)
    {
        _supplierAnalyticsService = supplierAnalyticsService;
    }

    [HttpGet("analytics/orders/list")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status401Unauthorized)]
    [ProducesResponseType(StatusCodes.Status403Forbidden)]
    [ProducesResponseType(StatusCodes.Status413PayloadTooLarge)]
    [ProducesResponseType(StatusCodes.Status500InternalServerError)]
    public async Task<ActionResult<List<AnalyticsOrder>>> GetOrdersListAsync(string externalUID)
    {
        var list = await _supplierAnalyticsService.GetOrdersListAsync(externalUID);

        return Ok(list);
    }
}

DbContext配置 #

modelBuilder.Entity<AnalyticsOrder>().HasNoKey().ToView(null);

核心原因 #

报错里的Sequence contains no elements指向EF Core处理存储过程返回结果时，对空序列执行Max操作失败。主要问题出在：

1. 无键实体配置错误：HasNoKey().ToView(null)的方式会让EF Core无法正确识别存储过程的结果集结构，导致解析失败触发异常；
2. 参数传递可能存在问题：Swagger调用时传入的externalUID可能与SSMS测试值不一致（比如空值、大小写差异），导致存储过程实际返回空结果，进而触发EF Core解析异常。

解决步骤 #

1. 修正无键实体配置
   在AnalyticsOrder类上添加[Keyless]特性，并简化DbContext配置，确保实体属性与存储过程返回字段完全匹配（名称、类型一致）：

   [Keyless]
   public class AnalyticsOrder
   {
       // 确保属性名称、类型与存储过程返回字段完全一致
       public string OrderId { get; set; }
       // 其他属性...
   }
   
   // DbContext中配置改为
   modelBuilder.Entity<AnalyticsOrder>().HasNoKey();
   

2. 改用FromSqlRaw显式传递参数
   避免FromSqlInterpolated的参数解析问题，使用SqlParameter明确传递参数：

   public async Task<List<AnalyticsOrder>> GetOrderListAsync(string externalUID)
   {
       try
       {
           var parameter = new SqlParameter("@externalUID", externalUID);
           var OrderList = await Context.AnalyticsOrders
               .FromSqlRaw("EXEC usp__OrderList @externalUID", parameter)
               .ToListAsync();
           return OrderList;
       }
       catch (Exception e)
       {
           Console.WriteLine($"Error: {e.Message}, Inner Exception: {e.InnerException?.Message}");
           return new List<AnalyticsOrder>();
       }
   }
   

3. 添加参数校验
   在Controller层校验externalUID有效性，避免空值或无效参数传入：

   public async Task<ActionResult<List<AnalyticsOrder>>> GetOrdersListAsync(string externalUID)
   {
       if (string.IsNullOrWhiteSpace(externalUID))
       {
           return BadRequest("externalUID参数不能为空");
       }
       var list = await _supplierAnalyticsService.GetOrdersListAsync(externalUID);
       return Ok(list);
   }
   

4. 调试验证执行SQL
   在Repository的catch块打印完整异常信息和执行的SQL语句，确认EF Core实际执行的SQL与SSMS测试语句一致：

   catch (Exception e)
   {
       Console.WriteLine($"Error: {e.Message}, Inner Exception: {e.InnerException?.Message}");
       Console.WriteLine($"Executed SQL: EXEC usp__OrderList @externalUID = '{externalUID}'");
       return new List<AnalyticsOrder>();
   }
   

内容的提问来源于stack exchange，提问作者cdrrr