Understanding the results of multiple tables in the Entity Framework

Question

To show the page, I need to collect a lot of information from different tables, and at the moment it takes about 20 seconds to load the page, because it's terrible.

So, I want to move everything into one Store procedure and get all this information in the old DataTable way.

I get it

public WinnerPageInformation FindWinnerPageInformation(int calendarId) { BackendPagesContext ctx = new BackendPagesContext(db.Connection); IMultipleResults results = ctx.WinnersBackendPageInformation(calendarId); return new WinnerPageInformation() { Challenges = results.GetResult<Challenges>(), Content = results.GetResult<ContentWinners>().FirstOrDefault(), Einfo = results.GetResult<ContentEmails>().FirstOrDefault(), Fields = results.GetResult<SubscriberFields>(), Prizes = results.GetResult<Prizes>(), Winners = results.GetResult<Winners>() }; } 

and WinnersBackendPageInformation is as follows

 public class BackendPagesContext : DataContext { public BackendPagesContext(System.Data.IDbConnection connection) : base(connection) { } [Function(Name = "dbo.sp_GetWinnersBackendPageInformation")] [ResultType(typeof(JK_ContentWinners))] [ResultType(typeof(JK_Winners))] [ResultType(typeof(JK_SubscriberFields))] [ResultType(typeof(JK_Prizes))] [ResultType(typeof(JK_Challenges))] [ResultType(typeof(JK_ContentEmails))] public IMultipleResults WinnersBackendPageInformation( [Parameter(Name = "calendarId", DbType = "Int")] int calendarId) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), calendarId); return (IMultipleResults)(result.ReturnValue); } } public interface IMultipleResults : IFunctionResult, IDisposable { IEnumerable<TElement> GetResult<TElement>(); } 

but the problem I ran into is that the line this.ExecuteMethodCall throws an error saying that the result is not the result of MultipleTable.

my store procedure looks like

 ALTER PROCEDURE sp_GetWinnersBackendPageInformation @calendarId numeric = 0 AS BEGIN SELECT * FROM ContentWinners WHERE calendar_id = @calendarId; SELECT * FROM Winners WHERE calendar_id = @calendarId; SELECT * FROM SubscriberFields WHERE calendar_id = @calendarId ORDER BY position; SELECT * FROM Prizes WHERE calendar_id = @calendarId ORDER BY prizetype_id, to_day, title; SELECT * FROM Challenges WHERE calendar_id = @calendarId; SELECT * FROM ContentEmails WHERE calendar_id = @calendarId; END GO 

Error message

More than one type of result declared for the "WinnersBackendPageInformation" function, which does not return IMultipleResults.

What am I missing?

Answer 1

Nothing is missing.

Entity Framework 4 does not support multiple result sets in stored procedures.

If you read the blog post here , you will find this expression from a member of the EF team:

Unfortunately, we were not able to get full support for several results in the product this time. However, we added the "Translate to ObjectContext" method, which allows you to materialize objects from the DataReader. Therefore, if you have a stored procedure that returns several results whose properties are directly aligned with EF objects, you can get the underlying storage store from the context (context.Connection.StoreConnection), create a command and use it to execute the stored procedure and return the DataReader . Then you can call "Translate" and return the enumerated of these objects, followed by reader.NextResult () and "Translation", etc.

So, you can use ADO.NET "old school", you can also try the EF Extensions project on CodePlex , which seems to be doing plumbing for you.