Language-Integrated Query

Article Info

Contributed by
1 author

Last updated on
2024-01-25 05:12:53

Object-Relational Mapping
Data Mapping
Data Engineering
Database Management System
.NET
Entity Framework

Article Versions

6 2024-01-25 05:12:53
4047,4046 6,4047

By arvindpdmn

Correcting citation.
5 2024-01-25 05:07:37
4046,3856 5,4046

By arvindpdmn

Adding milestone with mind map
4 2022-11-20 09:36:24
3856,3855 4,3856

By arvindpdmn

More Sample Code examples. Added theoretical milestones. Added two questions: data types and tips.
3 2022-11-20 06:07:30
3855,3854 3,3855

By arvindpdmn

Completing all content and publishing.
2 2022-11-19 12:29:41
3854,3846 2,3854

By arvindpdmn

Milestones and See Also added.

Chat Room

Submitting ...

You are editing an existing chat message.
2024-01-25 05:08:55
-

By devbot5S

[URL Check] The following URLs in this article are outdated. Please update.

Redirected URLs:
Discussion: https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/classification-of-standard-query-operators-by-manner-of-execution#classification-table → https://learn.microsoft.com/en-us/dotnet/csharp/linq/query-a-collection-of-objects
References: https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/visual-studio-ide-and-tools-support-for-linq → https://learn.microsoft.com/en-us/dotnet/csharp/linq/

LINQ in C# (left) and Visual Basic (right). Source: Microsoft 2022a.

SQL databases, XML documents, web services and data objects are examples of different types of data sources. Each of these is typically interfaced differently from application code. LINQ simplifies this by offering a single unified querying capability directly within the C# language. In other words, querying is a first-class language construct.

Code using LINQ is simpler and concise. Syntax and type checking are possible at compile time along with IntelliSense support. This is unlike database interfacing in which queries are treated as strings. Debugging LINQ queries is easier.

.NET's Entity Framework Core is the modern way of using LINQ. LINQ is available in C#, F# and Visual Basic. In C#, SQL Server databases, XML documents, ADO.NET Datasets and collections with IEnumerable or IEnumerable<T> interfaces are supported. Third-party tools are available for interfacing LINQ with web services and other databases.

Discussion

Could you explain LINQ with a simple example?
Iteration compared against a LINQ query. Source: Adapted from Hummel 2006, p. 3.
Doctors collection inherits from List<Doctor>. Suppose we wish to know all doctors in Chicago. A common programming approach is to loop through the collection, check for a condition (Chicago, in our example) and store the matches into another collection.
LINQ provides a declarative syntax that's more like querying an SQL database. With LINQ and other .NET features, we can do many things with ease: find doctors in Chicago (filtering), sort doctors by their pager number (sorting), count the number of doctors in each city (grouping and counting), change specific attributes (updating), add a new doctor (inserting), etc.
Though LINQ doesn't directly support CRUD (Create, Read, Update, Delete), it supports CRUD-like operations and does so in a more intuitive manner. Such queries and transforms are first-class concepts of C#. Here are some examples (note that Add() and RemoveAll() are methods of List<T>):
- Create: doctors.Add( new Doctor("John", "Smith", ... ) );
- Read: var larsens = from d in doctors where d.FamilyLastName == "Larsen" select d;
- Update: foreach (var d in larsens) d.City = "Suburb";
- Delete: doctors.RemoveAll(x => x.FamilyLastName == "Larsen");
What's the architecture of LINQ in .NET?
Architecture of LINQ. Source: Microsoft 2021b.
.NET LINQ provides programmers a single unified API to interact with various types of data sources. These can be in-memory data structures/objects, XML documents, databases, and more. LINQ queries are translated into data source operations by what's called a LINQ provider. Developers can write their own LINQ providers to interface to data sources not supported by default in .NET.
Among the built-in providers are LINQ to Objects, LINQ to XML, LINQ to DataSet, LINQ to SQL, and LINQ to Entities. LINQ to SQL enables developers to generate .NET classes that interface to relational databases directly. LINQ to Entities provides an alternative so that applications interact with data as objects. LINQ support is at this object layer. The Entity Data Model presents a conceptual model that represents the data. It's been said that LINQ to SQL was .NET's first attempt at an ORM with support for only SQL Server. LINQ to Entities is the more modern approach.
There are many more third-party LINQ providers to query various data sources such as search engines (Google, Bing), spreadsheets (CSV, Excel), APIs (Twitter, MediaWiki), etc.
What programming languages support LINQ?
LINQ is supported in C#, F# and Visual Basic. There's little difference between using LINQ in C# and F#. Moreover, LINQ queries can be expressed differently using F# built-in functions. Between C# and Visual Basic, there are some syntax differences.
Other languages have tried to adopt LINQ-like capabilities. PHPLinq is an example with a syntax such as from('$name')->in($names)->where('$name => strlen($name) < 5')->select('$name'). This relies on lambda expressions. However, query expressions are quoted and therefore just strings. They don't benefit from the real power of LINQ.
Elcy is an ORM for TypeScript/JavaScript with LINQ-like query syntax. Another JavaScript library is Underscore.js, which adopts the functional programming paradigm.
Indeed, while LINQ is declarative it has similarities with functional programming. Mukherjee's book Thinking in LINQ teaches how LINQ can enable functional programming. Python's toolz and Ruby's Enumerable are packages that come to close to LINQ.
How does LINQ's query syntax differ from it's method syntax?
LINQ query syntax versus method syntax. Source: Adapted from Kyriakidis 2022.
LINQ's query syntax is similar to that in SQL. The simple example in the figure shows the from-in construct, a conditional expression and query operators. These together make a query expression. In LINQ's method syntax, operations appear as methods that are called on the data source. Method calls are chained. Both are equivalent and have similar performance. In fact, queries are transformed into methods before being compiled. Lambda expressions are typical of the method syntax.
Query syntax is more readable. Intermediate variables using the let keyword can be created with query syntax. However, operations that return singleton numeric values (such as Min, Sum, etc.) must use the method syntax. It's also permitted to mix the two syntaxes, that is, a query expression enclosed in parentheses and followed by a method call. An example is (from num in numbers where num < 3 select num).Count().
Both syntaxes result in a query object that's typically a collection of type IEnumerable<T>. The query object doesn't contain the results. Results are produced only when the query is executed.
What is deferred execution in LINQ?
Deferred versus lazy execution. Source: Adapted from .NET 2020.
When a query object is returned, it's not immediately executed. If it interfaces to an external data source, the data source is not immediately contacted. This happens only when the query is executed and results are needed. This is what we mean by deferred execution in LINQ (aka lazy evaluation). When a query object is used within a foreach loop, then the query is executed. Another way to trigger deferred execution is to call the GetEnumerator() method.
Within deferred execution, some LINQ operators support streaming. With streaming, the entire source data is not read at once. Results are returned when needed. With non-streaming, all source data is read before the results are returned. Sorting and grouping are examples of non-streaming operations. Some streaming operators have two input sequences where the first sequence is evaluated in a deferred streaming manner and the second in a deferred non-streaming manner. Examples include Except, GroupJoin, Intersect and Join.
For immediate execution we call Enumerable.ToList() or Enumerable.ToArray() on the results. Another way to trigger immediate execution is to call some functions, such as Count(), Max() and Average().
What are some operations that LINQ can perform?
LINQ query comprehension syntax. Source: Albahari 2022a.
.NET documentation gives a complete table of LINQ operators and their support for deferred/immediate execution.
LINQ query operators can be classified into aggregate (Average, Count, Sum), set (Distinct, Except, Intersect, Union), concatenation (Concat), join (Join), conversion (AsEnumerable, Cast, OfType, ToArray, ToList), element (ElementAt, First, FirstOrDefault, Last, Single), equality (SequenceEqual), generation (Empty, Range, Repeat), ordering (OrderBy, ThenByDescending), partitioning (Skip, TakeWhile), projection (Select, SelectMany, Zip), quantifier (All, Any), and restriction/filtering (Where).
Third-party packages are available that extend the above built-in LINQ operations. One such example is MoreLINQ.
How does LINQ handle data types?
All variables in a query expression are strongly typed. Type can be declared explicitly but the compiler is smart enough to infer the type in many cases.
Consider var custQuery = from cust in Customers where cust.City == "London" select cust. The use of var implies type inference. There's no need to explicitly declare it with IEnumerable<Customer>. Another example where var makes sense is when an anonymous type is returned. Example, var query = from cust in Customers where cust.City == "London" select new { name = cust.Name, phone = cust.Phone }.
When non-generic IEnumerable collections such as ArrayList are queried, type must be explicit, at least in the from expression. Example, var query = from Student s in students.
IEnumerable<T> queries are compiled to delegates. IQueryable and IQueryable<T> queries are compiled to expression trees. For example, LINQ to Objects works with delegates. LINQ to SQL works with expression trees. An expression tree is a data structure that defines code.
What are some tips when using LINQ?
Anatomy of a LINQ query. Source: Khasawneh 2007.
To check if a collection has items (that optionally match a specific condition), prefer to use Any() rather than Count(). Prefer to use Any(condition) instead of Where(condition).Any(). When calling FirstOrDefault(), check for null object reference before using the returned result. Alternatively, include the method DefaultIfEmpty() in the query to specify a default value.
A query such as from c in db.Customers select c does nothing. Instead simply say db.Customers. Likewise, (from c in db.Customers where c.ID == 123 select c).Single() can be simplified to db.Customers.Single(c => c.ID == 123).
Unlike SQL, joins can be accomplished without using the join keyword. This is due to LINQ's association properties. Thus, to obtain customers who haven't purchased anything, we can simply write from c in db.Customers where !c.Purchases.Any() select new { c.ID, c.Name }.
Although SQL emits flat result sets, LINQ can return complex objects. Such objects are easily initialized within either anonymous types or named types. With the latter, such types can be returned from methods.
Since query execution is deferred, queries can be built in steps. This makes the code more readable and maintainable.
What are some limitations of LINQ?
There are two sides to LINQ: consumers and implementers. For consumers, LINQ brings many benefits. For implementers, writing a LINQ provider is not easy. Essentially, this involves implementing IQueryable<T> that reads an expression tree and figure out how to translate that to query the underlying data store.
LINQ allows consumers to write queries, some of which may not execute efficiently at the database layer. The semantics of LINQ that are well-suited for memory collections may not map very well to relational and non-relational databases. Thus, it's possible to have queries that a LINQ provider may fail to handle properly.
It's been said that LINQ is not suited for complex SQL queries. SQL store procedures have execution caching that the LINQ approach can't utilize.
Where can I learn more about LINQ?
Screenshot of LINQPad.QueryPlanVisualizer. Source: Dalakishvili 2022.
Official .NET documentation describes how to use LINQ in C# and LINQ in Visual Basic. An important reference is the System.Linq namespace documentation.
Microsoft has published some videos that explain LINQ. A repository called try-samples has some LINQ examples.
Among the books to read are C# 10 Pocket Reference and LINQ Pocket Reference by Albahari and Albahari.
There are many useful tools that improve the developer experience when working with LINQ:
- Visual Studio IDE: This supports IntelliSense and syntax highlighting of LINQ queries. The debugger can debug query expressions. As a visual tool, there's Object Relational Designer for LINQ to SQL applications. CLI tool SQLMetal can generate classes from databases.
- LINQ Insight: Allows developers to execute, debug and analyze queries during the design phase even without starting a debug session.
- LINQPad: Similar to LINQ Insight. Can also execute C#/F#/VB code snippets. A related tool is LINQPad.QueryPlanVisualizer that supports SQL Server and PostgreSQL for databases, and Entity Framework Core 5 and LINQ to SQL for ORMs.

Milestones

1995

Buneman et al. publish a paper titled Principles of programming with complex objects and collection types. While not directly related to LINQ, this paper shares the theoretical foundations. In particular, relational algebra and category theory (monads) are used.

2004

The idea of using "sequence operators" that can be used on any IEnumerable collection is proposed by Anders Hejlsberg, the chief designer of C#. Such operators can also be used for remote queries on IQueryable types. The compiler needs to know about such operators so that correct static methods and delegates can be called.

2006

Meijer et al. note the problem of ROX impedance mismatch, where ROX (Relational, Object, XML) relates to data access technologies. .NET Framework's upcoming release is meant to solve this problem via LINQ. The LINQ framework would define two domain-specific APIs: XLinq (for XML documents) and DLinq (for relational data).

Nov
2007

Microsoft releases .NET Framework 3.5. LINQ is added as part of this release.

Aug
2008

Microsoft releases .NET Framework 3.5 Service Pack 1. This includes ADO.NET Entity Framework and ADO.NET Data Services. This enables LINQ to query databases with strong typing. In later years, it's claimed that LINQ changed how programmers write C# code.

Apr
2010

Microsoft releases .NET Framework 4.0. This includes support for parallel computing (multi-core or distributed systems). To this end, Parallel LINQ (PLINQ) is added. PLINQ bring parallelism to LINQ.

2013

Cheney et al. publish a paper titled A Practical Theory of Language-Integrated Query. Their approach uses quotation and normalization. Their theory is called T-LINQ. They provide an implementation in F# called P-LINQ. They note that Microsoft's LINQ fails for some of their example queries whereas P-LINQ works correctly.

Nov
2021

Microsoft releases .NET 6. This release expands the LINQ API in many ways. The Enumerable class gets many new methods: TryGetNonEnumeratedCount for counting without enumerating, DistinctBy/UnionBy/IntersectBy/ExceptBy for set operations with specific selector functions, MaxBy/MinBy operations with selector functions, Chunk for chunking, FirstOrDefault/LastOrDefault/SingleOrDefault to return defaults on empty enumerables, and more. Zip can now do three-way combines. ElementAt can take indices from the end.

Nov
2022

Microsoft releases .NET 7. With SQL Server's JSON support, LINQ can now query JSON documents stored in a relational database. There's now a simpler way to order items, using Order rather than OrderBy, The latter method is still useful for ordering non-trivial objects that don't implement IComparable<T>.

2023

Giesel publishes a C# LINQ mind map. This visualizes LINQ operations grouped by categories. It can be a useful learning guide for beginners.

Sample Code

// Source: https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/
// Accessed: 2022-11-03
 
// Specify the data source.
int[] scores = { 97, 92, 81, 60 };
 
// Define the query expression.
IEnumerable<int> scoreQuery =
    from score in scores
    where score > 80
    select score;
 
// Execute the query.
foreach (int i in scoreQuery)
{
    Console.Write(i + " ");
}
 
// Output: 97 92 81

// Source: https://www.codeproject.com/Articles/19154/Understanding-LINQ-C
// Accessed: 2022-11-20
 
// Query syntax
var result = from c in Customers
             where c.City.StartsWith("B")
             orderby c.LastName
             select new { c.FirstName, c.LastName, c.Address };
 
// Method syntax
var result = Customers.Where( c => c.City.StartsWith("B") )
                      .OrderBy( c => c.LastName  )
                      .Select( c => new { c.FirstName, c.LastName, c.Address } );

// Source: https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/introduction-to-linq-queries
// Accessed: 2022-11-20
 
// Create a data source from an XML document.
using System.Xml.Linq;
XElement contacts = XElement.Load(@"c:\myContactList.xml");

// Source: https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/introduction-to-linq-queries
// Accessed: 2022-11-20
 
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
 
// Query for customers in London.
IQueryable<Customer> custQuery =
    from cust in db.Customers
    where cust.City == "London"
    select cust;

References

Article Stats

2276

Words

Authors

Edits

Chats

Likes

2880

Hits

Cite As

Devopedia. 2024. "Language-Integrated Query." Version 6, January 25. Accessed 2024-06-25. https://devopedia.org/language-integrated-query

Contributed by
1 author

Last updated on
2024-01-25 05:12:53

data database query language data processing

Object-Relational Mapping
Data Mapping
Data Engineering
Database Management System
.NET
Entity Framework

Language-Integrated Query

Discussion

Milestones

Sample Code

References

Further Reading

Article Stats

Author-wise Stats for Article Edits

Cite As

See Also

Login