C# Entity Framework Multiple Results

Question

I'm trying to consume multiple results from a stored procedure, which returns 3 result sets.

The first result is a single column called 'Message'.
The second result set is a table
The third result set is another table

My main problem is that every other example on how to approach multiple result sets from a stored procedure implies that every result set can be mapped to an entity, which is not the case with my first result.

First result:

Select @Message

Second result set returned from this query:

Select SecondId, SecondName 
From T_Table1

Third result set returned from this query:

Select ThirdId, ThirdName 
From T_Table2

Update I'm updating my EDMX directly, I'll include an Example from Microsoft and explain my problem.

<FunctionImport Name="s_GetAllData">
      <ReturnType EntitySet="CustomerNames" Type="Collection(Model.CustomerName)" />
      <ReturnType EntitySet="CustomerOrders" Type="Collection(Model.CustomerOrder)" />
      <ReturnType EntitySet="CustomerShippings" Type="Collection(Model.CustomerShipping)" />
</FunctionImport>

According to this I'd need a Collection Type to reference (meaning I'd need a existing table within the Database that reflects the columns coming in).

You can't add a Table to EF Model unless it has a Key field, which I don't have in my Results.

Your first result is still an Entity, with one property defined as something like public string Message { get; set; } — entropic
– entropic, Commented Jun 28, 2019 at 20:13

Tom Regan · Accepted Answer · 2019-06-28 21:22:59Z

2

I don't use EDMX files and have not touched them in years, but here is an example of how you can consume multiple resultsets from a stored procedure call using POCO. In the example below the stored procedure dbo.MyProc has two select statements, assume the first select simply selects a string as in your example.

using (DbContexts.MyDbContext db = new DbContexts.MyDbContext())
        {
                using (var cmd = db.Database.Connection.CreateCommand())
                {
                    db.Database.Connection.Open();
                    cmd.CommandText = "EXEC dbo.MyProc @param1=@param1";
                    cmd.Parameters.Add(new SqlParameter("@param1", SqlDbType.Int) { Value = 1 });

                    using (var rdr = cmd.ExecuteReader())
                    {
                        using (var objectContext = ((IObjectContextAdapter)db).ObjectContext)
                        {
                            List<string> listSTring = objectContext.Translate<string>(rdr).ToList();
                            rdr.NextResult();
                            List<MyClass> listMyClass = objectContext.Translate<MyClass>(rdr).ToList();
                        }
                    }
                }
        }

edited Jun 28, 2019 at 21:22

answered Jun 28, 2019 at 21:13

Tom Regan

3,8719 gold badges48 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sandro Perez Over a year ago

Couple of Minutes ago I was able to get something working using this method, still thank you, I can mark this as the answer since I don't think most people prefer editing the EDMX directly.

Collectives™ on Stack Overflow

C# Entity Framework Multiple Results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related