Left Join with lambda Groupjoin and with condition

Question

How can I make one left join with some conditions using lambda LINQ expressions?

In SQL Server have this query:

select usr.Id, usr.FirstName, usr.LastName, ex.Id
from User usr
left join Exam ex on ex.Id = usr.IdExam 
and (ex.Id is null or (ex.InitialDate is null or ex.InitialDate >= getdate())
and (ex.EndDate is null or ex.EndDate <= getdate()))

But I need to do it in C# and I tried to do it this way with the GroupJoin:

dataModel.User
    .GroupJoin(dataModel.Exam,
        usr => usr.IdExam, ex => ex.Id,
        (usr, ex) => new { Usr = usr, Ex= ex})
    .DefaultIfEmpty()
    .SelectMany(final => final.Exam.Where(ex => ex == null ||
                                ((!ex.InitialDate.HasValue || DateTime.Compare(ex.InitialDate.Value, DateTime.Now) <= 0)
                                && (!ex.EndDate.HasValue || DateTime.Compare(ex.EndDate.Value, DateTime.Now) >= 0))),
        (final, ex) => new
        {
           IdUser = final.Usr.Id,
           FirstName = final.Usr.FirstName,
           LastName = final.Usr.LastName,
           IdExam = ex.Id
        }).ToList();

The problem is that in C# the expression is returning less data than the SQL query. What I am doing wrong?

Answer 1

Edit: I changed the place of Where, the logic that I made is for the collection already flattened, feel like doing another.

Groupjoin already makes a left Join, the point is that after we need to "flatten" with selectmany, your query in leftjoin seems correct, but Defaultifempty is in the wrong place.

dataModel.User
    .GroupJoin(dataModel.Exam,
        usr => usr.IdExam, ex => ex.Id,
        (usr, ex) => new { Usr = usr, Ex= ex.DefaultIfEmpty() })

So far only Leftjoin without filter and without flattening.

dataModel.User
            .GroupJoin(dataModel.Exam,
                usr => usr.IdExam, ex => ex.Id,
                (usr, ex) => new { Usr = usr, Ex= ex.DefaultIfEmpty() })
            .SelectMany(final => final.Ex,
                        (final, ex) => new
                        {
                           IdUser = final.Usr.Id,
                           FirstName = final.Usr.FirstName,
                           LastName = final.Usr.LastName,
                           IdExam = ex != null ? ex.Id : 0
                        })

After flattening, the filter.

dataModel.User
        .GroupJoin(dataModel.Exam,
            usr => usr.IdExam, ex => ex.Id,
            (usr, ex) => new { Usr = usr, Ex= ex.DefaultIfEmpty() })
        .SelectMany(final => final.Ex,
                (final, ex) => new
                {
                   IdUser = final.Usr.Id,
                   FirstName = final.Usr.FirstName,
                   LastName = final.Usr.LastName,
                   // Uma forma de proteger do null, não testado.
                   IdExam = ex != null ? ex.Id : 0
                })          
        .Where(final => (final.Ex == null) ||
                        ((final.Ex.Id == null || final.Ex.InitialDate == null || final.Ex.InitialDate >= DateTime.Now) &&
                        (final.Ex.EndDate == null || final.Ex <= DateTime.Now)))
        .ToList();