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

5
votes

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:

enter image description here

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).

1
linq-to-sql
Multiple result sets?? Or multiple rows (i.e. a table)? – RThomas
Multiple Result sets. Stackoverflow isn't letting me include the code. The stored procedure should contain two lines: return all rows from the customer table and return all rows from the orders table. Two select statements, one sproc. – longda
if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it. Works like a charm (once you know how to use it) – marc_s
Couldn't you just return a JOIN between the two tables as a single result set?? – marc_s
@marc_s: I'd rather not as that would return a lot of extra data that is not needed (in this case, the customer data would be duplicated for each row of order data). Plus, I already know how to do that. I'd much rather find a solution to the stated problem: two or more result sets being returned from the sproc. – longda

1 Answers

6
votes

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