C# Language LINQ Queries JOINS
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<string>(){ "a","b","c"}; // Left data
var second = new List<string>(){ "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<Region>()
{ new Region(1), new Region(3), new Region(4) };
// Right data
var second = new List<Region>()
{
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.
