How to deal with two returned values from SQL in C#?

Question

0.00/5 (No votes)

See more:

I have a stored procedure that has input Id and Loc and returns Id,Loc and Description So i made a class in C# called RetrieveDE that has these three returned fields

Then i edited my stored procedure to return two selects instead of one. And now i don't know how to deal with both selects

What I have tried:

Before i edited the stored procedure:

<pre>
using (TableDBContext table= new TableDBContext())
{
   var result = table.Database.SqlQuery<RetrieveDE>("exec usp_Retrieve 
                @Id, @Loc"
                    , new SqlParameter("Id", id)  //id and loc are input to the method
                    , new SqlParameter("Loc", loc))).ToList();

   return result;   //RetrieveDE has the name of fields that are returned by the 
                             stored procedure which are Id,Loc and Description
}

then i use the result in controller

Now i edited my stored procedure to return two results, i mean there is now 2 selects in the stored Procedure. what should i do in the code above to be able to deal with these two results.
The other select returns DayId and Day?

Posted 22-Apr-20 0:35am

Member 14800672

Updated 22-Apr-20 0:49am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2020-04-22T00:50:00

There is no native support for multiple result sets in Entity Framework.

If you're using EF6, you can get the ObjectContext and manually translate the results, as described in this SO answer[^]:

C#

using (TableDBContext context = new TableDBContext())
{
    var objectContext = ((IObjectContextAdapter)context).ObjectContext;
    
    using (var command = context.Database.Connection.CreateCommand())
    {
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.CommandText = "dbo.usp_Retrieve";
        command.Parameters.AddWithValue("@Id", id);
        command.Parameters.AddWithValue("@Loc", loc);
        
        context.Connection.Open();
        
        using (var reader = command.ExecuteReader())
        {
            var firstResultSet = objectContext.Translate<RetrieveDE1>(reader, "Set1", MergeOption.AppendOnly).ToList();
            
            reader.NextResult();
            
            var secondResultSet = objectContext.Translate<RetrieveDE2>(reader, "Set2", MergeOption.AppendOnly).ToList();
            
            return new { firstResultSet, secondResultSet };
        }
    }
}

EF Core doesn't have that option, and doesn't support materializing objects which aren't part of the model. If you're using a "query type" / "keyless entity type"[^], you may be able to use something like this code[^] to materialize the results. Otherwise, you'll have to load them manually.