Introduction:
From last Week,
I have started to write a few articles to explain LINQ to SQL, LINQ to Entities,
LINQ to XML, LINQ to objects, and LINQ to dataset.
In this
article I will describe LINQ to SQL in detail with some sample
examples.
Definition
LINQ to SQL
is a component of .NET Framework version 3.5 that provides a run-time
infrastructure for managing relational data as objects.
In Detail LINQ TO SQL:
Some people
says LINQ (Language Integrated Query) is to replaces the traditional sql query
execution process.
But reality
is that it doesn’t only manipulate database, but it can also be used to manipulate
array/list collections, XML and objects.
In LINQ to
SQL, the data model of a relational database is mapped to an object model
expressed in the programming language of the developer. When the application
runs, LINQ to SQL translates into SQL the language-integrated queries in the
object model and sends them to the database for execution. When the database
returns the results, LINQ to SQL translates them back to objects that you can
work with in your own programming language.
Following diagram explains how LINQ interacts
with sql server database

LINQ to SQL Supports:
1. Transactions
2. Views
3. Stored
Procedures
4. User-defined
functions
It also
provides an easy way to integrate data validation and business logic rules into
your data model, and supports single table inheritance in the object model.
LINQ to SQL programming:
How to use stored procedure using
LINQ to SQL?
LINQ to SQL maps output parameters to reference parameters, and for value types
declares the parameter as nullable.
The
following example takes a single input parameter (the UserID) and returns an
out parameter (the total Leaves for that Employee).
T-SQL Stored procedure:
Create PROCEDURE [dbo].[UserTotalLeaves_SP]
-- Add the
parameters for the stored procedure here
@userId int,
@TotalLeaves int output
AS
BEGIN
SELECT
@TotalLeaves= sum(leaves) from LeaveEntitlement where
UserId=@userId
END
Execute same stored procedure using LINQ TO SQL:
[Function(Name = "dbo.UserTotalLeaves_SP")]
[return: Parameter(DbType= "Int")]
[public int UserLeaveTotal([Parameter(Name = "userId", DbType = "int")] int userId, [Parameter(Name
= "TotalLeaves", DbType = "int")] ref System.Nullable<int> TotalLeaves)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), userId, TotalLeaves);
TotalLeaves =((System.Nullable<decimal>)(result.GetParameterValue(1)));
return ((int)(result.ReturnValue));
}
Querying database using LINQ TO SQL:
I have created DataContext class in .net and named something like ‘ApplicationServices (These DataContext classes are now
responsible for .NET To Database communications). In the linq query syntax,
this object will be used to present the database.
To query a
database using LINQ to SQL, we first need to construct a DataContext object,
like this
ApplicationServices BlogDbContext = new ApplicationServices();
Retrieve records from table:
Now we can use this LINQ query syntax to retrieve records
from the database table ,Following example
Example:
public
List<Designation>
GetEmployeeAllDesignations()
{
List<Designation>
Designations = new List<Designation>();
using
(ApplicationServices DbContext = new ApplicationServices())
{
// var LeaveTypes = (from d in DbContext.Designation
Designations = (from d in
DbContext.Designation
where
d.DesId != 0
orderby
d.DesId
select
d).ToList();
}
return
Designations;
}
I will explain
LINQ to SQL CRUD (insert, update, delete) operation example in next article.
LINQ TO SQL JOIN Example:
LINQ to SQL
join example to retrieve Leave Types
Example:
Following
example returns leave types by added user details using LINQ to SQL inner joins.
We can
perform Inner join, outer joins and some custom joins with the help of LINQ to
SQL.
public List<LeaveType>
GetAllLeavTypes()
{
List<LeaveType>
Types = new List<LeaveType>();
using
(ApplicationServices BlogDbContext = new ApplicationServices())
{
var LeaveTypes = (from l in
BlogDbContext.LeaveType
join e in BlogDbContext.User
on new { PID = l.AddedBy }
equals
new
{ PID = e.UserId }
select
new
{
TypeId = l.TypeId,
LeaveTitle = l.LeaveTitle,
Description = l.Description,
AddedBy = l.AddedBy,
UpdateBy = l.UpdateBy,
CreatedDate = l.CreatedDate,
UpdateDate
= l.UpdateDate,
CreatedBy = e.UserName
}
).ToList();
foreach
(var
l in LeaveTypes)
{
LeaveType
type = new LeaveType();
type.CreatedBy
= new aspnet_Users();
type.TypeId =
l.TypeId;
type.AddedBy =
l.AddedBy;
type.LeaveTitle = l.LeaveTitle;
type.Description =
l.Description;
type.CreatedDate = l.CreatedDate;
type.CreatedBy.UserName = l.CreatedBy;
type.UpdateBy
= l.UpdateBy;
type.UpdateDate = l.UpdateDate;
Types.Add(type);
}
}
return
Types;
}