LINQ_Syntax_1

//Orderby, Split
string[] musos = { "Roger Waters", "David Gilmour", "Rick Wright", "Nick Mason"  };

musos.OrderBy (m => m.Split().Last())   .Dump ("Sorted by last name");

//6.5: Fetch books using LINQ to SQL
//LINQPad abstracts the context. We'll set it to this.
DataContext dataContext = this;

IQueryable<Book> query = from book in dataContext.GetTable<Book>()
						select book;
						
query.Dump();

//
DataContext dataContext = this;

var query =
  	from book in dataContext.GetTable<Book>()
	group book by book.Subject into groupedBooks
	orderby groupedBooks.Key
  	select new
	{
		SubjectId = groupedBooks.Key,
		Books = groupedBooks
	};
  
query.Dump();

//
Books
   .GroupBy (book => book.Subject)
   .OrderBy (groupedBooks => groupedBooks.Key)
   .Select (
      groupedBooks => 
         new  
         {
            SubjectId = groupedBooks.Key, 
            Books = groupedBooks
         }
   )

//
SELECT [t1].[Subject] AS [SubjectId]
FROM (
    SELECT [t0].[Subject]
    FROM [Book] AS [t0]
    GROUP BY [t0].[Subject]
    ) AS [t1]
ORDER BY [t1].[Subject]
GO

//6.18: Approximating an Outer Join
//LINQPad abstracts the context. We'll set it to this.
DataContext dataContext = this;

var books = dataContext.GetTable<Book>();
var Subjects = dataContext.GetTable<Subject>();

var query =
	from subject in Subjects
  	join book in books
		on subject.ID equals book.Subject into joinedBooks
	from joinedBook in joinedBooks.DefaultIfEmpty()
  	select new
	{
		subject.Name,
		joinedBook.Title,
		joinedBook.Price
	};
  
query.Dump();

//inner join with IOrderQueryable as return
DataContext dataContext = this;

var Subjects = dataContext.GetTable<Subject>();

var query =
  	from subject in Subjects
	where subject.Books.Any()
  	select subject;
  
query.Dump();

//subject without book
DataContext dataContext = this;

var Subjects = dataContext.GetTable<Subject>();

var query =
  	from subject in Subjects
	where !subject.Books.Any()
  	select subject;
  
query.Dump();

//
//6.24: Running query using object heirarchies
//LINQPad abstracts the context. We'll set it to this.
DataContext dataContext = this;

var Subjects = dataContext.GetTable<Subject>();

var query =
  	from subject in Subjects
	orderby subject.Name
	select new 
	{
		subject.Name,
		Books = from book in subject.Books
				where book.Price < 30
				select new 
				{
					book.Title,
					book.Price
				}
	};
	
query.Dump();

//
from p in Products
let spanishOrders = p.OrderDetails.Where (o => o.Order.ShipCountry == "Spain")
where spanishOrders.Any()
orderby p.ProductName
select new
{
	p.ProductName,
	p.Category.CategoryName,
	Orders = spanishOrders.Count(),	
	TotalValue = spanishOrders.Sum (o => o.UnitPrice * o.Quantity)
}