LINQ [Language Integrated Query], it’s a programming model for any kind of data. It’s enable you to query data irrespective of data source hence its another data abstraction layer.

Following post will cover some topics which will be helpful to understand

• What is LINQ
• What is LINQ to SQL
• How LINQ to SQL works
• Why LINQ to SQL
• Getting Started
• Conclusion

What is LINQ?

The syntax is very similar to SQL is called query expression which you can write in your .NET language. LINQ query also operates on relational database like MS SQL Server; in this case it generates SQL statements.

Microsoft .NET framework 3.5 provided following flavor of LINQ implementation.

What is LINQ to SQL [ADO.NET]?

LINQ to SQL is one of the flavor of LINQ developed by Microsoft; it’s ORM [Object Relation Mapping] implementation in .NET Framework 3.5. It is a component which provides the capabilities to query a relational database. LINQ to SQL works only with Microsoft SQL Server, but can be extended to work with other databases as well. In fact, there are many implementations available on the internet that allow LINQ to SQL to work with other databases. So, in other words it is a thin layer on relational data with set of objects.

How LINQ to SQL Works?

When you write a query against LINQ to SQL objects, the following happens:

1. The runtime converts the query into Expression Trees and stores it in the memory.
2. When the query is executed or in other words when the results are requested, the LINQ engine converts the expression tree into a SQL query.
3. With the help of connection information provided to the LINQ engine, it sends the SQL query across to the database, executes it and gets the result back.
4. The result is then converted into the runtime object and that runtime object is finally returned as a result.

Why LINQ to SQL?

There are few advantage using LINQ to SQL.

1. As it’s working with Generic objects, its strongly typed code.
2. With use of Visual Studio, it provides design time IntelliSense to make development easy
3. The syntax is same for all types of data.
4. It make easy to transform data into objects.
5. Its data independent hence your code will not change if you change data.
6. Its transforming query expression into native SQK still Reduced injections attacks.
7. Its very quick to Generate DAL using IDE provided by Visual Studio
8. It provides compile time checking if any change made to any Database entity

Getting Started

Let’s take an example using both LINQ to SQL and ADO.NET. The following example gets a list of Users from the database that are active. First we will see how we can do this using ADO.NET.

string connectionString = GetConnectionString();  

DataSet userDataSet = new DataSet();

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = connection.CreateCommand();
    command.CommandText = "select * from Users where IsActive = 1";
    SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

    connection.Open();
    dataAdapter.Fill(userDataSet);
 

    if (userDataSet.Tables.Count > 0 && userDataSet.Tables[0].Rows.Count > 0)
    {
        Console.WriteLine("List of active users:");
        foreach (DataRow row in userDataSet.Tables[0].Rows)
        {
            Console.WriteLine(row["UserName"]);
        }
    }
}

This is common way using ASO.NET, now let see how it can be written in LINQ to SQL using expression

string connectionString = GetConnectionString();

using (UserDBDataContext context = new UserDBDataContext(connectionString))
{
    var users = from user in context.Users
                where user.IsActive == true
                select user;

    foreach (var user in users)
    {
        Console.WriteLine(user.UserName);
    }
}

Don't worry about syntax and keywords (such as var) for now. The output from both the examples will be same, that is a list of usernames that are active in the system. In both examples we easily iterate over the results using a simple foreach loop. One important concept of LINQ is deferred execution that is a LINQ query will not get executed until you try to iterate over it’s result or convert it’s result into a traditional .Net collection by calling methods like ToList() or ToArray(). In our case the query gets executed in the foreach loop as we are iterating over the result. Which means, the LINQ engine does not store the result of the query into users variable, but the query itself. Whenever that query is iterated over, it returns the result that may change depending on the values stored into the collection that the query uses which in our case is context.Users.

UserDbDataContext is an auto generated class which defines the bridge between LINQ and database. Each instance of DataContext will have a single connection to database. DataContext uses the connection of type IDbConnection so that it can map to different underlying database. DataContext is also able to call stored procedures.

How to create a dbml file.

dbml file generates the class files which can be used for Create, Read, Update and Delete (CRUD) through the mapping mechanism. Creating a dbml is very easy by following few easy steps with the use of Visual Studio IDE.

First step is to create a class library in which we can add the dbml file. Create a blank solution and add add new project of type Class Library.

After the Class Library is created, remove default class and add a new item to open up Add New Item dialog.

Select LINQ to SQL Classes template and specify a proper name.

This creates an empty dbml file which can be populated by dragging tables from the Server Explorer just like in ADO.NET.

Full Query Syntax

Previously we defined simple query over Users which is basically object collection. Every query starts with from and end with either select or group clause. The reason behind start with from is to let IntelliSense define a context for its capabilities. Following code will show full syntax of LINQ query expression.

query-expression = from-clause query-body

query-body: join-clause* 

    (from-clause join-clause* | let-clause | where-clause)* 
    orderby-clause? 
    (select-clause | groupby-clause)let-clause:  let itemName = selExpr 
    where-clause: where predExpr 

The first from clause can be followed by zero or more from or let clauses, followed by a single where clause. A let clause applies a name to the result of an expression, while a where clause defines a filter that will be applied to include specific items in the result. Each from clause is a generator that represents an iteration over a sequence on which query operates.

let-clause ::= let itemName = selExpr
where-clause ::= where predExpr

A from clause can be followed by any number of join clauses. The final select or group clause can be preceded by an orderby clause that applies an ordering to the results.

join-clause ::=
     join itemName in srcExpr on keyExpr equals keyExpr
     (into itemName)?
     orderby-clause ::= orderby (keyExpr (ascending | descending)?)*
     query-continuation ::= into itemName query-body

In the next post we will see how easily a query can be written with the help of above expressions.

Points to Note:

LINQ to SQL uses deferred execution methods which converts expression tree into SQL statements which is always valid for underlying relational database which is not different than a string containing a SQL statement. The best part is you can manipulate expression tree into memory before converting it to SQL to make your query more dynamic.

Conclusion:

LINQ is a way of embedding a query into .Net languages and LINQ to SQL extends it’s use to query the database right from the source code using your favorite .Net language. It’s not a replacement of stored procedures but a way to write stored procedures in your code files.

About the author