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

Published 16 May 08 06:59 by Dinesh.Kulkarni

Comments

# Prajwal Tuladhar said on May 18, 2008 12:33 AM:

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

   }

# Matthieu MEZIL said on May 20, 2008 4:26 AM:

Dinesh Kularni a publié depuis novembre 5 astuces sur LINQ To SQL : LINQ to SQL Tips 1: how to map an

# Dinesh.Kulkarni said on May 23, 2008 8:39 PM:

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

# ronen said on May 26, 2008 9:40 AM:

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

# Dinesh.Kulkarni said on May 26, 2008 12:36 PM:

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

# Dinesh's Cyberstation said on June 6, 2008 9:16 PM:

The code generated for LINQ to SQL is a set of partial classes - one for your DataContext and one per

# John said on June 10, 2008 8:43 AM:

Anyone has vb.net version of this code?

New Comments to this post are disabled

About Dinesh.Kulkarni

I am a program manager in the Visual C# Product Unit of Microsoft. I am currently working on the LINQ project with specific responsibility for DLinq. Previously, I have been in a PM in SQL Server working on ObjectSpaces and DataSet. In pre-MS life, I have worked for companies ranging from startup to IBM on a wide range of software projects. Before I started working, I did M.S.E.E. and Ph.D. (CSE) from the University of Notre Dame and B.Tech. E.E. from IIT Bombay, India.

Search

This Blog

Syndication

Page view tracker