Entity Framework - Eagerly load object graph using stored procedures

Question

Background

I am changing my LINQ-to-SQL code in my project to Entity Framework. Most of the change over was relatively simple, however, I have run into a fairly major issue. With LINQ-to-SQL, I was able to load an entire object graph (for Model B) using stored procedures like so:

ViewModel.Model = MyDbContext.usp_ModelA_GetByID(AId).Single();
List<ModelB> Details = 
    (from b in MyDbContext.usp_ModelB_GetByID(BId)
    join c in MyDbContext.usp_ModelC_GetAll()
       on b.CId equals c.CId
    select new ModelB()
    {
        BId = b.BId,
        CId = b.CId,
        C = c
    }).ToList();
ViewModel.Model.ModelBs.AddRange(Details);

However, after converting this code to EF, on the line where ViewModel.Model.ModelBs is accessed, I get the error "EntityCommandExecutionException" with the inner exception explaining that "The SELECT permission was denied on the object 'ModelBTable'." Obviously, EF is attempting to fetch the ModelBs for the ModelA even though I have already loaded them from the database. While I don't fully understand why it's trying to load the entities even though I have added them, I can only assume that because it didn't load them itself, it doesn't believe they are fully loaded and probably views all of the objects I loaded into it as "New".

In an effort to bypass EF attempting to fetch the objects itself, I decided to change my code to:

ViewModel.Model = MyDbContext.usp_ModelA_GetByID(AId).Single();
List<ModelB> Details = 
    (from b in MyDbContext.usp_ModelB_GetByID(BId)
    join c in MyDbContext.usp_ModelC_GetAll()
        on b.CId equals c.CId
     select new ModelB()
     {
        BId = b.BId,
        CId = c.CId,
        C = c
     }).ToList();
ViewModel.Model.ModelBs = new EntityCollection<ModelB>();
foreach (ModelB detail in Details)
{
    ViewModel.Model.ModelBs.Attach(detail);
}

After making this change, I now run into the error "InvalidOperationException" with a message of "The EntityCollection could not be initialized because the relationship manager for the object to which the EntityCollection belongs is already attached to an ObjectContext. The InitializeRelatedCollection method should only be called to initialize a new EntityCollection during deserialization of an object graph.".

This is confusing enough because I am using the same context to load all of the entities so I'm unsure as to why it won't allow me to combine them together. I am able to do this in other ORMs without issue.

After researching this error, I decided to attempt an approach that I hoped would trick EF into thinking that the entire object graph was loaded by the same context so I rewrote my code to be:

ViewModel.Model = 
    (from a in MyDbContext.usp_ModelA_GetByID(AId)
    select new A()
    {
        AId = a.AId,
        ModelBs = (from b in MyDbContext.usp_ModelB_GetByID(BId)
                  join c in MyDbContext.usp_ModelC_GetAll()
                      on b.CId equals c.CId
                  select new ModelB()
                  {
                      BId = b.BId,
                      CId = b.CId,
                      C = c
                  }).ToEntityCollection()
    }).Single();

with ToEntityCollection being an extension method I created like so:

public static EntityCollection<TEntity> ToEntityCollection<TEntity>(
     this IEnumerable<TEntity> source) where TEntity : class, IEntityWithRelationships
{
    EntityCollection<TEntity> set = new EntityCollection<TEntity>();
    foreach (TEntity entity in source)
    {
        set.Attach(entity);
    }
    return set;
}

Now, I get the error "InvalidOperationException" with a message of "Requested operation is not allowed when the owner of this RelatedEnd is null. RelatedEnd objects that were created with the default constructor should only be used as a container during serialization.".

After extensively researching each of these errors, I was still unable to find a solution pertaining to my problem.

Question

So, after all of that, my question is: How do I load an entire object graph when each object has its own stored procedure using Entity Framework 4?

Update

So, based on the answers so far, I feel I need to include the following caveats here:

1. I am not looking for an answer that uses a single stored procedure to load an entire object graph. I am looking for a way to load an object graph using a get stored procedure per entity. I realize that loading the object graph using a single stored procedure could, theoretically perform much better, but at this time, I am more interested in smaller changes to the code base especially with regards to the way the database is structured.

2. If your solution requires editing the edmx directly, it will not be an acceptable answer. Since this is an auto-generated file, editing the edmx directly essentially means that those same changes would need to be re-done upon any modification through the designer.

Update 2

So, after some deliberation, I came up with a work around. What I did was change my ViewModel to have a List ModelBs property that pulls the data using the stored procedure joins and in my view, I am just setting this property as the datasource. This is definitely not what I would consider to be an optimal solution because now my ViewModel is acting more like the Model than a ViewModel and I can no longer traverse my ModelA type to get the list of ModelBs, but it works! I still don't understand why I can do:

(from b in MyDbContext.usp_ModelB_GetByID(BId)
join c in MyDbContext.usp_ModelC_GetAll()
    on b.CId equals c.CId
select new ModelB()
{
    BId = b.BId,
    CId = b.CId,
    C = c //<------Setting a navigation property and EF figures out that it belongs
}).ToList();

but I can't do:

(from a in MyDbContext.usp_ModelA_GetByID(AId)
select new ModelA()
{
    AId = a.AId,
    ModelBs = MyDbContext.usp_ModelB_GetByID(BId).ToEntityCollection() //<----Won't let me set the navigation property when the navigation property is a collection.
}).Single();

Answer 1

It can be done in a fairly simple way but takes some manual effort. Here is an MSDN post on handling stored procedures with multiple result sets which shows both a code first and database first approach.

Example:

Load EntityB proc:

create proc dbo.Get_EntityB_by_EntityAId( @aId int )
as

select distinct
    b.EntityBId
    , b.Description
from
    EntityA a
    left outer join EntityB b
     on a.PrimaryEntityB_EntityBId = b.EntityBId
    left outer join EntityB b2
     on a.AlternativeEntityB_EntityBId = b2.EntityBId
where
    a.EntityAId = @aId
go

Load EntityA proc (which calls load B proc)

create proc dbo.Get_EntityA_by_Id( @id int )
as

-- use a select statement
select 
    a.EntityAId
    , a.Description
    , a.PrimaryEntityB_EntityBId
    , a.AlternativeEntityB_EntityBId
from
    EntityA a
where
    a.EntityAId = @id

-- and/or other sprocs
exec dbo.Get_EntityB_by_EntityAId @id

go

Entity classes

[Table("EntityA")]
public partial class EntityA
{
    public int EntityAId { get; set; }
    public string Description { get; set; }


    public virtual EntityB PrimaryEntityB { get; set; }

    public virtual EntityB AlternativeEntityB { get; set; }
}


[Table("EntityB")]
public partial class EntityB
{
    public int EntityBId { get; set; }
    public string Description { get; set; }

    [InverseProperty("PrimaryEntityB")]
    public virtual ICollection<EntityA> EntityAsViaPrimary { get; set; }
    [InverseProperty( "AlternativeEntityB" )]
    public virtual ICollection<EntityA> EntityAsViaAlternative { get; set; }
}

Method that calls sproc and handles results (for this method, you could return the one EntityA if you'd like)

public static void EagerLoadEntityA( int aId )
{
    using( var db = new TestEntities() )
    {
        // if using code first
        db.Database.Initialize( false );

        var cmd = db.Database.Connection.CreateCommand();
        cmd.CommandText = "dbo.Get_EntityA_by_Id";

        db.Database.Connection.Open();

        try
        {
            var reader = cmd.ExecuteReader();

            var objContext = ( ( IObjectContextAdapter )db ).ObjectContext;

            var aEntities = objContext
                .Translate<EntityA>( reader, "EntityAs", MergeOption.AppendOnly );

            reader.NextResult();

            var bEntities = objContext
                .Translate<EntityB>( reader, "EntityBs", MergeOption.AppendOnly );

        }
        finally
        {
            db.Database.Connection.Close();
        }
    }
}

Usage:

EagerLoadEntityA( 1234 );
var entityA = db.EntityAs.Find( 1234 ); // cached
var primB = entityA.PrimaryEntityB; // this is already loaded

Answer 2

Okay, so after even further deliberation, I figured out a solution that works for what I am wanting. Since I am in a web environment and have no need to lazily load objects, I turned EnableLazyLoading to false for the entire DbContext. Then, using an EF feature called the magical relationship fix-up, I am able to do the following:

ViewModel.Model = MyDbContext.usp_ModelA_GetByID(AId).Single();
var Details = 
(from b in MyDbContext.usp_ModelB_GetByID(BId)
join c in MyDbContext.usp_ModelC_GetAll()
   on b.CId equals c.CId
select new ModelB()
{
    BId = b.BId,
    CId = b.CId,
    C = c
}).ToList();  
//ToList() executes the proc and projects the plate details into the object 
//graph which never tries to select from the database because LazyLoadingEnabled is
//false.  Then, the magical relationship fix-up allows me to traverse my object graph
//using ViewModel.Model.ModelBs which returns all of the ModelBs loaded into the graph
//that are related to my ModelA.