LINQ : Some sample queries

Been paying around with LINQ to SQL using the excellent LINQPad and the Northwind DB.

Some samples I came up with:

(The "C# Expression" refers to the Type in LINQPad. Ensure the DB drop-down points to the Northwind DB. The lambda is generated during run time and can be viewed by clicking on the lambda symbol next to the Results tab. You can think of lambda as a function e.g. "a => a * 2" is a function that takes "a" as input and returns "a times 2".

The Dump statement just outputs the current object.)

// This works - C# Expression
// Get all rows in Products - sort by ProductName

from p in Products
orderby p.ProductName
select p

// This is the corresponding lambda - C# Expression

Products
.OrderBy (p => p.ProductName)

// C# Expression
// Only show rows that have an "A" in the ProductName

from p in Products
where p.ProductName.Contains("A")
select p

These are all C# Statement(s) - ";" are now required at the end of declarations.

// Return all rows of employees who live in the UK sorted by LastName

var employees =
from emp in Employees
where emp.Country == "UK"
orderby emp.LastName
select emp;

employees.Dump();

Some more complicated ones.

// Find all products that are in stock

var products =
from prod in Products
where prod.UnitsInStock > 0
orderby prod.UnitsInStock
select prod;

products.Dump();

// And the lambda

Products
.Where (prod => ((prod.UnitsInStock) > 0))
.OrderBy (prod => prod.UnitsInStock);

Products.Dump();

// Putting the lambda on one line

Products.Where (prod => ((prod.UnitsInStock) > 0)).OrderBy (prod => prod.UnitsInStock);
Products.Dump();

What about two tables with a foreign key relationship? Let's throw in two "where" statements for fun!

// Find all products that are in stock and that whose category is Beverages

var products =
from prod in Products
from cat in Categories
where prod.UnitsInStock > 0 && prod.CategoryID == cat.CategoryID && cat.CategoryName == "Beverages"
orderby prod.UnitsInStock
select prod;

products.Dump();

// Write the rows out individually line-by-line

foreach(var prod in products)
Console.WriteLine(prod.ProductID + " " + prod.ProductName);

// "products" is an object which we can simply re-sort without another DB look-up

var products2 =
from prod in products
orderby prod.ProductID
select prod;

Console.WriteLine();

foreach(var prod in products2)
Console.WriteLine(prod.ProductID + " " + prod.ProductName);

Enjoy!