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:

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

What's wrong here? Nothing, UNLESS you want to map it to a view . To map the entity to a stored 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 the DbContext to be excluded from the migrations. The complete code line is:

modelBuilder.Entity<YourType>().HasNoKey().ToFunction("[dbo].[YourStoredProcedureName]").Metadata.SetIsTableExcludedFromMigrations(true);

Last but not least, the return type MUST have class props. So, define the model properly:

public class Model
{
    public int code { get; set; }
    public string title { get; set; }
    ....................................
}

And to make sure that EF Core fully understands, I also included the fully qualified name of the stored procedure when call it from the method:

public async Task<List<Type>> GetListAsync(string externalUID)
    {
        try
        {
            var xList = await Context.DbSetType
                .FromSqlInterpolated($"EXEC [dbo].[YourStoredProcedureName] @externalUID = {externalUID}")
                .ToListAsync();

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

I hope that this solution would save you some time!

赞(0）回复(0）举报 2023-11-16

Empty array returned when executing stored procedure against MSSQL server

1条答案

相关问题

热门标签

最新问答