blog_LINQExercisesUsingQuerySyntax.cs

object q;


// unless otherwise indicated, the resulting type is usually IOrderedQueryable<Product>


// SELECT *
q = from p in Products
	select p;

// SELECT TOP(1)
q = (from p in Products
	select p).First(); // returns Product
q = (from p in Products
	select p).FirstOrDefault(); // returns Product

//q = (from p in Products
//	select p).Single(); // throws an exception if there is more than 1 row
//q = (from p in Products
//	select p).SingleOrDefault(); // throws an exception if there is more than 1 row

q = (from p in Products
	select p).Take(1);

// SELECT column, [column, ]*
q = from p in Products
	select p.Name; // returns IOrderedQueryable<String>
	
q = from p in Products
	select new {p.Name, p.ProductNumber}; // returns IOrderedQueryable<anonymous>

// WHERE
q = from p in Products
	where p.Name.Contains("Sport")
	select p;
	
q = from p in Products
	where p.Name.Contains("Sport") && p.Color == "Black"
	select p;
q = from p in Products
	where p.Name.Contains("Sport")
	where p.Color == "Black"
	select p;
	
q = from p in Products
	where p.ProductNumber == "SH-M897-L" || p.ProductNumber == "SH-M897-S"
	select p;
	
// WHERE column IN [...]
q = from p in Products
	where new[]{"SH-M897-L", "SH-M897-S"}.Contains(p.ProductNumber)
	select p;
	
// INNER JOIN
// without association properties
q = from p in Products
	join c in ProductCategories on p.ProductCategoryID equals c.ProductCategoryID
	select new {p, c}; // returns IOrderedQueryable<anonymous>
// with association properties
q = from p in Products
	select new {p, p.ProductCategory}; // returns IOrderedQueryable<anonymous>

// LEFT JOIN (same logic applies to RIGHT JOIN)
// select all products and any sales order details
// without association properties
q = from p in Products
	join s in SalesOrderDetails on p.ProductID equals s.ProductID into g
	select new {p, SalesOrderDetails = g.DefaultIfEmpty()};
// with association properties
q = from p in Products
	select new {p, p.SalesOrderDetails};

// GROUP BY
q = from p in Products
	group p by p.ProductCategory.Name into g
	select new {g.Key, Count = g.Count()};
	
q = from p in Products
	group p by new {p.ProductCategory.Name, p.Color} into g
	select new {g.Key, Count = g.Count()};

q = from p in Products
	group p by p.ProductCategory.Name into g
	where g.Count() > 10
	select new {g.Key, Count = g.Count()};

// ORDER BY
q = from p in Products
	orderby p.ProductCategory.Name
	select new {Category = p.ProductCategory.Name, p.Name};

q = from p in Products
	orderby p.ProductCategory.Name descending
	select new {Category = p.ProductCategory.Name, p.Name};

q = from p in Products
	orderby p.ProductCategory.Name, p.Color descending
	select new {Category = p.ProductCategory.Name, p.Color, p.Name};


Console.WriteLine(q);