Language-Integrated Query

LINQ in C# (left) and Visual Basic (right). Source: Microsoft 2022a.
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.
    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.
    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 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.
    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.
    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.
    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.
    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>.

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
     

References

  1. .NET. 2020. "LINQ Query Composition and Execution." .NET, on YouTube, January 6. Accessed 2022-11-09.
  2. Abuhakmeh, K. 2022. "Add MS SQL Server JSON Support To Entity Framework Core." October 11. Accessed 2022-11-19.
  3. Aguirre, C. 2021. "A quick guide to LINQ with examples." Blog, Some Code, January 18. Accessed 2022-11-03.
  4. Albahari, J. 2022a. "LINQ: Query Comprehension Syntax." In: C# 10 in a Nutshell, O'Reilly Media, Inc. Accessed 2022-11-03.
  5. Albahari, J. 2022b. "Ten LINQ Myths." In: C# 10 in a Nutshell, O'Reilly Media, Inc. Accessed 2022-11-05.
  6. Albahari, J. and B. Albahari. 2022. "C# 10.0 Pocket Reference & LINQ Pocket Reference." O'Reilly Media Inc. Accessed 2022-11-03.
  7. Allen, J. 2021. ".NET 6 LINQ Improvements." InfoQ, April 29. Accessed 2022-11-03.
  8. Bai, Y. 2020. "Introduction to Language Integrated Query (LINQ)." Chapter 4 in: SQL Server Database Programming with Visual Basic.NET: Concepts, Designs and Implementations, IEEE, pp.123-213. doi: 10.1002/9781119608493.ch4. Accessed 2022-11-03.
  9. Balliauw, M. 2008. "LINQ for PHP (Language Integrated Query for PHP)." Blog, January 22. Accessed 2022-11-03.
  10. Borozdin, M. 2008. "Is PHPLinq As Cool As Real LINQ?" Blog, July 4. Accessed 2022-11-03.
  11. Box, D., and A. Hejlsberg. 2007. "LINQ: .NET Language-Integrated Query." MSDN, February. Accessed 2022-11-03.
  12. Buneman, P., S. Naqvi, V. Tannen, and L. Wong. 1995. "Principles of programming with complex objects and collection types." Theoretical Computer Science, 149(1):3-48. Accessed 2022-11-05.
  13. Chandra, Leo. 2020. "elcy." On GitHub, July 29. Accessed 2022-11-19.
  14. Cheney, J., S. Lindley, and P. Wadler. 2013. "A practical theory of language-integrated query." ICFP '13: Proceedings of the 18th ACM SIGPLAN international conference on Functional programming, pp. 403-416, September. doi: 10.1145/2500365.2500586. Accessed 2022-11-03.
  15. CodeMaze. 2022. "LINQ Improvements in .NET." CodeMaze, August 2. Accessed 2022-11-03.
  16. Corey, Tim. 2022. ".NET 7 Update: Simplified LINQ Ordering In 10 Minutes or Less." IAmTimCorey, on YouTube, November 9. Accessed 2022-11-19.
  17. Dalakishvili, G. 2022. "LINQPad.QueryPlanVisualizer." On GitHub, August 9. Accessed 2022-11-05.
  18. Devart. 2022. "LINQ Insight." Devart. Accessed 2022-11-05.
  19. Douglas, J., J. Likness, and A. Petropoulos. 2022. ".NET 7 is Available Today." .NET Blog, Microsoft, November 8. Accessed 2022-11-19.
  20. Eini, O. 2011. "The pain of implementing LINQ providers." Comms. of the ACM, vol. 54, no. 8, pp. 55-61, August. Accessed 2022-11-20.
  21. Horton, A. 2007. "C# 3.0: The Evolution Of LINQ And Its Impact On The Design Of C#." MSDN Magazine, June. Accessed 2022-11-03.
  22. Hummel, Joe. 2006. "LINQ: The Future of Data Access in C# 3.0." O'Reilly Media, Inc. Accessed 2022-11-05.
  23. Khasawneh, A. 2007. "Understanding LINQ (C#)." Code Project, June 12. Accessed 2022-11-03.
  24. Kyriakidis, I. 2022. "Understanding the .NET Language Integrated Query (LINQ)." Blog, .NET Nakama, March 4. Accessed 2022-11-03.
  25. LINQPad. 2022. "LINQPad: The .NET Programmer's Playground." Accessed 2022-11-03.
  26. Lander, R. 2021. "Announcing .NET 6 — The Fastest .NET Yet." .NET Blog, Microsoft, November 8. Accessed 2022-11-19.
  27. Meijer, E., B. Beckman, and G. Bierman. 2006. "LINQ: reconciling object, relations and XML in the .NET framework." Proceedings of the 2006 ACM SIGMOD International Conference on Management of Data (SIGMOD '06), Association for Computing Machinery, New York, NY, USA, pp. 706. doi: 10.1145/1142473.1142552. Accessed 2022-11-03.
  28. Michel, C. 2021. "A History of Microsoft .NET, Part 4: .NET Framework 3.0 (LINQ)." Blog, Don't Panic Labs, August 10. Accessed 2022-11-03.
  29. Microsoft. 2021a. "Introduction to LINQ Queries (C#)." Documentation, .NET, Microsoft, September 15. Accessed 2022-11-03.
  30. Microsoft. 2021b. "LINQ and ADO.NET." Documentation, .NET, Microsoft, September 15. Accessed 2022-11-03.
  31. Microsoft. 2021d. "Visual Studio IDE and Tools Support for LINQ (C#)." Documentation, .NET, Microsoft, September 15. Accessed 2022-11-09.
  32. Microsoft. 2021e. "Expression Trees Explained." Documentation, .NET, Microsoft, September 15. Accessed 2022-11-20.
  33. Microsoft. 2021f. "How to query an ArrayList with LINQ (C#)." Documentation, .NET, Microsoft, September 15. Accessed 2022-11-20.
  34. Microsoft. 2022a. "Language Integrated Query (LINQ) (C#)." Documentation, .NET, Microsoft, October 13. Accessed 2022-11-03.
  35. Microsoft. 2022b. "Language-Integrated Query (LINQ) (Visual Basic)." Documentation, .NET, Microsoft, September 10. Accessed 2022-11-03.
  36. Microsoft. 2022c. "Type Relationships in LINQ Query Operations (C#)." Documentation, .NET, Microsoft, September 21. Accessed 2022-11-03.
  37. Microsoft. 2022d. "LINQ to SQL tools in Visual Studio." Documentation, Visual Studio 2022, Microsoft, April 30. Accessed 2022-11-05.
  38. Microsoft. 2022e. "List<T>.RemoveAll(Predicate<T>) Method." Documentation, .NET 6.0, Microsoft, June 1. Accessed 2022-11-08.
  39. Microsoft. 2022f. "Entity Framework documentation." .NET, Microsoft. Accessed 2022-11-08.
  40. Microsoft. 2022g. "Classification of Standard Query Operators by Manner of Execution (C#)." Documentation, .NET, Microsoft, June 28. Accessed 2022-11-08.
  41. Microsoft. 2022h. "Write LINQ queries in C#." Documentation, .NET, Microsoft, September 21. Accessed 2022-11-19.
  42. Microsoft. 2022i. "Language Integrated Query (LINQ) provider for C#." Documentation, Dynamics 365, Microsoft, August 13. Accessed 2022-11-20.
  43. Mukherjee, S. 2014. "Thinking in LINQ: Harnessing the Power of Functional Programming in .NET Applications." Apress. Accessed 2022-11-03.
  44. Nemes, A. 2017. "LINQ statements in F#." Blog, Exercises in .NET, June 10. Accessed 2022-11-03.
  45. Obasanjo, D. 2004. "The Unified Theory of Everything." Blog, January 1. Accessed 2022-11-20.
  46. Rocklin, M. and J. Jacobsen. 2018. "Heritage." Docs, Toolz, July 10. Accessed 2022-11-05.
  47. Wikipedia. 2021. "Language Integrated Query." Wikipedia, December 17. Accessed 2022-11-03.
  48. Wikipedia. 2022. ".NET Framework version history." Wikipedia, November 2. Accessed 2022-11-09.
  49. Wikipedia. 2022b. ".NET." Wikipedia, November 8. Accessed 2022-11-09.
  50. de Gouvello, A. 2018. "Awesome LINQ." On GitHub, October 26. Accessed 2022-11-20.
  51. marc_s. 2010. "What is the difference between LINQ to Entities, LINQ to SQL and LINQ to Dataset." StackOverflow, March 14. Accessed 2022-11-20.
  52. vcsjones. 2011. "Differences in LINQ syntax between VB.Net and C#." StackOverflow, June 29. Accessed 2022-11-03.

Further Reading

  1. Microsoft. 2021a. "Introduction to LINQ Queries (C#)." Documentation, .NET, Microsoft, September 15. Accessed 2022-11-03.
  2. Microsoft. 2021c. "Data Transformations with LINQ (C#)." Documentation, .NET, Microsoft, September 15. Accessed 2022-11-03.
  3. Aguirre, C. 2021. "A quick guide to LINQ with examples." Blog, Some Code, January 18. Accessed 2022-11-03.
  4. Meijer, E. 2011. "The world according to LINQ." Communications of the ACM, vol. 54, no. 10, pp. 45-51. Accessed 2022-11-03.
  5. Horton, A. 2007. "C# 3.0: The Evolution Of LINQ And Its Impact On The Design Of C#." MSDN Magazine, June. Accessed 2022-11-03.
  6. Albahari, J. 2022. "Ten LINQ Myths." In: C# 10 in a Nutshell, O'Reilly Media, Inc. Accessed 2022-11-05.

Article Stats

Author-wise Stats for Article Edits

Author
No. of Edits
No. of Chats
DevCoins
4
0
1550
2245
Words
0
Likes
24
Hits

Cite As

Devopedia. 2022. "Language-Integrated Query." Version 4, November 20. Accessed 2022-11-20. https://devopedia.org/language-integrated-query
Contributed by
1 author


Last updated on
2022-11-20 09:36:24

Improve this article
  • Object-Relational Mapping
  • Data Mapping
  • Data Engineering
  • Database Management System
  • .NET
  • Entity Framework

Article Warnings

  • Readability score of this article is below 60 (59.4). Use shorter sentences. Use simpler words.