LINQ to SQL and multiple result sets in Stored Procedures


In this post I’m going to demonstrate how you would return and consume multiple result sets from a stored procedure in LINQ to SQL.

Imagine you have a stored procedure like this one below. Very simple – it’s just returns all customers and all orders.

clip_image001

In my LINQ to SQL Data Context, I have the following tables.

clip_image002

As well as the stored procedure definition.

clip_image003

Simple enough so far!

The first thing we need to do is create a partial class and define a new method returning type ‘IMultipleResults’. I’ve defined the one below to accept one parameter, which is a customerId.

clip_image004

You’ll notice that there’s a few attributes you need to add.

1. Function – this is the name of the stored procedure that will be called. It’s the same one we defined in our data context above. Note: the actual stored procedure name is irrelevant – it must be the name you have given the stored  procedure on the data context (which will be the same 99% of the time I’d imagine.)

2. ResultType – This is the mapping that basically says “Make the first result type of ‘Customer’ and the second result type of ‘Order’”.

Once we’ve defined this method, we can go ahead and consume it from our UI and/or OM as shown below.

clip_image005

As shown above, you call the method you defined, passing in a customer id in this case, which returns you a type of IMultipleResults. From there it’s just a case of calling the ‘GetResult’ method making sure to pass in the type of the object you want.

The results are shown below!

image

I hope this helps!

Jason

Comments (9)

  1. Him says:

    Jason, what if we want to return multiple resultsets that are not typed? like anonymous types?

    like for example:

    select pr.Title, pr.Description from products

    select o.orderid, u.userid from orders o

    inner join users u on o.userid = u.userid

  2. Leakybagel says:

    If you get errors like ‘Procedure expects parameter <@Name>, which was not supplied:

    Take a look at the stored procedure code created in the Designer.cs file, and copy the parameter list and attributes that match them to the SP params: [Parameter(Name="CustomerID", DbType="Int")]

    When passing multiple parameters to ExecuteMethodCall, you don’t need to create an object array, just list each parameter.

    Can you skip over one of the results, or is each result set returned in order?

  3. Hi!!

              I have understand you requierment but here I am having a query that in in combination of another table and returns 2 tables one is having same structure of one of the table of database and other is having just only one field now i want to convert in to dataset how can i do this please help me ?

               I am having following error when converting and the 2nd table have the field which is not acctually in the table so how can it be done ?

    [Error]

    The required column ‘UserId’ does not exist in the results.

    [/Error]

    Regards,

    Milnd Kansagara

  4. LightSpeed6 says:

    Thank you! This post helped me a lot! 🙂

  5. Gold says:

    Jason, what if we want to return multiple resultsets that are not typed? like anonymous types?

    for example:

    select * from EmployeeDetails inner join

                 Department on EmployeeDetails.DeptID=Department.DeptID

  6. Josh says:

    I think this is good when you have one customer selected. What if you want to return multiple customers like WHERE CustomerID < 500 the results come independent and you can't call for each Customer->Orders.

  7. How can it be resolved  if both results are from the same table means if the queries would be like this,

    1 : Select c.*, o.* From Customers c join Orders o on c.CustomerId = o.CustomerID

    2 : Select * From Customers

    In this case what we do??

    and more issue with results.GetResult<>

  8. Mahsa says:

    Please help me. I get this error "does no contain a definition for ExecuteMethodCall….."

    I use these namespaces :

    using System;

    using System.Collections.Generic;

    using System.Linq;

    using System.Web;

    using System.Reflection;

    using System.Data.Linq;

    using System.Data.Linq.Mapping;

    what should  I do?