The meaning of life is to explore the world

LINQ compared with SQL

Posted on By Jason Liu

LINQ is better than SQL in some ways. https://www.linqpad.net/WhyLINQBeatsSQL.aspx

E.g.
LINQ:

from p in db.Purchases
where p.Customer.Address.State == "WA" || p.Customer == null
let purchaseValue = p.PurchaseItems.Sum (pi => pi.SaleAmount)
where purchaseValue > 1000
orderby purchaseValue descending
select new
{
   p.Description,
   p.Customer.SalesPerson.Name,
   PurchaseItemCount = p.PurchaseItems.Count()
}

SQL:

SELECT 
    p.Description,
    s.Name,
    (SELECT COUNT(*) FROM PurchaseItem pi WHERE p.ID = pi.PurchaseID) PurchaseItemCount	
FROM Purchase p
    LEFT OUTER JOIN 
        Customer c 
            INNER JOIN Address a ON c.AddressID = a.ID
            LEFT OUTER JOIN SalesPerson s ON c.SalesPersonID = s.ID
    ON p.CustomerID = c.ID	
WHERE
    (a.State = 'WA' OR p.CustomerID IS NULL)
    AND p.ID in
    (
        SELECT PurchaseID FROM PurchaseItem
        GROUP BY PurchaseID HAVING SUM (SaleAmount) > 1000
    )
ORDER BY
    (SELECT SUM (SaleAmount) FROM PurchaseItem pi WHERE p.ID = pi.PurchaseID) DESC