CODEWITHSUNDEEP
c#sql-serverasp.net-web-api
GrillOwner69420
GrillOwner69420

Reputation: 825

ASP.Net Core Web API how to call a stored procedure and return 1 result object

I have an ASP.Net Core Web API project in which I access an SQL Server database. This is one of my API endpoints that works that returns a single result object:

GetPlayer()

 // GET: api/Players/5
        [HttpGet("{id}")]
        public async Task<ActionResult<Player>> GetPlayer(int id)
        {
            var player = await _context.Players.FindAsync(id);

            if (player == null)
            {
                return NotFound();
            }

            return player;
        }

This one returns a list of all players using a stored procedure:

GetPlayers()

  // GET: api/Players
        [HttpGet("proc")]
        public async Task<ActionResult<IEnumerable<Player>>> GetPlayersProc()
        {
            return await _context.Players.FromSqlRaw("exec GetAllPlayers").ToListAsync();
        }

How could I rewrite the first one with a stored procedure?

EDIT:

Based on the first answer I am now trying this:

GetPlayerWithSP()

       // GET: api/Players/5
        [HttpGet("{id}")]
        public async Task<ActionResult<Player>> GetPlayerInfo(int id)
        {
            var player = await _context.Players.FindAsync(id);

            if (player == null)
            {
                return NotFound();
            }

            //return player;
            var ID = new SqlParameter("@ID", id);

            return (await _context.Players.FromSqlRaw("exec GetOnePlayerInfo @ID",
            parameters: new[] { ID }).ToListAsync()).FirstOrDefault();
        }

But when I try this I get this error:

System.ArgumentException: An item with the same key has already been added. Key: PlayerID
   at System.Collections.Generic.Dictionary`2.TryInsert(TKey key, TValue value, InsertionBehavior behavior)
   at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
   at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.BuildIndexMap(IReadOnlyList`1 columnNames, DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, 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.FromSqlQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at CyberpunkAPI.Controllers.PlayersController.GetPlayerInfo(Int32 id) in C:\Users\e096752\Documents\Cole's Git Repos\CyberpunkAPI\CyberpunkAPI\Controllers\PlayersController.cs:line 74
   at lambda_method52(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>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>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.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.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

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

This is the stored proc GetOnePlayerInfo

GetOnePlayerInfo

USE [cyberpunk]
GO
/****** Object:  StoredProcedure [dbo].[GetOnePlayerInfo]    Script Date: 8/19/2021 11:35:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[GetOnePlayerInfo] @ID int
AS 
SELECT Player.*, Stats.*, Special_Abilities.*
FROM Player
LEFT OUTER JOIN Stats ON Player.ID=Stats.PlayerID
LEFT OUTER JOIN Special_Abilities ON Player.ID=Special_Abilities.PlayerID
WHERE ID = @ID;

Player.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

#nullable disable

namespace CyberpunkAPI.Models
{
    public partial class Player
    {
        public int Id { get; set; }
        [Required]
        public string Handle { get; set; }
        [Required]
        public string Role { get; set; }
        public string Avatar { get; set; }

        public virtual SpecialAbilities SpecialAbilities { get; set; }
        public virtual Stats Stats { get; set; }
    }
}

Upvotes: 1

Views: 2486

Answers (1)

Serge
Serge

Reputation: 43860

You will have to create a special class for your sp result, it should include ALL data properties that are selected in your sp

[NotMapped]
public class SpPlayerResult
{
        public int PlayerId { get; set; }
      
        public string Handle { get; set; }
       
         .......

        public int SpecialAbilitiiesId { get; set; }
        public int SpecialAbilitiiesName{ get; set; }
        ..... and so on
        
        public int StatsId { get; set; }
        public int StatsName { get; set; }
        ..... and so on
}

All properties in this class should be the same name and type as it is in you stored procedure

SELECT Player.Id as PlayerId, Stats.Id as StatsId, 
Special_Abilities.Id as SpecialAbilitiesId,  ....and so on the same name as it in the SpResultClass

add SpPlayerResult to db context and run this code


public virtual DbSet<SpPlayerResult> SpPlayerResults { get; set; }


[HttpGet("{id}")]
 public async Task<ActionResult<Player>> GetPlayerInfo(int id)
{
      if(id==0) return null;
      
var ID = new SqlParameter("@ID", id);

var players = await _context.SpPlayerResults.FromSqlRaw("exec GetOnePlayerInfo @ID",
            parameters: new[] { ID }).ToListAsync();

if (players!=null && players.Count>0) return players.FirstOrDefault();

return null;

 }

after this you will have to map result to a Player object.

So as you can see it needs some code. I am using stored procedures only if I neeed a very complicated select query that can take hundreds line of code. For CRUD much easier to use just EF

 var player = await  _context.Players
                      .Include(i=> i.SpecialAbilities) 
                      .Include(i=> i.Stats)
                      .FirstOrDefaultAsync(i=> i.PlayerID==id);

Upvotes: 1

Related Questions