Glimpse on LINQ TO SQL
What is Linq to SQL Server
So far all this time we have been using the ADO.NET to communicate with the Database, extracting and manipulating data. Linq to SQL opens a new window to do non other than the same thing. Linq to SQL provides ways of extracting and manipulating data on SQL Server Databases only.
How does it do it?
So, how does LINQ do it? The concept behind it is, it converts a Linq Expression to a SQL query at the backstage. But it’s not straight forward as such.
In Linq, first will look how it querys SQL data? Obviously, the DB structure should be mapped to some format understandable by linq. And that is done using the Linq to SQL class. I assume that application is already connected to some datasource.
- Add a Linq to SQL Class to your solution; this class automatically creates necessary functionality to work with the table in the DB.
- Open the server explores, connect to your DB and add some tables to this class; when tables are added a Linq to SQL Class which in this example the ContactDBDataContext is updated with the necessary functionality to deal with the DB.
- Take a look at the code below.
ContactDBDataContext context = new ContactDBDataContext();
var contactData = from Course in context.Courses
select new { Course.Name, Course.Certification };
ContactDBDataContext is the Linq to SQL Class I added. Course is the table I added to that class. It references the course table as Courses. I’ve written a linq expression to get all the course names and certification names from the course table.
When you add your table to that class, it makes that added table identified as a System.Data.Linq.Table<TypeOfTable> and this is accessible from the Linq expression. Ok, now linq is aware of what tables to work with. Now how does it fetch the data? When linq expression is run on a Table<T> it returns an object of type System.Data.Linq.DataQuery, which has all the details to fetch the data you want. Even the SQL statement preview could be viewed by called the ‘ToString()’ on the particular DataQuery object.
This object of type System.Data.Linq.DataQuery implements the IEnumerable interface allowing it to traverse through its items using a ‘for’ loop. This object contains all the data you need to access, in this sample.
- This is just a surface of an ocean to dive in.
LINQ in Brief
What is LINQ?
LINQ stands for Language INtegrated Query. Basically this presents a mechanism for retrieving, sorting, manipulating data which are in various sources. As LINQ is new to .NET so as the syntax.
List<int> Prices = new List<int> {456,789,235,723 };
var myPriceList = from p in Prices orderby p descending select p
In the above line of code, from the Prices List<int>, p is specified as a representation of an item in the collection, saying to get p which are in Prices List<int> based on the ordering clause and finally selecting the item.
Difference between LINQ & SQL, .NET 3.5
Seeing LINQ in .NET 3.5 suddenly made me guess whether it operates like SQL too? Linq uses similar syntax as in SQL but its functionality is totally different to SQL.
SQL basically operates on tables, while LINQ could be operated on collections, and other datasources such as databases or xml. Unlike dealing with rows in a table, these collections could be manipulated using LINQ, whether they have values in them or even objects. Let’s look at some interesting features in LINQ.
Some Interesting Features in LINQ
A collection could be modified retaining the initial values of the collection as it is. For an example, let’s say you retrieve prices and you want to add the currency symbol to the prices. In spite of LINQ, if we were to retrieve the values and add the currency symbol to it, may require traversing through the collection. But LINQ does it simply as this.
private static void GetPriceList()
{
List<int> Prices = new List<int> {456,789,235,723 };
var myPriceList = from p in Prices orderby p descending select p.ToString("C");
foreach (string i in myPriceList)
{
Console.WriteLine(i);
}
}
Not only that, calculations too could be performed on them. Let’s say in the above list you want to get the average price. Here is how to get it.
Console.WriteLine("The average is {0:F2}", Prices.Average());
The important point here is that, method Average() is not a method of the List. It’s available due to LINQ. Such methods are called extension methods. That is basically used to extend the functionality of List in this example. Saving the manipulated collection to an array, dictionary or a list is also possible by the extension methods such as ToArray(), ToDictionary(), ToList().
For a programmer using .net 2.0 for and the previous versions for some time LINQ might seem a bit odd and alien. But when you look deep in to it, it does cuts off lot of code and thinking which you would invest to do some complex work out. For example a simple selection from a list, will require you to traverse through a loop and then check for a condition and extract the data.Linq makes this task easy by giving a whole new look in to the Visual Studio 2008 environment, giving the new key words and a lot of functionality to what you want to achieve.
