LINQ Join Where Clause

To join on multiple field in LINQ, you have to create a new anonymous type containing the columns you want to compare and then use that anonymous type in the join:

var results = from t1 in context.tb1
              join t2 in context.tb2
              on new { t1.Col1, t1.Col2, t1.Col3 } equals
                  new { t2.Col1, t2.Col2, t2.Col3 }
              where t2.Col1 == col1 && t2.Col2 == col2 && t2.Col4 == someString
              select t1;

And here is the equivalent Lambda Syntax:

var results = context.tb1.Join(
                  context.tb2,
                  t1 => new { t1.Col1, t1.Col2, t1.Col3 },
                  t2 => new { t2.Col1, t2.Col2, t2.Col3 },
                  (t1, t2) => new { t1, t2 })
              .Where(o => o.t2.Col1 == col1 
                  && o.t2.Col2 == col2
                  && o.t2.Col4 == someString)
              .Select(o => o.t1);

As you can see, in the case of joins, query syntax usually produces an easier to read statement.

You can also include the WHERE clause in lamda syntax in the reference to the table you're joining on.

var query = from pt in dc.ProjectTasks
            join ttab in dc.TimeTaskAssigns on pt.Id equals ttab.ProjectTaskId
            join ttb2 in dc.CMS_TAT_TIMEs.Where(a => a.WIP_STATUS == 'B') on ttab.CmsTimeUno equals ttb2.TIME_UNO
            select pt;

Seems obvious now, doesn't it? It took me a long time to find that solution.