LINQ Kung Fu - Tips from t'coalface of t'Interwebs

I find myself constantly having to look up how to do things in LINQ that I find straightforward in SQL, so I'm making a note of them here. Hopefully someone else will find these useful.

Great tool for making your Code look pretty in html here by the way http://www.manoli.net/csharpformat/format.aspx

Select Statements

Get List based on Category

// Get Products in a certain category
// same as SQL: select * from Products where CategoryId = CategoryId
    int CategoryId = 1;
    List<Product> Products = db.Products.Where(x => x.CategoryId == CategoryId).ToList();

Select a Single Value with Where Clause

// get the Price of the latest product entered with the Name: "Box of Blue Sparks"
// SQL Equivalent: select TOP 1 Price from Products where Name = 'Box of Blue 
// Sparks' ORDER BY CreatedDate DESC
string pName = "Box of Blue Sparks";
  var available = db.Products.Where(x => x.Name == pName).OrderByDescending(x => x.CreatedDate).Select(x => x.Price).FirstOrDefault();
                              

Select DISTINCT

// Get a list of Distinct Product Colours
// SQL Equivalent: SELECT DISTINCT(Colour) FROM Products WHERE Price is more then £10
List<string> ProductNames = db.Products.Where(x => x.Price > 10).Select(x => x.Colour).Distinct().ToList();

// Note to the Colonials, that is the correct way to spell COLOUR!

Select a Single Record

// Get the first product priced over £100
// SQL Equivalant: SELECT TOP 1 * FROM Products WHERE Price > 100
Product p = db.Products.Where(x => x.Price > 100).FirstOrDefault();

I'll add to this as I get further along the learning curve.

Update 08-01-2015

Get all Model Validation Errors via LINQ

This is very useful when debugging MVC Controller Methods.

var allErrors = ModelState.Values.SelectMany(v => v.Errors);