In this example we are going to see how to perform a left join, using SQL and using the Entity Framework with the power of Linq, also with the dot notation.
Contents for Entity Framework: Left Join example C#
Starting Point
In this tutorial we will use the simple database created in the Entity Framework Database First Approach.
Syntax
Obviously you must know the syntax:
SELECT [column/columns]
FROM [table1-name]
LEFT [OUTER] JOIN [table2-name]
ON [table1-column] = [table2-column]
[OUTER] is optional and it changes nothing.
Remember: LEFT JOIN = LEFT OUTER JOIN
Result
What will be the result of a left join?
With the use of our supercomputers we were able to create an incredible and astonishing image, that will answer all your life doubts.
It’s really easy.
It’s like a join, but you also take with you the elements of the first collection, even if they haven’t any correlated element in the second collection.
Left Join Query
We left-join the two tables on the RobotFactoryId column, using also the group by.
The result will show the number of robot dogs for each location, even for those location lacking cyber-beasts.
{
var query = from robotFactory in context.RobotFactories
join dog in context.RobotDogs on robotFactory .RobotFactoryId equals dog.RobotFactoryId into gj
from subDog in gj.DefaultIfEmpty()
group subDog by robotFactory.Location into grouped
select new { Building = grouped.Key, RobotCount = grouped.Count() };
}
Or you can use the dot notation (a little more difficult in this case):
{
var query = context.RobotFactories.GroupJoin(
context.RobotDogs,
f => f.RobotFactoryId ,
d => d.RobotFactoryId ,
(f, dogsGroup) => new { f, dogsGroup })
.SelectMany(x => x.dogsGroup.DefaultIfEmpty(), (x, d) => new { Building = x.f.Location, RobotCount = x.dogsGroup.Count() });
}
DefaultIfEmpty is really important, it (as you can see in its dll): “Returns the elements of the specified sequence or the type parameter’s default value in a singleton collection if the sequence is empty.”