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

Question

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 1

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