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

  • Article

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