Entity framework function import, can't load relations for functions that return entity types

Question

I've created a function import that returns the results of a stored proceedure as one of my entities. however I can't seem to traverse my through navigation properties to access the data in other entities. I know that you can use include() for objectQueries but can't find anything that will force the EF to load my relations for entity results of function imports.

Any ideas??

Thanks in advance.

Answer 1

This is not possible in EF 1.0

The reason is that EF will consider stored procedure values to be just values and not navigation properites.

For example, Employee entity has multiple Order entities. In Order you have a property called EmployeeID. When the database fills your query using include statements, it creates 1 projection query in SQL to populate all of the Order data that a particular Employee could have.

So if I said

var employee = context.Employees.Include("Orders").Where(e => e.ID == 1).First();

var orders = employee.Orders;

The SQL for the first query will create a projection query which will contain orders where the EmployeeID = 1.

Now when your stored procedure runs, this can do any code behind the scenes (in otherwords it can return any set of data). So when SQL runs the stored procedure, it just runs the code in that stored procedure and does not have any knowledge that EmployeeID on Order is an FK to that property. Additionally, if your stored procedure returns an Employee entity, then you are looking at another scenario where you will not even have an OrderID to pursue.

To work around this though, you can setup your query in EF using Include statements that can mirror any stored procedure. If you use the proper mix of .Select and .Include statements you should be able to do the same thing.