Entity Framework: Left Join example C#

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.

Starting Point

In this tutorial we will use the simple database created in theĀ Entity Framework Database First Approach.

robot edmx


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.


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.

using (RobotIndustryEntities context = new RobotIndustryEntities())
    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):

using (RobotIndustryEntities context = new RobotIndustryEntities())
   var query = context.RobotFactories.GroupJoin(
    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.”