9
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?
Ninita, I asked a question addressing your question http://answall.com/questions/50456/entity-framework-left-join, it also shows my way of making left Join
– Pablo Tondolo de Vargas