Entity Framework - get records in multiple tables using stored procedure

Question

I am trying to get to grips with the Entity framework, and have a requirement to order results by distance from a point on the globe. I have decided on previous advice to do this using a stored procedure which I have successfully done populating a view. However I need to return multiple tables, which I understand I cannot do directly using stored Procedures on the Entity Framework. If this is not correct, I would be grateful if someone could advise how I might do this.

Anyway I therefore have defined a simple sp (SELECT id FROM table) and then wanted to perform a linq query to join this with the equivalent object in my model as follows:

var sp = db.StoredProcedure();

var ret = from x in db.X
          join y in sp on x.ID equals y.ID
          select x;

However when I perform this I get the following exception resulting from the query:

"Unable to create a constant value of type 'System.Collections.Generic.IEnumerable'1'.Only primitive types('suchas Int32, String, Guid') are supported in this context."

Why is this happening? Is this the right approach? (Note that my final sp will be more complex, and I will be returning multiple classes from the select in 'ret')

Answer 1

The EF in .NET 3.5 SP1 cannot map procs which return scalar values (in .NET 4.0 you can). The SP must return all the values necessary to materialize a full entity, which is likely more than just the ID.

Also, it's almost never correct to use the "join" reserved word in LINQ to Entities. You traverse relationships in your client schema instead.

Start by writing a proc which returns all values necessary for an entity type. Map that proc. Then do:

IQueryable<MyEntity> q = from e in Context.MyEntities
                         select e;

Then move on from there.

Answer 2

Use EF Extensions

Stored procedures are really badly supported in EF. Even if you they return a entity results, they don't provide any name mappings, so you have to rename column in the stored procedures yourself.

But. There's project called Entity Framework Extensions that will make all kinds of different scenarios with stored procedures possible.

Using EF extensions you can use stored procedures the in any way you want:

• you can do column remappings in your custom materializer (so your stored procedure returns same columns as they are in the DB
• you can return multiple result sets (great for 1:* and . relations)
• you can use scalar stored procedures or even procedures that don't return anything
• you can consume results from a stored procedure that returns multiple entities per row (when having 1:1 relation between two of them)
• you can also use output parameters which is great if you create a stored procedure that does paging (returns a subset of records as entities and returns out parameters with total count)
• etc.

You can do preety much anything. We've used EF Extensions with much success on a some project. We wrote our own materializers (basically a lamba expression) for entities that our stored procedures returned. And then we materialized their results.

I don't think EF4 will support stored procedures to this level anyway, so getting acquainted to EF Extensions is always valuable.