LinqPad – Convert SQL to Linq command

There is a tool called Linqer, but be careful: transliterating from SQL to LINQ can give you the worst of both worlds.

For instance, suppose you want all purchases of $1000 or greater paid for in cash or by customers who live in Washington. Here’s the query in SQL:

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" || p.CustomerID IS NULL)
    AND p.ID in
    (
        SELECT PurchaseID FROM PurchaseItem
        GROUP BY PurchaseID HAVING SUM (SaleAmount) > 1000
    )

How would translate this to LINQ? The wrong way is to transliterate the query into LINQ, trying to reproduce the outer and inner joins, subquery and group clause. The right way is to map your original query (in English) directly into LINQ, leveraging LINQ’s linear flow of data and association properties:

I want all purchases…

from p in db.Purchases

…of $1000 or greater…

where p.PurchaseItems.Sum (pi => pi.SaleAmount) > 1000

…paid for in cash…

where p.Customer == null

…or by customers who live in Washington

|| p.Customer.Address.State == "WA"

Here’s the final query:

from p in db.Purchases
where p.PurchaseItems.Sum (pi => pi.SaleAmount) > 1000
where p.Customer == null || p.Customer.Address.State == "WA"
select p

More info here.