How to call stored procedure using LINQ to SQL

Using LINQ you can also call stored procedures when creating a data layer. The LINQ to SQL object relational mapping that ships in .NET 3.5 is flexible and can be used to create data model classes whose object model can be independent of the underlying database schema. It encapsulate business logic and validation rules that work regardless of whether the data model is populated via dynamic SQL or via Stored Procedures.


Before we can start using LINQ to call stored procedure, first we need to create new connection and map it on LINQ To SQL designer as explained on my previous article: What is LINQ to SQL? An Introduction

If you already finished mapping the stored procedure on LINQ to SQL designer, lets move forward to the actual code. On the example below, I used the stored procedure "GetProductsByCategory" which accepts category as parameter.

using (NorthwindDataContext datacontext = new NorthwindDataContext())
{
//retrieve all product that are beverage using stored procedure "GetProductsByCategory"
var products = datacontext.GetProductsByCategory("Beverages");
//display all retrieved products from stored procedure
foreach(Product p in products)
{
Console.WriteLine(p.ProductName);
}
}

Translation in VB.NET

Dim datacontext As New NorthwindDataContext()

'retrieve all product that are beverage using stored procedure "GetProductsByCategory"
Dim products = datacontext.GetProductsByCategory("Beverages");
'display all retrieved products from stored procedure
For Each p as Product In products
Console.WriteLine(p.ProductName)
Next

As you can see, retrieving records through stored procedure using LINQ to SQL is relatively easy. Try it now on your application.

For more LINQ coding tips, subscribe now