Linq to SQL Stored Procedures with Multiple Results – IMultipleResults

5 באוקטובר 2007

Linq to SQL Stored Procedures with Multiple Results – IMultipleResults

Continuing my post series about Linq to SQL, this post talks about using stored procedures that return multiple result sets in Linq to SQL. If you missed any of my previous posts about Linq to SQL, here is a reminder:

  • Linq to SQL Attribute Based Mapping
  • Linq to SQL Xml Based Mapping
  • How To: Model Inheritance in Databases
  • Linq to SQL Inheritance
  • Linq to SQL Stored Procedures
  • Sql Server supports returning more than a single result type from a stored procedure. This was very useful when we wanted to fill a large Dataset with multiple tables in a single access to the database. Similarly, this is also very useful when using Linq to SQL, and

    Linq to SQL IMultipleResults

    So, having created the following stored procedure, based on the schema in the post Linq to SQL Stored Procedures.

    CREATE PROCEDURE dbo.GetPostByID

    (

        @PostID int

    )

    AS

        SELECT    *

        FROM      Posts AS p

        WHERE     p.PostID = @PostID

     

        SELECT    c.*

        FROM      Categories AS c

        JOIN      PostCategories AS pc

        ON        (pc.CategoryID = c.CategoryID)

        WHERE     pc.PostID = @PostID

    The calling method in the class the inherits from DataContext should look like:

    [Database(Name = "Blog")]

    public class BlogContext : DataContext

    {

        …

     

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

        [ResultType(typeof(Post))]

        [ResultType(typeof(Category))]

        public IMultipleResults GetPostByID(int postID)

        {

            IExecuteResult result =

                this.ExecuteMethodCall(this,

                                       ((MethodInfo)(MethodInfo.GetCurrentMethod())),

                                       postID);

            return (IMultipleResults)(result.ReturnValue);

        }

    }

    Notice that the method is decorated not only with the Function attribute that maps to the stored procedure name, but also with the ReturnType attributes with the types of the result sets that the stored procedure returns. Additionally, the method returns an untyped interface of IMultipleResults:

    public interface IMultipleResults : IFunctionResult, IDisposable

    {

        IEnumerable<TElement> GetResult<TElement>();

    }

    so the program can use this interface in order to retrieve the results:

    BlogContext ctx = new BlogContext(…);

     

    IMultipleResults results = ctx.GetPostByID(…);

     

    IEnumerable<Post> posts = results.GetResult<Post>();

    IEnumerable<Category> categories = results.GetResult<Category>();

    Enjoy!

    Add comment
    facebook linkedin twitter email

    17 comments

    1. Zvika6 באוקטובר 2007 ב 7:43

      Thank you for the great series of articles a bout linq to sql .
      Please generate and post the T-SQL used to create the 4 tables ( Post , blogs etc )
      and the relations between them.

    2. Carlos7 באוקטובר 2007 ב 16:53

      Could I do it with a Dynamic Linq Query (ExecuteQuery) that returns to me multiple resultsets ? How?

    3. Denny Ferrassoli29 בדצמבר 2007 ב 8:38

      Thanks Guy! This post as well as your previous post on Stored Procedures was extremely helpful. It was exactly what I was looking for, thanks!

    4. Tim Hardy13 בפברואר 2008 ב 21:22

      What happens if the first select returns no results? I have a scenario where the first select returns nothing and the second result returns something. I'm getting "Unable to cast object of type to ". If the first select returns something, and the second returns nothing, I get "Value cannot be null. Parameter name: source".

      I need some direction or info on how LINQ handles multiple results when one or more results has no records coming back.

    5. Alan Singfield4 במרץ 2008 ב 12:04

      Tim –

      Just make your first select return an empty recordset.

      So instead of

      BEGIN

      IF @param = 1
      SELECT a, b FROM c

      SELECT d, e FROM f

      END

      do

      BEGIN

      SELECT a, b FROM c
      WHERE @param = 1

      SELECT d, e FROM f

      END

    6. Pranil14 במרץ 2008 ב 16:15

      Does the function (which gets created for the SP), in this case GetPostByID, gets the return type IMultipleresult by default after dragging and dropping on the .dbml file?
      I am trying this with one of my SPs, which returns four tables, but in the .dbml.designer.cs file I see the function return type as ISingleResult.
      Can we change the .designer.cs file?

      Thanks in Advance,
      Pranil

    7. Pranil14 במרץ 2008 ב 18:16

      hi,

      I checked on some of the sites and came to a conclusion that, if your
      SP is returning more than one tables, then either you can use sqlmetal.exe tool or manually modify the designer.cs class to make return type for the method as IMultipleResults.
      Now the SP returns 6 tables(which is what I require), however there is data only in the first table, the other tables are empty(which is wrong), since if I execute the SP through server explorer I get data in all the 6 tables.

      Any idea what is going wrong?

      Thanks,
      Pranil

    8. Tabrez5 במאי 2008 ב 12:32

      i see your code it good.
      i have prob. where SP return two table and both from different join.

      in your SP field return from both query are from single table even join used in second query so it is easy to undustand the return type i.e Post and Category.

      in my case what is the retun type i take where both query have fields from different table also?
      e.g
      Table Name::
      emp(id,ename,salary,deptid)
      dept(deptid,dname)

      SP::
      select ename,salary,dname from emp,dept where emp.deptid=dept.deptid

      select dname,sum(salary) from emp,dept where emp.deptid=dept.deptid group by dname

    9. Abdul kalam9 במאי 2008 ב 13:24

      HI,
      I am using dataset with stored procedure(sp)
      but now i want to do these using linq but sp is needed
      i have sp' which return more the one result
      whose solution is imultipleresult as in about forum is given.
      But i got problem there
      where each result of that sp will have join from more then one table
      & in my dbml file only one result class is generated automatically.
      how can i create another class for my second or next result set.
      should i done menuly or any easy way is there.
      plz reply as soon as poossible

    10. Anas TM16 במאי 2008 ב 0:25

      How do we handle this if the second RecordSet is from another sp executed inside the 1st one.
      Am getting error when i change the Context Designer from IsingleResults to ImultipleResults.

      On executing it says "More than one result type declared for function
      'Sp_Name' that does not return IMultipleResults".

      Any Idea why is it so?
      Please reply as soon as poossible

    11. Uri1 ביולי 2008 ב 4:47

      the dbml file overrides the modifications to the designer.cs file and each time I add something to the model I have to re-type the IMultipleResults definition.

      Any recomendation?
      Leit

    12. Slawomir4 ביולי 2008 ב 13:08

      What about classes Post and Category? Where is mapping between sp results and those classes?

    13. Eduardo17 בספטמבר 2008 ב 16:00

      Uri, in case you're still wondering hoy do modify the dbml file and avoid the so call to be overwritten, you can create a partial class for your DataContext and put the code to call the SP in there.

      Eduardo

    14. Kalapana28 באוקטובר 2008 ב 17:10

      Any one this code in VB.Net

    15. Mazhar Karimi28 בפברואר 2009 ב 9:46

      I think there is a problem with dbml, if you create a stored procedure returning multiple results, but you also do some logics before returning them, for example declaring a variable of some type (Note: you are not returning that variable, it is being used internally). But when you will drop that SP into the dbml Designer, dbml will create ISingleResult with the compatible type of that variable which you created in that SP.

      I solved that problem by opening dbm into XML editor, manually addming multiple elements. and saved it. Now when I opened dbml again in visual studio, it automatically created addition classes in designer.cs(with the same elements names which i created), and change the ISingleResult with IMultipleResult.

    16. Joseph jelakser Fernando23 ביוני 2009 ב 10:01

      it is really a fabulous article.it also enabled me to think out of box
      Thanks a lot for this post

    Comments are closed.