LINQ enables us to work with different data sources using similar types of code styles like SQL Database, XMLDocuments, and in-memory objects. Another benefit of using LINQ is that it provides intellisense and compile time error checking .

For example:

We are developing a Dot NET Application and this application needs to fetch data from different data sources like Databases, XML and in memory objects like lists of customers, lists of orders, lists of sales and so on. For a developer who is working on Dot NET applications in order to fetch data from different data sources in order he has to understand the technology and syntax of these different data sources. In order to fetch data from SQL he needs to understand ADO.NET and T-SQL that is specific to SQL Database. Similarly, for XML Documents the developer needs to understand XPATH and XSLT syntax. Also, to fetch data from in memory objects a developer needs to understand the arrays and generics of syntax and code.

LINQ Architecture:

This is the architecture for LINQ; we all know that we can develop .NET application. Similarly, LINQ programming returns any of the above mentioned .NET programming languages. Between the actual LINQ query and underlying data source there is another component called LINQ provider. The responsibility of this LINQ provider is to convert this LINQ query into a format so that the underlying data source can understand it.

Let's say the application is trying to fetch data from a SQL database; if that is the case, this LINQ query will fit into the LINQ to SQL provider which is going to convert this LINQ query into T-SQL so that the underlying database can understand. Similarly, if the LINQ query has to fetch data from XML Documents the same LINQ query is going to be fed into this, which is LINQ to XML provider, which is going to convert this LINQ query into XLST so that the XML datasource can understand it.

Let’s understand with an example

In SQL Server, we have this data

Now we will write some ADO.NET Code; to do that create an empty application and add a webform:

Add a gridview to that webform:

Now let's add connection string in our web.config file

Now let’s write the ADO.NET code for our form:

This is a simple ADO.NET code. We are reading a connection string from Web.config file and using that connection string we are building SQL Connection object and then we are passing the actual SQL command which we want executed against SQL Database. So this is a T-SQL specific to SQL Database. Look at the query , we have the firstname, lastname, gender from table name where gender=Male . Notice that we don’t have any form of intellisense; here this query is error prone. We can make mistakes here while writing a query; instead of Firstname, we can make a mistake such as FirstName1 and so on . So we don’t have a proper intellisense while writing a query. Then, we are passing of list of students, opening the connection, executing a list of commands, and then we are looping through objects. We are creating instance of an object, student, and then we are retrieving data from the reader and populating the respective properties of the student object and then adding the student object to the list and closing the connection.

So, let's quickly run this and see whether it works or not.

So, it has fetched only male students. Now, we don’t have intellisense here so let’s make some mistake and try to run the form:

I had changed it to LastName1 now let’s run the solution

We are getting this error. Since, ADO.NET queries are buried into strings we don’t have intellisense or error checking. With LINQ, we do get that so we will re-write the same example using LINQ.

Now delete the existing webform and add another webform

Add the grid view control and let’s get back to code behind file .Now we will add a component LINQ to SQL Classes as

Right Click on the project folder -> Add -> New Item -> Data

Select LINQ to SQL and give desirable name as Sample.dbml

Drag and drop the table on Sample.dbml and now go back to sample.designer.csfile. We will understand the code in detail in our later articles; now we will write some code in our code behind file. In addition to this, in sample.designer.cs file we have this

SampleDataContext is the gateway into the database to the instance of sampleDataContext class in order to retrieve data from the database. So the first thing which we need to do is to create instance of that class:

So what we want to do retrieve data from SQL database using a LINQ query so whatever the LINQ query is going to return we are going to set that as datasource for the gridview control

We had used from keyword here and created a variable student in which all the students are present in datacontext.students; now we don’t want all the students we want only mail .

First, we will see for all students:

Now , let's quickly run the solution and let's see the output

Now we want to filter them; we want only mail so we will apply where condition:

As you can see we got the intellisense which we were talking about earlier

We had applied where condition here as we want only males; now run the solution

As you can see from the output we had got only males. Now let's try to modify the where condition -- we will specify the as column Gender1 and try to build our solution

As you can see from the above output it's throwing an error .

So this is our LINQ query. We want this query to be able to retrieve data from underlying SQL server database.Can SQL server understand this LINQ query ? NO

SQL server can only understand T-SQL so there has to be someone in between who is converting this LINQ query into T-SQL query so thr underlying SQL database can underatand it. Who is that going to be? That is the LINQ provider's job. LINQ to SQL is going to convert that to T-SQL and send it to SQL Database .

To prove that we will run the solution and launch SQL server profiler and see the T-SQL statement as

So this is what our SQL server profiler looks like

Now lets run the form and try to monitor the query in SQL server profiler

Now just copy that statement and paste it in SQL server and run that statement and you will see the same output

Now we saw how a LINQ query works with databases, now let's see an example of in memory objects array

So here we created array in which we want to display even numbers . So our output will be:

Conclusion

So, we worked with different data sources. One is the T-SQL data source, and the other is in memory data source. But the LINQ query is similar -- depending on the data source it's not going to change much. It's actually the provider who is going to covert the LINQ query into the syntax which is specific to the underlying data source.