Microsoft’s Language-integrated Query (LINQ) is an innovation introduced in Visual Studio 2008 and .NET Framework version 3.5 that bridges the gap between the world of objects and the world of data.
Traditionally, queries against data are expressed as simple strings without type checking at compile time or IntelliSense support. Users have to learn a different query language for each type of data source: SQL databases, XML documents, various Web services, etc. LINQ makes a query a first-class language construct in C# and Visual Basic. Users write queries against strongly typed collections of objects by using language keywords and familiar operators.
In my development of sophisticated applications, especially applications that need to perform some fairly intense data grouping and filtering processes, LINQ has become a very important tool upon which I rely. The following examples will demonstrate some of the more common ways one can apply the concepts of Microsoft LINQ in software development.
Grouping and selecting distinct values from a dataset
In many applications, data needs to be grouped by a specific value with totals summed to represent the total of each groups data items. The easiest way to accomplish this in LINQ is represented below:
First, let’s declare a class that represents the data items that will be used in the examples of this blog. For the purposes of this blog, we’re going to pretend that each item in our data list contains a unique name with each item having an amount. Each item will also belong to a specific Geography and there may be one or more items that belong to the same geographies.
Creating and populating a list of items:
Creating a class that will be used to create our output dataset with the grouping data:
Now for the LINQ needed to get a unique (distinct) list of all data items by the field we called ‘DataItemName’ and a total amount for each distinct ‘DataItemName’
The results will look like the following:
The list shown is sorted in Unique Name order in the above example.
Grouping and ordering using LINQ
If we need to sort by the amount and order the largest amount by name at the top, we could simply change the ‘Order By’ clause in our statement as shown below:
The results of this grouping would look like the following:
Notice that ‘Name 2’ is now at the top of the list with $350.00 and the list is now sorted in descending order by dollar amount.
Grouping, ordering and tie-breaking in LINQ.
How do you produce a list of names and the list needs to have the top, highest amount by geography, where any name could belong to more than one geography? Normally, I would have preferred to do this using a ‘Row over Partition’ method in SQL using a stored procedure. In this case, I did not have the luxury of performing this using SQL, so I came up with the following LINQ statement:
In the above LINQ procedure, the items are grouped by the Item Name and the Geography. They are then ordered by the sum of the dollar amount of each grouping. Finally, these groups are used to create a new object which is then sorted into dollar amount in descending order and the top item is picked from this newly sorted list. Also note that when the new objects are created in dollar amount order, the first Geography belonging to this group is selected since this is the Geography with the highest amount within that data item name. This then produces an output like the following:
Since we only selected the group with the highest amount by Geography and Canada had two items for a total of 295.00 for the name ‘Name 2’, this is the top item by Geography in our list.
As you can see, LINQ can be extremely useful for sorting, listing and filtering data within a C# application. It is a powerful tool that can be used in a multitude of ways to solve many of the complex data gathering tasks that software developers regularly face. Because of the similarities in the syntax of LINQ and SQL, software developers familiar with the SQL database scripting language will comfortably be able to use LINQ in a C# application.
In Visual Studio you can write LINQ queries in Visual Basic or C# with SQL Server databases, XML documents, ADO.NET Datasets, and any collection of objects that supports IEnumerable or the generic IEnumerable<T> interface. LINQ also contains support for ADO Entity Framework in C# applications.