Matthew Matthew - 2 months ago 9
C# Question

Adding second condition in LINQ JOIN,

I have looked around and found a few posts on adding a second condition to a JOIN clause but they are always instances of having one column link to another but in my instance I need to just have a column equal a certain value. The RAW SQL I am trying to imitate is:

LEFT OUTER JOIN dbo.TimeCardHeader AS tch2 on tch1.EmployeeID = tch2.EmployeeID && tch2.WeekEndingDate = @PayPeriod


As you can see my second join condition is to match a column value from the table to a variable. I have tried the following LINQ queires but they all fail.

join leftth2 in db.TimeCardHeaders on th1.EmployeeID equals leftth2.EmployeeID AND leftth2.WeekEndingDate == PayPeriod into leftjointh2

join leftth2 in db.TimeCardHeaders on th1.EmployeeID equals leftth2.EmployeeID && leftth2.WeekEndingDate == PayPeriod into leftjointh2

join leftth2 in db.TimeCardHeaders on new
{
employeeID = th1.EmployeeID,
weekEndingDate = leftth2.WeekEndingDate
} equals new
{
employeeID = leftth2.EmployeeID,
weekEndingDate = PayPeriod
}


The first two fail saying
AND
and
&&
are not valid, the last fails saying
leftth2
is not in the scope of the left side.

What is the proper way of doing this?

Answer

The condition

tch2.WeekEndingDate = @PayPeriod

doesn't feel like part of the join to me - it's not comparing data in two different rows. It feels like it should be part of what you're joining on, leading to:

join leftth2 in db.TimeCardHeaders.Where(p => p.WeekEndingDate == PayPeriod)
    on th1.EmployeeID equals leftth2.EmployeeID into leftjointh2