LINQ: Language Integrated Query¶
Consider the following classes and lists of such instances.
class Product
{
public int ID;
public string Name;
public int Price;
public int VATID;
}
class VAT
{
public int ID;
public int Percentage;
}
List<Product> products = ...
List<VAT> vat = ...
System.Linq
To access Linq functionality we need the System.Linq namespace:
using System.Linq;
LINQ operations¶
The examples below, when available, show both syntaxes.
Filtering¶
products.Where(p => p.Price < 1000)
from p in products
where p.Price < 1000
Projection¶
products.Select(p => p.Name)
from p in products
select p.Name
Join¶
from p in products
join v in vat on p.VATID equals v.Id
select p.Price * v.Percentage
products.Join(vat, p => p.VATID, v => v.Id, (p, v) => p.Price * v.Percentage)
Sorting¶
products.OrderBy[Descending](p => p.Name)
.ThenBy[Descending](p => p.Price)
from p in products
orderby p.Name, p.Price [descending]
Set operations¶
products.Select(p => p.Name).Distinct()
products.Where(p => p.Price < 1000)
.Union( products.Where(p => p.Price > 100000) )
// similarly Except, Intersect
Aggregation¶
products.Count()
products.Select(p => p.Price).Average()
// similarly Sum, Min, Max
First, last¶
products.First()
products.Last()
products.Where(p => p.Id==12).FirstOrDefault()
products.Where(p => p.Id==12).SingleOrDefault()
Paging¶
products.Take(10)
products.Skip(10).Take(10)
Contains (exists)¶
products.Any(p => p.Price == 1234)
products.Where(p => p.Price == 1234).Any()
Grouping¶
from p in products
group p by p.VATID
products.GroupBy(p => p.VATID)
Advanced projections¶
During projection we can transform the results into various formats.
Whole entity¶
from p in products
...
select p
The result set is of type IQueryable<Product>, so we get Product instances.
Specified field¶
from p in products
...
select p.Name
The result set is of type IQueryable<string>, so we only get the names.
Named types¶
from p in products
...
select new MyType(p.Name, p.Price)
The result set is of type IQueryable<MyType>, when MyType is a class we have to define and has a matching constructor.
Anonym types¶
from p in products
where p.Price > 1000
select new { ID = p.ID, Name = p.Name };
Anonym types can be instantiated using the syntax new { }. The compiler will effectively create a class definition with the properties we specified. This is generally used when we only need two or three properties, and we have no need for the entire entity.
A similar use case for anonym types is when we calculate a property inside the query, such as the name of the product and the full price:
from p in products
join v in vat on p.VATID equals v.Id
select new { Name = p.Name, FullPrice = p.Price * v.Percentage }
LINQ expressions and IEnumerable/IQueryable¶
Depending on the data source we are using Linq on the result of a query, such as products.Where(p => p.Price < 1000) yields a variable of type IEnumerable<T> or IQueryable<T>. Neither of these contain the result sets; they are only descriptors, that is, the operation has not yet been evaluated yet. This is called deferred execution, as the execution will only happen when the result is effectively used:
- when the result set is iterated (e.g.
foreach), - when a specific item is accessed (see later, e.g.
.First()), - when we as for a list instead (
.ToList()).
This operation is useful, because this allows us to chain LINQ operations after each other, such as:
var l = products.Where(p => p.Price < 1000)
.Where(p => p.Name.Contains('s'))
.OrderBy(p => p.Name)
.Select(p => p.Name)
...
// variable l does not contain the result
foreach(var x in l) // this is when the execution will happen
{ ... }
Force evaluation
If we want to force the execution at any given moment, we usually use .ToList(). But this has to be considered first and only used when necessary.
More information and further examples¶
Lambda expressions: https://www.tutorialsteacher.com/linq/linq-lambda-expression
Linq: https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/