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?
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
See more on this question at Stackoverflow