How do I use LINQ to SQL with stored procedures returning multiple result sets without ORM?

Question

http://blogs.msdn.com/b/dditweb/archive/2008/05/06/linq-to-sql-and-multiple-result-sets-in-stored-procedures.aspx

Similar to this link however the project I'm working on doesn't use the ORM component of LINQ to SQL (we use it more for quickly generating the ADO.Net interface to the db).

Currently, the pattern we follow is:

var result = myDataContext.GetAllCustomersAndOrders();

And the stored procedure looks like this:

Are there extra steps I need to take? Do I need to extend the generated dbml or the data context partial class file?

Hopefully this makes sense... It's a bit difficult to explain and all the examples I've found use the ORM piece of the dbml (dragging and dropping tables onto the dbml designer surface).

Answer 1

Yes, you can do this, but i will require creating a partial class for your dbml.

So, if your dbml file is MyLinqToSQL.dbml

• open the dbml designer
• drag the SP onto the design surface
• save
• and then press F7.

This will create a partial class of MyLinqToSQL.cs.

Now...

• open the auto-generated MyLinqToSQL.designer.cs
• copy the access method with the same name as the SP (the one you want to return multiple sets from.)
• Insert this into the body of the partial class in MyLinqToSQL.cs you just created.
• change the name of the method to something at least slightly different
• change the return type from ISingleResult to IMultipleResult.
• decorate your new class with System.Data.Linq.Mapping.ResultType attributes to define your return set types.

All of the resultsets will be returned in a single collection of IQueryable.

You can then use reflection to determine the type of each collection item and then cast them for use.

See http://kishor-naik-dotnet.blogspot.com/2011/12/linq-multiple-result-set-of-procedure.html