Understanding Multiple Tables result under Entity Framework

Question

In order to show a page I need to grab a lot information from various tables, and for now, it takes around 20 seconds to load the page, witch is terrible.

So I want to move everything into one Store Procedure and get all that info the old DataTable way.

I get this

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 looks like this

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'm facing is that, the line this.ExecuteMethodCall throws an error saying that the result is not a MultipleTable result.

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 is

More than one result type declared for function 'WinnersBackendPageInformation' that does not return IMultipleResults.

What am I missing?

Answer 1

You're not missing anything.

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

If you read the blog post here, you'll find this statement from a member of the EF team:

Unfortunately we weren’t able to get full support for multiple results into the product this time around. We did, however, add the method Translate<T> to ObjectContext which allows you to materialize objects from a DataReader. So if you have a stored procedure which returns multiple results whose properties directly align with EF objects, then you could get the underlying store connection from the context (context.Connection.StoreConnection), create a command and use it to execute the stored procedure and get back the DataReader. Then you could call Translate<FirstObjectType> and get back an enumerable of those objects, followed by reader.NextResult() and Translate<SecondObjectType>, etc.

So, you can use some "old-school" ADO.NET, alternatively you can try the EF Extensions project on CodePlex which appears to do the plumbing for you.