Click here to Skip to main content
11,922,641 members (56,768 online)
Click here to Skip to main content
Add your own
alternative version


86 bookmarked

Six simple steps to use a Stored Procedure in LINQ

, 7 Jul 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Six simple steps to use a Stored Procedure in LINQ.

Table of contents


This is an extremely small article which describes how to flourish LINQ objects using a Stored Procedure. What provoked me to write this article is the ExecuteMethodCall function which helps to execute Stored Procedures in LINQ. As this is a protected function, it changes the way you architect the DAL using the DataContext class and probably you would like to tweak and consider some options here. You will see more details when you read through the steps below. I am writing a huge series of LINQ FAQ and these small articles form small sprints to complete the huge FAQ series. I hope you enjoy it.

Watch my 200 videos on Microsoft technologies @

LINQ basics

This article assumes that you have a basic knowledge of how entity objects can be flourished using LINQ. In case you are not aware of the basics of LINQ to SQL mapping, you can read my article to understand the basic LINQ concepts from OneManyandOneOneLINQ.aspx.

Step 1: Create a Stored Procedure

Below is the Stored Procedure which we will use to flourish LINQ objects:

Create PROCEDURE dbo.usp_SelectCustomer
Select CustomerId,CustomerCode,CustomerName from Customer

Step 2: Create a LINQ Entity

The above Stored Procedure returns CustomerId, CustomerCode, and CustomerName, so we need to prepare a LINQ entity as per the returning Stored Procedure data. In case you are not aware of LINQ entities, please read the basics at OneManyandOneOneLINQ.aspx.

[Table(Name = "Customer")]
public class clsCustomerEntity
    private int _CustomerId;
    private string _CustomerCode;
    private string _CustomerName;

    [Column(DbType = "nvarchar(50)")]
    public string CustomerCode
            _CustomerCode = value;
            return _CustomerCode;

    [Column(DbType = "nvarchar(50)")]
    public string CustomerName
            _CustomerName = value;
            return _CustomerName;

    [Column(DbType = "int", IsPrimaryKey = true)]
    public int CustomerId
            _CustomerId = value;
            return _CustomerId;

Step 3: Inherit from the DataContext class

In order to execute Stored Procedures, LINQ has provided an ExecuteMethod call function which belongs to the DataContext class. This function returns an ISingleresult of an entity collection. The ExecuteMethod call function is a protected function and can only be invoked through inheritance. Methods and functions from which we call our Stored Procedures normally forms our DAL. In other words, ExecuteMethod should be a part of our DAL.

As said, the function is purely protected, you can only invoke it by inheritance and not aggregation. I am really not sure why this compulsion is put by Microsoft, in other words, we need to create an extra class which inherits from DataContext and then put in the corresponding function calls for Stored Procedures. Below is the code snippet where we inherit from the DataContext class and create a new DAL class called ClsMyContext.

public class clsMyContext : DataContext

Step 4: Attribute using the Function attribute

We have created the GetCustomerAll function which is attributed with the Function attribute from the System.Data.Linq.Mapping namespace. The Function attribute has a name parameter which specifies the Stored Procedure name; currently the Stored Procedure is usp_SelectCustomer, as defined in the previous steps.

The IsComposable parameter defines whether this method call is for a Stored Procedure or UDF, i.e., User Defined Function. If IsComposable is false, that means it’s a Stored Procedure, and if it is true, that means it’s a user defined function.

[Function(Name = "usp_SelectCustomer", IsComposable = false)]
public ISingleResult<clsCustomerEntity> getCustomerAll()

Step 5: Invoke the ExecuteMethod call

Now it’s time to fill in the empty function GetCustomerAll. Below is the code snippet to execute the ExecuteMethod call. This invocation returns back an IExecuteResult object.

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

The object returned from IExecuteResult has a ReturnValue property from which we can get the results collection of the ClsCustomerEntity type.

ISingleResult<clsCustomerEntity> objresults = (ISingleResult<clsCustomerEntity>) objResult.ReturnValue;

Below is the complete code snippet with the function:

[Function(Name = "usp_SelectCustomer", IsComposable = false)]
public ISingleResult<clsCustomerEntity> getCustomerAll()
    IExecuteResult objResult = 

    ISingleResult<clsCustomerEntity> objresults = 
        (ISingleResult<clsCustomerEntity>) objResult.ReturnValue;
    return objresults;

Step 6: Finally we call the data context in the client

In the final step, we create the context object, call our function, and loop through the object collection display data.

clsMyContext objContext = new clsMyContext(strConnectionString);
foreach(var row in objContext.getCustomerAll())


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Shivprasad koirala
India India

You may also be interested in...

Comments and Discussions

GeneralGood work Pin
Menon Santosh6-Dec-13 20:17
professionalMenon Santosh6-Dec-13 20:17 
QuestionDataContext being Open/Closed Pin
Member 1041029618-Nov-13 22:16
memberMember 1041029618-Nov-13 22:16 
QuestionPassing Parameters? Pin
oalbad13-Jan-12 9:19
memberoalbad13-Jan-12 9:19 
AnswerRe: Passing Parameters? Pin
carmel26-Nov-14 20:55
membercarmel26-Nov-14 20:55 
GeneralMy vote of 3 Pin
prabhatmishra3-Jan-11 23:45
memberprabhatmishra3-Jan-11 23:45 
QuestionMaster - Detail Entries using Stored Procedures Pin
ravi@hitsoft26-Aug-10 22:00
memberravi@hitsoft26-Aug-10 22:00 
QuestionUsing stored procedure in LINQ Pin
Tapas Ranjan Singh14-Sep-09 20:45
memberTapas Ranjan Singh14-Sep-09 20:45 
GeneralThank you Pin
EricFromMars14-Jul-09 23:52
memberEricFromMars14-Jul-09 23:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.151125.1 | Last Updated 8 Jul 2009
Article Copyright 2009 by Shivprasad koirala
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid