Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Introduction to Language Integrated Query (LINQ)


By:   |   Read Comments (4)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Language Integrated Query LINQ

Problem
Language-Integrated Query (LINQ) is a groundbreaking innovation in Visual Studio 2008 and the .NET Framework version 3.5 that bridges the gap between the world of objects and the world of data. As LINQ is part of the development enhancements in SQL Server 2008, how can I have an understanding of how it works and how I can use it in other areas of administration, not just SQL Server?

Solution
Let's start off by explaining LINQ.  LINQ is a codename for a project which is a set of extensions to the .NET Framework that encompasses language-integrated query, set and transform operations. It extends C# and VB with native language syntax for queries and provides class libraries to take advantage of these capabilities, available only in .NET Framework 3.5. For developers who write code that regularly access a recordset, this means a lot. The fact that queries are usually expressed in a specialized query language for different data sources makes it difficult for developers to learn a query language for each data source or data format that they must access. This is what LINQ is all about. It simplifies data access by providing a consistent model for working with data across various kinds of sources and formats. In LINQ, data is translated into objects, something that developers are more comfortable with working . Understanding LINQ will give us an idea of its capabilities and its benefits

Create a simple LINQ project

Let's start by creating a simple console project using the C# language in Visual Studio 2008. You can also download the free Visual C# 2008 Express Edition from the MSDN Download Center. Make sure you select .NET Framework 3.5 from the target framework drop-down menu.

This will open up your Program.cs file. Notice that by simply creating a project that targets the .NET Framework 3.5 automatically adds a using directive for the System.Linq namespace as this is already a part of the System.Core assembly. The System.Linq namespace provides classes and interfaces that support queries that use LINQ. We will start with this to understand the basics of LINQ.

Let's start writing some code inside the static void Main(string[] args):

//Obtaining the data source
string[] carNames = {"Ferrari", "Porsche", "Mercedes" , "McLaren", "Audi", "BMW"};

// Create the query
// query is an IEnumerable
var query = from name in carNames
         
where name.Contains("e")      
            select
name;

// Execute the query
foreach (string name in query)
{
     Console.WriteLine(name);
}

//Pause the application
Console.ReadLine();

We'll examine the basic components of a LINQ query. Any LINQ query consists of three distinct actions.  These are obtaining the data source, creating the query and executing the query. The first thing that we need to do is to have a data source. In this case, it's an array of strings which supports the generic IEnumerable(T) interface. This makes it available for LINQ to query. A queryable type does not require special modification to serve as a LINQ data source as long as it is already loaded in memory.  If not, you would have to load it into memory so LINQ can query the objects. This is applicable to data sources like XML files. Next, is the query. A query specifies information to retrieve from the data source. This is similar to a SQL query which includes syntaxes like SELECT, FROM, WHERE, GROUP BY, etc. Looking at the code above, you'll notice that its not like your typical SQL statement as the FROM clause appeared before the SELECT clause. There are a couple of reasons for this. One, it adheres to the programming concept of declaring the variable before using it. Also, from the point of view of Visual Studio, this makes it easy to provide the IntelliSense feature using the dot (.) notation as the variable has already been declared and that the framework has already inferred the correct type to the object.  This provides the appropriate properties and methods, making it easy for the developers to write their code.

Let's look at how the code was constructed. The from clause specifies the data source, in this case, the carNames collection. The where clause applies the filter, in this case, the list of all elements in the collection containing the letter 'e'. The select clause specifies the type of the returned elements. This means that you can create an instance of the elements in your collection. An example could be creating an instance of an object with fewer attributes. The query variable, query, just stores the information required to produce the results when the query is executed, maybe at a later point. Simply defining the query variable does not return any data nor takes any action. The third component of the code above is query execution. As I mentioned, the query variable does not contain any data, but rather simply contains only the query commands. The actual execution of the query is when we iterate over the query variable. There are a couple of ways to do this. One of which is shown above. The use of a foreach statement iterates thru the query variable and execute it as well. This concept is called deferred query execution. This is very much important when dealing with data sources such as highly-transactional database systems as you minimize connecting to the database unless necessary (database connections are additional resources on the database server as well). You can opt to execute the query immediately by using aggregate functions such as Count, Max, Average and First or calling the ToList() or ToArray() methods. Another way is to bind the collection to a data-bound control in either a web or windows form control similar to how you would do it in previous versions of Visual Studio - specifying the DataSource property of the control to be the query variable and calling the DataBind() method.

Another area to highlight in the code is the use of the keyword var, which is a new keyword introduced in C# 3.0. What this does is it looks at the value assigned to the variable, then determines and sets the appropriate one. This concept is called type inference. From the code above, the query variable, query, appears to be an array of string. So the compiler will automatically assume that it is a variable of type IEnumerable. This is helpful if you do not know the variable type during runtime. But this does not mean that any type can be assigned to the variable after the initial assignment - something like a dynamic type - since .NET is a strongly typed language platform. This simply means that an object can take on a different type and the compiler can simply handle that. Assigning a different type to an already existing one violates the concept of polymorphism in object-oriented programming. Let's say you assign the value 12 to the query variable, query. This will throw a type conversion exception as the original type of the variable is a string collection.

Your output will look like this when you run your project in Visual Studio. You can press F5 or click on Debug - Start Debugging in Visual Studio

Next Steps



Last Update:






About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, December 17, 2008 - 9:41:31 PM - aprato Back To Top

It appears Microsoft will be eventually fixing the proc cache bloat issue in a future SP

 https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=363290&wa=wsignin1.0


Monday, June 09, 2008 - 4:47:15 PM - bass_player Back To Top

LINQ to SQL is designed specifically for SQL Server and as such the queries are optimized for SQL Server. 


Friday, June 06, 2008 - 10:00:34 AM - aprato Back To Top

I'm a little leery of LINQ.... I haven't used it but I fear that it could lead to bloated plan caches and less than optimal queries.

If you decide to use it, keep an eye out for these.


Friday, June 06, 2008 - 12:56:42 AM - underground Back To Top

Hello Admin,

Nice way for dotNet developper


Learn more about SQL Server tools