Left join with lambda GroupJoin and with condition

Question

Left join with lambda GroupJoin and with condition

Navigation

#1 by (5 votes)

9

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

In SQL Server I 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 tried to do this with 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 query in SQL. What am I doing wrong?

sql sql-server c# linq

asked by anonymous 11.02.2015 / 20:02

1 answer

Display something when trying to close my site Connection with the Bank falls after a certain time

score 5 · Accepted Answer

Edit: I changed the place of where, the logic that I did is for the already flattened collection, feel the urge to make another.

GroupJoin already does a left join, the issue is that we then need to "flatten" with selectmany, your query in leftjoin looks 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 the 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();