Table of Contents
LINQ
- Snippet from Wikipedia: Language Integrated Query
Language Integrated Query (LINQ, pronounced "link") is a Microsoft .NET Framework component that adds native data querying capabilities to .NET languages, originally released as a major part of .NET Framework 3.5 in 2007.
LINQ extends the language by the addition of query expressions, which are akin to SQL statements, and can be used to conveniently extract and process data from arrays, enumerable classes, XML documents, relational databases, and third-party data sources. Other uses, which utilize query expressions as a general framework for readably composing arbitrary computations, include the construction of event handlers or monadic parsers. It also defines a set of method names (called standard query operators, or standard sequence operators), along with translation rules used by the compiler to translate query syntax expressions into expressions using fluent-style (called method syntax by Microsoft) with these method names, lambda expressions and anonymous types.
Why LINQ beats SQL
“If you're not a LINQ addict, you might wonder what the fuss is about. SQL isn't broken, so why fix it? Why do we need another querying language?
The popular answer is that LINQ is INtegrated with C# (or VB), thereby eliminating the impedance mismatch between programming languages and databases, as well as providing a single querying interface for a multitude of data sources. While that's true, it's only part of the story. More importantly: when it comes to querying databases, LINQ is in most cases a significantly more productive querying language than SQL.
Compared to SQL, LINQ is simpler, tidier, and higher-level. It's rather like comparing C# to C++. Sure, there are times when it's still best to use C++ (as is the case with SQL), but in most situations, working in a modern tidy language and not having to worry about lower-level details is a big win.
SQL is a very old language—invented in 1974. Since then it's been extended endlessly, but never redesigned. This has made the language messy—rather like VB6 or Visual FoxPro. You might have become so accustomed to this that you can't see anything wrong!
Let's take an example. You want to write a simple query that retrieves customers as follows:
SELECT UPPER(Name) FROM Customer WHERE Name LIKE 'A%' ORDER BY Name That doesn't look too bad, right? But now suppose these results are feeding a web page, and we want to retrieve just rows 21-30. Suddenly, you need a subquery:
SELECT UPPER(Name) FROM (
SELECT *, RN = row_number() OVER (ORDER BY Name) FROM Customer WHERE Name LIKE 'A%') A WHERE RN BETWEEN 21 AND 30 ORDER BY Name And if you need to support older databases (prior to SQL Server 2005), it gets worse:
SELECT TOP 10 UPPER (c1.Name) FROM Customer c1 WHERE
c1.Name LIKE 'A%'
AND c1.ID NOT IN
(
SELECT TOP 20 c2.ID
FROM Customer c2
WHERE c2.Name LIKE 'A%'
ORDER BY c2.Name
)
ORDER BY c1.Name
Not only is this complicated and messy, but it violates the DRY principle (Don't Repeat Yourself). Here's same query in LINQ. The gain in simplicity is clear:
var query =
from c in db.Customers
where c.Name.StartsWith ("A")
orderby c.Name
select c.Name.ToUpper();
var thirdPage = query.Skip(20).Take(10); Only when we enumerate thirdPage will the query actually execute. In the case of LINQ to SQL or Entity Framework, the translation engine will convert the query (that we composed in two steps) into a single SQL statement optimized for the database server to which it's connected.
Composability You might have noticed another more subtle (but important) benefit of the LINQ approach. We chose to compose the query in two steps—and this allows us to generalize the second step into a reusable method as follows:
IQueryable<T> Paginate<T> (this IQueryable<T> query, int skip, int take) {
return query.Skip(skip).Take(take);} We can then do this:
var query = … var thirdPage = query.Paginate (20, 10); The important thing, here, is that we can apply our Paginate method to any query. In other words, with LINQ you can break down a query into parts, and then re-use some of those parts across your application.
Associations Another benefit of LINQ is that you can query across relationships without having to join. For instance, suppose we want to list all purchases of $1000 or greater made by customers who live in Washington. To make it interesting, we'll assume purchases are itemized (the classic Purchase / PurchaseItem scenario) and that we also want to include cash sales (with no customer). This requires querying across four tables (Purchase, Customer, Address and PurchaseItem). In LINQ, the query is effortless:
from p in db.Purchases where p.Customer.Address.State == “WA” ]] | linq | SELECT p.* FROM Purchase p LEFT OUTER JOIN Customer c INNER JOIN Address a ON c.AddressID = a.ID ON p.CustomerID = c.ID WHERE (a.State = 'WA' | linq | from p in db.Purchases where p.Customer.Address.State == "WA" | linq | 7, and then complaining about the clumsy syntax for GOTO. Shaping Data Selecting from more than one table in SQL requires joining - the end result being rows of flat tuples. If you've used SQL for many years, you may have become so accepting of this that it may not occur to you that this forced denormalization is often undesirable: it leads to data duplication and makes result sets awkward to work with on the client. In contrast, LINQ lets you retrieve shaped or hierarchical data. This avoids duplication, makes results easier to work with, and in most cases it even obviates the need for joining. For example, suppose we want to retrieve a selection of customers, each with their high-value purchases. In LINQ, you can do this: from c in db.Customers where c.Address.State == "WA" select new { c.Name, c.CustomerNumber, HighValuePurchases = c.Purchases.Where (p => p.Price > 1000) } HighValuePurchases, here, is a collection. And because we were querying an association property, we didn't need to join. Which means the detail of whether this was a inner or outer join is nicely abstracted away. In this case, the query, when translated to SQL, would be an outer join: LINQ doesn't exclude rows just because a subcollection returns zero elements. If we wanted something that translated to an inner join, we could do this: from c in db.Customers where c.Address.State == "WA" let HighValuePurchases = c.Purchases.Where (p => p.Price > 1000) where HighValuePurchases.Any() select new { c.Name, c.CustomerNumber, HighValuePurchases } LINQ also supports flat outer joins, adhoc joins, subqueries, and numerous other kinds of queries through a rich set of operators. Parameterization What if we wanted to parameterize our previous example, so that the state "WA" came from a variable Source: https://www.linqpad.net/WhyLINQBeatsSQL.aspx