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?

Solution 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

Solution 2

I found this SO question when researching Stored Procedures (SPs) with EF. I also see people like @KristianNissen and @Todilo have asked if there is an update with EF6.

The answer is yes, EF 6 has changed things, but it did not add anything to help load navigational properties when using SPs. Nor can you use the .Include() method with SPs as was asked in this SO question.

The only way is to write your SP to specifically load the navigational properties. However there is now some good Microsoft documentation on using SPs - see Query SP and SP returning multiple result sets.

For completeness the change that EF version 6 brought in was to allow Stored Procedures (SPs) to handle insert, update and delete - see Microsoft article and Entity Framework Tutotial.