Linq to SQL Stored Procedures

5 באוקטובר 2007

Linq to SQL Stored Procedures

Continuing my Linq to SQL post series, this post talks about using Linq to SQL Stored Procedures. Note that this post is being written based on Visual Studio 2008 Beta 2 release, so if you're using later releases, things might be a little different.

To demonstrate using stored procedures, I am using a blog site database schema:

Linq to SQL Stored Procedures

and in order to retrieve the posts that belongs to a certain category, I have also created a stored procedure:

CREATE PROCEDURE dbo.GetPostsByCategory

(

    @CategoryID int

)

 

AS

    SELECT p.*

    FORM   Posts AS p

    JOIN   PostCategories AS pc

    ON     p.PostID      = pc.PostID

    WHERE  pc.CategoryID = @CategoryID

Linq to SQL Attribute Based Mapping for Stored Procedures

Using Linq to SQL Attribute Based Mapping in order to execute this stored procedure, we have to create a DataContext. By this, I don't mean creating an instance of a DataContext, by to creating a class that inherits from DataContext. This is because the method being called in order execute the stored procedures is only available as a protected method in the DataContext class.

[Database(Name="Blog")]

public class BlogContext : DataContext

{

    public BlogContext(string connectionString)

        :base(connectionString)

    {

    }

}

The class that inherits from the DataContext can call ExecuteMethodCall method that executes the stored procedure according to the mapping source. In our case, the mapping source is attribute based, so the method will examine the Function attribute defined on the wrapping method.

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

public IEnumerable<Post> GetPostsByCategory(int categoryId)

{

    IExecuteResult result =

        this.ExecuteMethodCall(this,

                               (MethodInfo)MethodInfo.GetCurrentMethod(),

                               categoryId);

    return result.ReturnValue as IEnumerable<Post>;

 

}

The ExecuteMethodCall method takes the instance of the DataContext and the wrapping method information in order to query the mapping source for the appropriate mapping. Additionally it takes the parameters for the stored procedure. This method returns a IExecuteResult interface, that its ReturnValue can be cast to the return value of the method.

Linq to SQL Xml Based Mapping for Stored Procedures

Using Linq to SQL Xml Based Mapping is very easy when mapping a stored procedure. Under the database element, along with the TableMapping elements there is also a FunctionMapping element.

<Database Name="Blog"

          xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007"> 

   …

 

  <Function Name="dbo.GetPostsByCategory" Method="GetPostsByCategory">

    <Parameter Name="categoryId" Parameter="CategoryID" />

    <ElementType Name="Post" />

  </Function>

</Database>

The Function element has the Name attribute which is the name of the stored procedure, and the method is the name of the calling method in the class the inherits from DataContext. Below the Function element we can see the ParameterMapping elements that map parameters of the method to parameters in the stored procedure. The last element is ElementType which specifies the return type of the stored procedure, usually another type that is defined earlier in this mapping file.

public IEnumerable<Post> GetPostsByCategory(int categoryId)

{

    IExecuteResult result =

        this.ExecuteMethodCall(this,

                               (MethodInfo)MethodInfo.GetCurrentMethod(),

                               categoryId);

    return result.ReturnValue as IEnumerable<Post>;

 

}

The method that executes the stored procedure really looks the same, but this time without the [Function] attribute above.

Using the Object Relational Designer

If we have a Linq to SQL model (a .dbml file) opened in the designer, that shows the entities mapped to the database objects (tables, views), than we also have a pane for stored procedures and user defined functions (the right pane in the designer).

Linq to SQL Stored Procedures

We can drag the stored procedure node from the Server Explorer to the above pane. This will result in the stored procedure shows up in the right pane and Visual Studio generating the code for the execution of the stored procedure.

[System.Data.Linq.Mapping.DatabaseAttribute(Name="Blog")]

public partial class BlogDataContext : System.Data.Linq.DataContext

{

    …

 

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

    public ISingleResult<GetPostsByCategoryResult> GetPostsByCategory(

        [Parameter(Name="CategoryID", DbType="Int")] System.Nullable<int> categoryID)

    {

        IExecuteResult result = this.ExecuteMethodCall(this,

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

                                                      categoryID);

        return ((ISingleResult<GetPostsByCategoryResult>)(result.ReturnValue));

    }

}

Notice that Visual Studio couldn't have known that this method returns an element of type Post, than it has generated a new type called GetPostsByCategoryResult. The return type can be changed in the properties pane of the stored procedure – Change the default (Auto-generated Type) to one of the types in the model.

Linq to SQL Stored Procedures

Conclusion

Linq to SQL supports executing stored procedures, that are called from a method in a class the inherits from DataContext. Mapping can be done both via Xml Based Mapping and Attribute Based Mapping, but the simplest way is using the Object Relational Designer.

Add comment
facebook linkedin twitter email

7 comments

  1. meh1 באפריל 2008 ב 14:59

    thank you. you saved my day..

  2. quachnguyen14 באוגוסט 2008 ב 1:20

    Sine I read your article, I solved my problem.

    Problem i solved : (Auto-generated Type) 😉

    Thank you so much!

  3. Andy Ray22 בדצמבר 2008 ב 21:08

    Hi,

    I have created a stored proc in SQL which when I am dragging to my project is showing up as:

    public int usp_SelectBulkLoadFilter([Parameter(DbType="VarChar(7)")] string subId, [Parameter(DbType="VarChar(10)")] string entDate, [Parameter(DbType="NVarChar(2)")] string recType, [Parameter(DbType="Int")] System.Nullable batchCnt)
    {
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), subId, entDate, recType, batchCnt);
    return ((int)(result.ReturnValue));
    }

    I was expecting the return value to be something like :

    return ((ISingleResult)(result.ReturnValue));

    Do you know what is the problem here. Because programaticcally I cannot do much when I try to use it the way i want to.
    Any help will be much appreciated.

    My email: aniray369@gmail.com.

  4. Bebandit6 בינואר 2009 ב 20:57

    That last part about changing the return type in the designer was exaactly what I was looking for! Thanks.

  5. Ashfaqh26 בפברואר 2009 ב 15:24

    I have a different issue here:
    when I run the stored procedure using LINQ which returns the ISingleResult by default. The stored proc is supposed to return an XML output. But when I capture the trace by running the profiler, it generates a slightly modified query which produces the XML result plus an integer (0 or 1). I want to capture the XML output. How can I do it.
    Every time I debug, the only value which is being returned is an Integer and hence I am unable to type cast it to my required type.
    Is this an issue with LINQToSQL autogenerated class or with my code.
    Please suggest.

  6. Jim Wooley19 ביוני 2009 ב 3:34

    In addition to changing the type in the designer through setting the Return Type explicitly, you can often drag the procedure directly onto the target data type rather than dropping it in the method pane. This will effectively set the return type for you (assuming all of the properties line up correctly).

Comments are closed.