LINQ Join Where Clause

Question

I'm struggling with a join/where clause with what is a rather simple sql select statement.

I am trying to retrieve a list of product information from tb1 with the where condition behind situated in tbl2 but this must be joined by three different columns.

so the SQL would look something along the lines of:

SELECT     tb1.*
FROM         tb2 INNER JOIN
                      tb1 ON tb2.Col1 = tb1. Col1 AND tb2.Col2 = tb1. Col2 AND 
                      tb2.Col3 = tb1.Col3
WHERE     (tb2.Col1 = col1) AND (tb2.Col2 = col2) AND (tb2.Col4 = string)

ColX is the main where clause with the string to be passed in as parameter; all other columns are within the contexts.

How do you implement multiple joins with a where clause?

And shoves in the right direction, muchly appreciated.

Solution 1

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.

Solution 2

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.

Solution 3

Also you can group result and use subquery

var innerGroupJoinQuery2 =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID into prodGroup
    from prod2 in prodGroup
    where prod2.UnitPrice > 2.50M
    select prod2;

ref.: join clause (C# Reference)