Using a stored procedure in entity framework, how do I get the entity to have its navigation properties populated?

c# .net entity-framework entity
user48545 picture user48545 · Jun 24, 2009 · Viewed 13.2k times · Source

Entity framework is cripplingly slow so I tried using a stored procedure but I ran into this problem.

Entity Framework allows you to define a stored procedure that produces an entity. However my entity has 'navigation properties' which are not being populated when using this method.

Is there a work around?

Answer

Alex James picture Alex James · Jun 24, 2009

Well stored procedures are not composable. So there is no way to call your SPROC and have the EF automatically populate relationships in the same query, using Include() or something.

So say you have products and categories

and you have a sproc to get Products:

i.e.

var products = context.GetProducts(someproductfilter);

the resulting products won't have their categories loaded.

However if you have a second stored procedure that gets the Categories for said products:

i.e.

var categories = context.GetCategoriesForProducts(someproductfilter);

a feature in EF called relationship fixup, which links related entities once the second entity enters the context, will insure that after both calls are made, each product in products will have a non-null Category.

This is not ideal, because you are doing more than one query, but it will work.

An alternative is to use EFExtensions. The guy who wrote that created the ability to write sprocs that load more data in one go.

Hope this helps

Cheers Alex

Related questions

MetadataException: Unable to load the specified metadata resource

All of a sudden I keep getting a MetadataException on instantiating my generated ObjectContext class. The connection string in App.Config looks correct - hasn't changed since last it worked - and I've tried regenerating a new model (edmx-file) from …

Read More
Unable to update the EntitySet - because it has a DefiningQuery and no <UpdateFunction> element exist

I am using Entity Framework 1 with .net 3.5. I am doing something simple like this: var roomDetails = context.Rooms.ToList(); foreach (var room in roomDetails) { room.LastUpdated = DateTime.Now; } I am getting this error when I try to do: context.SaveChanges(); …

Read More
How to compare only Date without Time in DateTime types in Linq to SQL with Entity Framework?

Is there a way to compare two DateTime variables in Linq2Sql but to disregard the Time part. The app stores items in the DB and adds a published date. I want to keep the exact time but still be …

Read More