ASP.NET Core : get stored procedure results to a view model

Question

I have a view model called TdetailsVM as below:

public class TdetailsVM 
{
    public Tournaments tourney { get; set; }
    public List<Participants> currentlyjoined { get; set; }
}

Now in the controller I am passing an ID of the tournament to the task:

public async Task<IactionResult> Details(guid id)
{
    var ThisTourney = _context.Tournaments.FirstOrDefaultAsync(m => m.TID == id);

This will return the value for a specific tournament into ThisTourney where I pass it later to the view model

I need something similar like:

var ThisParticipants = (result "has many rows" from a stored procedure called SP_GetParticipants that needs a parameter =id)

Then I can pass the values to the view model as below

TdetailsVM tvm = new TdetailsVM()
                     {
                          tourney = ThisTourney,
                          currentlyjoined = ThisParticipants
                     }

// then I can return the view 
return view(tvm);

Passing the data for the first requirement is fine and it works but how can I pass the stored procedure ?

Many thanks in advance

Answer 1

If you are using Entity Framework Core, then for calling your stored procedure you can use this line of code.

List<Participants> participants= _context.Participants.FromSql("SP_GetParticipants @ID",id).ToList();

Note how I passed @ID value to the stored procedure in FromSql method and how mapped the result to List<Participants>. Please have a look at raw sql for more information.

After this line of code you have you list of Participants, then you can fill your parent ViewModel.

var result = new TdetailsVM 
        {
            Tourney = thisTourney,
            Currentlyjoined  = Participants
        };

Note that it is recommended to use PascalCase for naming your public properties in your class (ViewModel) and camelCase for your private ones. Please take a look at General Naming Conventions for more information.