Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
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?
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
//Pause the application
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
- You have seen how powerful LINQ queries are and how similar they are to SQL queries. There are other data access methods that LINQ implements including
- Give this example a try and change the query parameters so you can have a feel of how LINQ works.
- Have a look at the 101 LINQ Samples at the MSDN Visual C# Developer Center.
- For samples in Visual Basic, you can check the Getting Started with LINQ in Visual Basic site from MSDN
Last Update: 2008-05-20
About the author
View all my tips