Categories
c# entity-framework linq sql sql-server

Entity Framework – Eagerly load object graph using stored procedures

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();