I'm changing my EF linq query to a stored procedure. Is there an easy way to setup complex mappings?

Question

I have an Entity Framework project containing 5 separate tables. When I query data from these tables and use .Include(), the query it generates is extremely slow and times out.

I have moved the query to a stored procedure, and now am looking for a way to query this stored procedure, and easily map the data it returns to the existing data classes.

My original Linq query to EF's DataContext looked like this:

var data = (from a in context.A
                    .Include("B")
                    .Include("C")
                    .Include("D")
                    .Include("E")
            where a.Id == someValue
            select a);

It return an Entity data object that resembled this:

class A
{
    int Id;
    string otherProperties;

    List<B> B;
    List<C> C;
    List<D> D;
    List<E> E;
}

The query that EF generated to run on SQL server returned a result set that looks something like this:

C1    C2    C3    C4    C5    C6    C7    C8    C9    C10
----------------------------------------------------------
A1    A2    B1    B2        
A1    A2                C1    C2
A1    A2                C1    C2
A1    A2                C1    C2
A1    A2                            D1    D2
A1    A2                            D1    D2
A1    A2                            D1    D2
A1    A2                                        E1    E2
A1    A2                                        E1    E2

(Assumes 1 B record, 3 C records, 3 D records, and 2 E records.

I have reproduced this query in a stored procedure and reduced its runtime to practically nothing, however I am stuck at trying to figure out how to map the result set to my Entity Framework data class (class A from above).

EF is definitely capable of this as it does it already somewhere behind the scenes when I use the Linq query, but I'm not sure if that's something I would have access to.

Is there an easy way in Entity Framework to map a single stored procedure (Function Import) to an multi-level Entity Framework class?

Answer 1

I found a blog post that explains how this can be done with Entity Framework.

http://blogs.infosupport.com/ado-net-entity-framework-advanced-scenarios-working-with-stored-procedures-that-return-multiple-resultsets/

To summarize it briefly, you need to write your stored procedure so it returns your data in a specific way that is expected by Entity Framework, and then you can use some EF extensions to customize your stored procedure execution, and to materialize your object graph.

It looks like this is built into Entity Framework in future versions (5.0+ I believe), however this workaround looks like it should work for lower versions.

Although in my case, I found if I just got rid of the .Include() in my Linq query and manually triggered loading the other properties by referencing them in the code before returning the object, my problem with performance got fixed. This is because instead of building one massive query between the 5 tables with unoptimized joins, it runs 5 separate queries that each only pull the specific data it needs.