I am trying to execute a stored procedure against a local MSSQL server but I am getting an empty array as catched by the catch
block although when executed in SSMS
I get one record returned.
The error I get
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, IReadOnlyList 1& 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.<ExecuteAsyncb__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.<InvokeActionMethodAsyncg__Awaited|12_0(ControllerActionInvoker invoker, ValueTask1 actionResultValueTask) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsyncg__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.<InvokeFilterPipelineAsyncg__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsyncg__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsyncg__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invokeg__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)
HEADERS 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
This is my method within the 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; }
}
This is my method within the 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);
}
}
This is my 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);
}
In the DbContext, I've added a new entity and set it not to have a key as per Microsoft docs here :
modelBuilder.Entity<AnalyticsOrder>().HasNoKey().ToView(null);
What am I missing here? Why am I getting an empty array when execute the stored procedure
via Swagger? Thanks
1条答案
按热度按时间oknwwptz1#
Sooo, after some time, came across and found the solution.
Initially, I set in the
DbContext
the entity as it follows:What's wrong here? Nothing, UNLESS you want to map it to a
view
. To map theentity
to astored procedure
you need to use.HasNoKey().ToFunction("[dbo].[YourStoredProcedureName]")
.Moving further,
EF Core
was creating a table with every migration although I specifically set it to have no key. To overpass it, I've set in theDbContext
to be excluded from the migrations. The complete code line is:Last but not least, the return type MUST have class props. So, define the model properly:
And to make sure that
EF Core
fully understands, I also included the fully qualified name of thestored procedure
when call it from the method:I hope that this solution would save you some time!