LINQ to SQL: returning multiple result sets

As you probably know LINQ to SQL supports stored procedure (SP). You can drag&drop a SP in the LINQ to SQL Designer and the magic happens: a new method inside the DataContext class is generated and a new return type is defined by the designer to represent this result (the name of the type is composed by the SP name followed by Result).

Of course it is possible to map the return result to an existing data model class you have already defined in the LINQ to SQL designer: assuming you have a getCustomers SP which returns all columns of a Customers table and you have created a Customer Type inside the designer by dragging&dropping the Customers table, you can write something like this:

List<Customer> cust = dbContext.getCustomers().ToList();

So far, nothing new…

Suppose now that you have a SP that returns multiple result sets. Just be clear, this is not a discussion about if it is good or not returning multiple result set from a SP; but I just want to show you what you can do if you find yourself in such a situation (maybe you just need to support an old written SP…).

Let’s first define a simple SP that returns all customers and all products (I know that this doesn’t really make sense, but it’s just for didactical reason).

CREATE PROCEDURE [dbo].[ReturnMultipleRS]

AS

      select * from customers

      select * from products

You can now create a ConsoleApplication project and as we want to built a LINQ to SQL sample, you can now add a LINQ to SQL classes file to the new created project and name it Northwind. You can now open the LINQ to SQL designer and drag&drop the customers and the products tables and the ReturnMultipleRS SP, as shown in the following figure:

If you built the whole project and you open the .designer file of the generated NorthwindDataContext class you will see that the LINQ to SQL designer has generated a ReturnMultipleRS() method that maps our SP.

Note that the return type implements the ISingleResult interface. What’s happened? Per default the LINQ to SQL designer generated a method that is able to return a single “list” of a defined type (in our case ReturnMultipleRSResult). What does it mean? If you execute the following code inside your Console Application, you will see that result will contain just the result of the first query (select * from customers), so just customers. 

Do we have a workaround? Yes, of course we have J! You can define a new method (GetMultipleRS) inside theNorthwindDataContext class by using the partial class technology, that it’s very similar to the one generated by the designer:

What are the differences between this and the ReturnMultipleRS method generated by the designer? This method (GetMultipleRSi)implements the IMultipleResults interface and the result types are explicitly defined in form of attributes.

You can now make use of this new created method in the following way:

Hope this helps,
Ken