Getting a multi-dimensional result out of a stored procedure with LINQ to SQL

Question

I am using LINQ to SQL to call a stored procedure with a single result set (I have found a lot of information about handling multiple result sets, but that is not what I am doing). The result set is actually all columns from 3 tables joined together, and I have LINQ to SQL entities for those 3 tables. The schema is something like this:

• Customer 1
  • Order 1
    • Product 1
    • Product 2
    • Product 3
  • Order 2
    • Product 4
• Customer 2
  • Order 3
    • Product 5
  • Order 4
    • Product 6
    • Product 7

Basically, 1 Customer to many Orders, 1 Order to many Products. So the result set of the stored proc is actually one row per Product, but each row includes all of the Order and Customer columns. In the above example, the stored proc would return 7 rows.

So the question is: how can I get 2 Customer objects out of LINQ to SQL, each having their Orders collection filled, and each Order object having its Products collection filled all from the results of the stored proc?

I know that if you do something like (dc is the LINQ to SQL DataContext)...

var options = new DataLoadOptions();
options.LoadWith<Customer>(c => c.Orders);
dc.LoadOptions = options;

var customers = from c in dc.Customers select c;

...and then watch the SQL that gets generated, it will actually run one SQL statement that joins Customers to Orders, selecting all columns from both, and then return to you the distinct Customer objects with their Orders collections filled. I am wanting this same kind of translation to objects, but from the results of my stored proc.

I have tried setting the return type of my stored proc to Customer, but with the example above, I get a collection of 7 Customer objects (5 duplicates) which do not have their Orders collection filled. If I then iterate over the Orders collection of one of the Customer objects, I see that they are lazy loaded with another round trip to the database. I then tried setting the return type to Product, and I do get the 7 products, but their Order property is lazy loaded via another round trip if I try to access it.

I have also tried treating the result as an IMultipleResults, calling GetResult<Customer>() and GetResult<Order>() and GetResult<Product>() and manually piecing them together. In this case, though, only the first call to GetResult<>() will return something (any of the three entity types will work, but only for the first GetResult<>() call). The second and third GetResult<>() calls return null.

Thanks for any help anyone can provide. I keep thinking I am either missing something easy, or LINQ to SQL doesn't offer any public API to do this (even though it appears to do this on its own in the LoadsWith example above).

Answer 1

Anyone got any workable solutions to this?

I have similar - and more - issues attempting to utilise LINQ to populate nested object collections where the SQL select statements are in SPROCs and not dynamically generated by LINQ. Seems to me that applying various workarounds for fulfilling my data access requirements ends up with far more convoluted code than if I stuck to a traditional approach. I'll stop there, before this turns into a rant!

Answer 2

The best solution I could come up with is this:

1. Modify the sproc to actually return multiple result sets (the Customers, the Orders, and the Products from the above example)
2. Use IMultipleResults as usual (there are plenty of docs and examples of this) on the LINQ to SQL side to get those 3 separate IEnumerables
3. Create grouped collections of each non-root entity, i.e. var groupedOrders = allReturnedOrders.GroupBy(order => order.CustomerID) and var groupedProducts = allReturnedProducts.GroupBy(p => p.OrderID)
4. Loop through the non-leaf objects and use the EntitySet.SetSource() method to fill their collections of associated objects, i.e. customer.Orders.SetSource(groupedOrders.Single(orderGroup => orderGroup.Key == customer.CustomerID)

This gets your usual entity objects from your DBML in memory and it is not very much code (2-3 lines per level in the hierarchy you are creating). It does involve modifying the sproc, but it should actually be less data transfer from your database since you are not repeating all the higher level data like joining all the tables would do (repeating the Customer and Order columns for every Product in the above example).