How to execute stored procedure in ASP.NET Core 3.1 and assign result sets to variables

[fokwabest]

I am new to ASP.NET Core (3.1). I have a stored procedure that takes two parameters: startDate and endDate.
It returns the following result sets in one go (or execution):

I want to be able to grab the result sets in 3 separate variables because I will like to output them on my view:


For example - for the 1st result set:

foreach (var maindata in Model.MainDataViewModel)
{
     <td>maindata.CaseId</td>
     <td>maindata.EpisodeId</td>
     <td>maindata.SpecimenTag</td>
     <td>maindata.PathologyOrderId</td>
}

Likewise for the second and third.


I have attempted to call the stored procedure using this code:

public SpPotentialCandidatesForOncotypeDXViewModel GetPotentialCandidatesForTheOncotypeDXData(DateTime startDate, DateTime endDate)
{
    try
    {
        List<SqlParameter> pc = new List<SqlParameter>
                        {
                           new SqlParameter("@p0", startDate),
                           new SqlParameter("@p1", endDate),
                        };
       
        var da = _dbContext.Database.ExecuteSqlRaw("spPotentialCandidatesForOncotypeDX @p0, @p1", pc.ToArray());
    }
    catch (Exception ex)
    {
        AppLog.WriteError("GetPotentialCandidatesForTheOncotypeDXData", ex.Message);
    }

    // return spPotentialCandidatesForOncotypeDXes;
}

After testing, da has value -1. Via further research I realise ExecuteSqlRaw returns the number of rows affected and not the result sets.


How can I capture these result sets individually and assign to variables?

 

[Skydiver]

I hope that you do know that .NET Core 3.1 is out of support.

 

[cjard]

You can't. It was discussed at length but never implemented for your version:

FromSql: Support multiple resultsets · Issue #8127 · dotnet/efcore

From @rpundlik on March 22, 2017 12:9 While retrieving the results using stored procedure how can I retrieve and store multiple result set in view model in .net core For e.g. from stored procedure ...

github.com

That page does have some efforts from people hacking it in but if you're new to all this you might not have much success

I personally think you should use Dapper alongside EFC and have dapper retrieve where you have multiple result sets. See this article for an In depth explanation:

Handling Multiple Resultsets

While not a very common usage scenario, it can come handy sometimes

medium.com

After you had dapper retrieve your resultset and map them to lists of types that EFC uses you can attach them to an EFC context to save them if you want.
If the results from that sproc don't map to EFC types, you can simply make classes that represent them to use with dapper