Using LINQ with stored procedure that returns multiple instances of the same entity per row

Question

Our development policy dictates that all database accesses are made via stored procedures, and this is creating an issue when using LINQ.

The scenario discussed below has been somewhat simplified, in order to make the explanation easier.

Consider a database that has 2 tables.

• Orders (OrderID (PK), InvoiceAddressID (FK), DeliveryAddressID (FK) )
• Addresses (AddresID (PK), Street, ZipCode)

The resultset returned by the stored procedure has to rename the address related columns, so that the invoice and delivery addresses are distinct from each other.

OrderID InvAddrID DelAddrID InvStreet DelStreet InvZipCode DelZipCode
1       27        46        Main St   Back St   abc123     xyz789

This, however, means that LINQ has no idea what to do with these columns in the resultset, as they no longer match the property names in the Address entity.

The frustrating thing about this is that there seems to be no way to define which resultset columns map to which Entity properties, even though it is possible (to a certain extent) to map entity properties to stored procedure parameters for the insert/update operations.

Has anybody else had the same issue?

I'd imagine that this would be a relatively common scenarios, from a schema point of view, but the stored procedure seems to be the key factor here.

Answer 1

Have you considered creating a view like the below for the stored procedure to select from? It would add complexity, but allow LINQ to see the Entity the way you wanted.

Create view OrderAddress as 
Select o.OrderID 
,i.AddressID as InvID
,d.AddressID as DelID
...
from Orders o
left join Addresses  i
on o.InvAddressID= i.AddressID
left join Addresses d
on o.DelAddressID = i.AddressID

Answer 2

LINQ is a bit fussy about querying data; it wants the schema to match. I suspect you're going to have to bring that back into an automatically generated type, and do the mapping to you entity type afterwards in LINQ to objects (i.e. after AsEnumerable() or similar) - as it doesn't like you creating instances of the mapped entities manually inside a query.

Actually, I would recommend challenging the requirement in one respect: rather than SPs, consider using UDFs to query data; they work similarly in terms of being owned by the database, but they are composable at the server (paging, sorting, joinable, etc).

(this bit a bit random - take with a pinch of salt)

UDFs can be associated with entity types if the schema matches, so another option (I haven't tried it) would be to have a GetAddress(id) udf, and a "main" udf, and join them:

var qry = from row in ctx.MainUdf(id)
          select new {
             Order = ctx.GetOrder(row.OrderId),
             InvoiceAddress = ctx.GetAddress(row.InvoiceAddressId),
             DeliveryAddress = ctx.GetAddress(row.DeliveryAddressId)) };

(where the udf just returns the ids - actually, you might have the join to the other udfs, making it even worse).

or something - might be too messy for serious consideration, though.

Answer 3

this it isn't very efficient at all, but if all else fails, you could try making two procedure calls from the application one to get the invoice address and then another one to get the delivery address.

Answer 4

If you know exactly what columns your result set will include, you should be able to create a new entity type that has properties for each column in the result set. Rather than trying to pack the data into an Order, for example, you can pack it into an OrderWithAddresses, which has exactly the structure your stored procedure would expect. If you're using LINQ to Entities, you should even be able to indicate in your .edmx file that an OrderWithAddresses is an Order with two additional properties. In LINQ to SQL you will have to specify all of the columns as if it were an entirely unrelated data type.

If your columns get generated dynamically by the stored procedure, you will need to try a different approach: Create a new stored procedure that only pulls data from the Orders table, and one that only pulls data from the addresses table. Set up your LINQ mapping to use these stored procedures instead. (Of course, the only reason you're using stored procs is to comply with your company policy). Then, use LINQ to join these data. It should be only slightly less efficient, but it will more appropriately reflect the actual structure of your data, which I think is better programming practice.