JOINS C# with Example

Joins are used to combine different lists or tables holding data via a common key. 
Like in SQL, the following kinds of Joins are supported in LINQ: 
Inner, Left, Right, Cross and Full Outer Joins. 
The following two lists are used in the examples below: 
var first = new List(){ "a","b","c"}; // Left data 
var second = new List(){ "a", "c", "d"}; // Right data 
 

(Inner) Join 
var result = from f in first 
join s in second on f equals s 
select new { f, s }; 
var result = first.Join(second, 
f => f, 
s => s, 
(f, s) => new { f, s }); 
// Result: {"a","a"} 
// {"c","c"} 
Left outer join 
var leftOuterJoin = from f in first 
join s in second on f equals s into temp 
from t in temp.DefaultIfEmpty() 
select new { First = f, Second = t}; 
// Or can also do: 
var leftOuterJoin = from f in first 
from s in second.Where(x => x == f).DefaultIfEmpty() 
select new { First = f, Second = s}; 
// Result: {"a","a"} 
// {"b",  null} 
// {"c","c"} 
// Left outer join method syntax 
var leftOuterJoinFluentSyntax = first.GroupJoin(second, 
f => f, 
s => s, 
(f, s) => new { First = f, Second = s }) 
.SelectMany(temp => temp.Second.DefaultIfEmpty(), 
(f, s) => new { First = f.First, Second = s }); 
Right Outer Join 
var rightOuterJoin = from s in second 
join f in first on s equals f into temp 
from t in temp.DefaultIfEmpty() 
select new {First=t,Second=s}; 
// Result: {"a","a"} 
// {"c","c"} 
// {null,"d"} 
Cross Join 
var CrossJoin = from f in first 
from s in second 
select new { f, s }; 
// Result: {"a","a"} 
// {"a","c"} 
// {"a","d"} 
// {"b","a"} 
// {"b","c"} 
// {"b","d"} 
 

// {"c","a"} 
// {"c","c"} 
// {"c","d"} 
Full Outer Join 
var fullOuterjoin = leftOuterJoin.Union(rightOuterJoin); 
// Result: {"a","a"} 
// {"b",  null} 
// {"c","c"} 
// {null,"d"} 
Practical example 
The examples above have a simple data structure so you can focus on understanding the different LINQ joins 
technically, but in the real world you would have tables with columns you need to join. 
In the following example, there is just one class Region used, in reality you would join two or more different tables 
which hold the same key (in this example first and second are joined via the common key ID). 
Example: Consider the following data structure: 
public class Region 
{ 
public Int32 ID; 
public string RegionDescription; 
public Region(Int32 pRegionID, string pRegionDescription=null) 
{ 
ID = pRegionID; RegionDescription = pRegionDescription; 
} 
} 
Now prepare the data (i.e. populate with data): 
// Left data 
var first = new List() 
{ new Region(1), new Region(3), new Region(4) }; 
// Right data 
var second = new List() 
{ 
new Region(1, "Eastern"), new Region(2, "Western"), 
new Region(3, "Northern"), new Region(4, "Southern") 
}; 
You can see that in this example first doesn't contain any region descriptions so you want to join them from 
second. Then the inner join would look like: 
// do the inner join 
var result = from f in first 
join s in second on f.ID equals s.ID 
select new { f.ID, s.RegionDescription }; 
//  Result:  {1,"Eastern"} 
// {3, Northern} 
// {4,"Southern"} 
 

This result has created anonymous objects on the fly, which is fine, but we have already created a proper class - so 
we can specify it: Instead of SELECT NEW { f.ID, s.RegionDescription }; we can say SELECT NEW Region(f.ID, 
s.RegionDescription);, which will return the same data but will create objects of type Region - that will maintain 
compatibility with the other objects. 
Live demo on .NET fiddle