LINQ to SQL Tips 5: Using stored procs that return multiple results

Often I get the following questions about stored procs in LINQ to SQL:

1. I have sproc that returns multiple results. How can I use it in LINQ to SQL?
2. Can I use a sproc that returns results of different shapes; e.g. Customers and Orders?
3. I drag-dropped a sproc returning multiple results on the designer surface. But I don't get a method returning multiple results. What is missing?

Here is the overall answer.

Yes, you can use sprocs returning multiple results of different shapes. Here is an example:

This should be added to your partial class that is derived from DataContext:

            [Function(Name="dbo.MultipleResultTypesSequentially")]

            [ResultType(typeof(Product))]

            [ResultType(typeof(Customer))]

            public IMultipleResults MultipleResultTypesSequentially()

            {

                  IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));

                  return ((IMultipleResults)(result.ReturnValue));

            }

In consuming code, here is how it can be used

 

            using(IMultipleResults sprocResults = db.MultipleResultTypesSequentially()) {

                List<Product> prods = sprocResults.GetResult<Product>().ToList();

                List<Customer> custs = sprocResults.GetResult<Customer>().ToList();

                  …

            }

And no, the designer does not support this feature. So you have to add the method in your partial class. SqlMetal does however extract the sproc. The reason for that is an implementation detail: the two use the same code generator but different database schema extractors.

Anyway, with or without SqlMetal, you can use the feature as described above.

Dinesh

Comments

• Anonymous
  May 17, 2008
  I tried to tweak stored procedure "CustOrdersDetail" in NorthWind Database and try to run according to your tip i.e. returning selected columns from multiple tables Customer and Order but i get the errors:

The required column 'ProductID' does not exist in the results.

The required column 'OrderID' does not exist in the results.

My Front Code:

int orderID = 10248;        using (IMultipleResults multipleResults = db.CustOrdersDetailMultiple(orderID))        {            List<Product> products = multipleResults.GetResult<Product>().ToList();            List<Order> orders = multipleResults.GetResult<Order>().ToList();        } DataContext Code:

[Function(Name = "dbo.CustOrdersDetail")]    [ResultType(typeof(Product))]    [ResultType(typeof(Order))]    public IMultipleResults CustOrdersDetailMultiple([Parameter(Name = "OrderID", DbType = "Int")] System.Nullable<int> orderID)    {        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), orderID);        return ((IMultipleResults)(result.ReturnValue));    }

• Anonymous
  May 19, 2008
  Dinesh Kularni a publié depuis novembre 5 astuces sur LINQ To SQL : LINQ to SQL Tips 1: how to map an

• Anonymous
  May 23, 2008
  Prajwal, For some reason, the spam filter snagged your comment until I got around to checking all unpulbishwed comments. My apologies for the unwarranted filtering by the spam filter. Apparently CustOrdersDetail does not have the necessary columns. So you can do one of two things:

1. Use a sproc that has the ID columns if you want to use the entity types; or
2. To use the existing sproc, define new non-entity types (e.g. OrderProjection instead of Order) and map them so that they don't contain the ID columns. Multiple results or any other such feature will not change what the materializer needs to create an entity type. HTH Dinesh

• Anonymous
  May 26, 2008
  Hello Dinesh I want to achive this IMultipleResults , when you say , "Change the partial class" what do you mean ? the designer.cs ? whouldn't it earse it after any change to the dbml ? or you meant something else ? TIA

• Anonymous
  May 26, 2008
  Hello Ronen, I was assuming that you already have something in a partial class for DataContext and that you would need to add what I showed in my post. When you start, the designer creates its own generated file (e.g. Northwind.designer.cs). If you right click on the design surface (not on a class), and select "View code", you will get a file created for writing your class (e.g. Northwind.cs). It contains an empty partial class declaration. You can add the method wrapper for multiple result returning sproc in that class. I should probably cover this in a separate post with a few figures. Dinesh

• Anonymous
  June 06, 2008
  The code generated for LINQ to SQL is a set of partial classes - one for your DataContext and one per

• Anonymous
  June 10, 2008
  Anyone has vb.net version of this code?