Join And Group Tables

Group Join

from publisher in SampleData.Publishers
join book in SampleData.Books
  on publisher equals book.Publisher into publisherBooks
select new { Publisher=publisher.Name, Books=publisherBooks };

This is a group join. It bundles each publisher’s book as sequences named publisherBooks. This new query is equivalent to this one:

from book in SampleData.Books 
group book by book.Publisher into publisherBooks
select new { Publisher=publisherBooks.Key.Name, Books=publisherBooks };

Inner Join
An inner join essentially finds the intersection between two sequences. With an inner join, the elements from two sequences that meet a matching condition are combined to from a single sequence.

from publisher in SampleData.Publishers
join book in SampleData.Books
  on publisher equals book.Publisher
select new { Publisher=publisher.Name, Book=book.Title };

Left Outer Join
When we want to keep all the elements from the outer sequence, independently of whether there is a matching element in the inner sequence, we need to perform a left outer join.

A left outer join is like an inner inner join, except that all the left-side elements get included at least once, even if they don’t match any right side elements.

from publisher in SampleData.Publishers
join book in SampleData.Books
  on publisher equals book.Publisher into publisherBooks
from book in publisherBooks.DefaultIfEmpty()
select new { 
  Publisher = publisher.Name, 
  Book = book == default(Book) ? "(no books)" : book.Title
};

DefaultEmpty operator supplies a default element for an empty sequence.
DefaultEmpty uses the default keyword of generics. It returns null for reference types and zero for numeric value types. For structs, it returns each member of the struct initialized to zero or null depending on whether they are value or reference types.

Cross Join
A cross join computes the Cartesian product of all the elements from two sequences. The result is a sequence that contains a combination of each element from the first sequence with eacht element from the second sequence.

from publisher in SampleData.Publishers
from book in SampleData.Books
select new { 
  Correct = (publisher == book.Publisher)
  Publisher = publisher.Name, 
  Book = book.Title
};