Returning multiple resultsets with EntityFramework

问题:

I am working on a code where I need Multiple tables as result of a stored procedure. I am using Entity Framework repository pattern. It returns and bind an IEnumerable object, but I need to bind it with multiple IEnumerables at the same time. Can anybody help?

This is the code I am using : db.Database.SqlQuery("procReturnsMultipleResuiltSets")

回答1:

the ways to achieve your goal are disclosed in this article.

From related article the most common way is:

using (var db = new BloggingContext()) 
{ 
// If using Code First we need to make sure the model is built before we open the connection 
// This isn't required for models created with the EF Designer 
db.Database.Initialize(force: false); 

// Create a SQL command to execute the sproc 
var cmd = db.Database.Connection.CreateCommand(); 
cmd.CommandText = "[dbo].[GetAllBlogsAndPosts]"; 

try 
{ 

    db.Database.Connection.Open(); 
    // Run the sproc  
    var reader = cmd.ExecuteReader(); 

    // Read Blogs from the first result set 
    var blogs = ((IObjectContextAdapter)db) 
        .ObjectContext 
        .Translate<Blog>(reader, "Blogs", MergeOption.AppendOnly);    


    foreach (var item in blogs) 
    { 
        Console.WriteLine(item.Name); 
    }         

    // Move to second result set and read Posts 
    reader.NextResult(); 
    var posts = ((IObjectContextAdapter)db) 
        .ObjectContext 
        .Translate<Post>(reader, "Posts", MergeOption.AppendOnly); 


    foreach (var item in posts) 
    { 
        Console.WriteLine(item.Title); 
    } 
} 
finally 
{ 
    db.Database.Connection.Close(); 
} 
}

please note the important remark: The first result set must be consumed before moving to the next result set.