Use LINQ to Join on Multiple Fields

Question

How would I do this in LINQ?

SQL

Where tblAccounts.AccountCode = tblAccountAssociations.ChildCode
And tblAccountAssociations.AssociationType = "DS"

Here is my attempt. The problem seems to be with "assoc.AssociationType == "DS". Is it part of the join or the Where clause?

var customers = 
    from customer in context.tblAccounts 
    join assoc in context.tblAccountAssociations on customer.AccountCode equals assoc.ChildCode 
    where customer.AccountType == "S" and assoc.AssociationType == "DS" 
    orderby customer.AccountType 
    select new { Customer = customer, Assoc = assoc };

Thanks in advance

possible duplicate of [How to do joins in LINQ on multiple fields in single join](http://stackoverflow.com/questions/373541/how-to-do-joins-in-linq-on-multiple-fields-in-single-join) — vcsjones, Jun 01 '12 at 20:35
You need to add a bit more of your code. Your SQL doesn't show the full query, and your LINQ doesn't show the sets or types you are querying. e.g; what is 'assoc' ? — RJ Lohan, Jun 01 '12 at 20:39
vcsjones - I am not using a col1, col2 in tablea to col1, col2 in tableb. One of my conditions is a hard-coded value ie "DS" — dorkboy, Jun 01 '12 at 20:43
Does what you have now not work? It looks like it should be fine. — CAbbott, Jun 01 '12 at 20:44
CAbbott - this does not work. Among other things it says "A query body must end with a select clause or a group clause. — dorkboy, Jun 01 '12 at 20:48
You have a syntax error in your linq statement. Your where clause needs to use `&&` instead of `and`. — CAbbott, Jun 01 '12 at 21:06

score 0 · Answer 1 · answered Jun 01 '12 at 21:49

0

According to MSDN (http://msdn.microsoft.com/en-us/library/bb311043.aspx), you use "&&", not "and", to specify multiple conditions in the "where" clause.

answered Jun 01 '12 at 21:49

ekolis

6,270
12
50
101

score 0 · Answer 2 · edited Jun 05 '12 at 06:23

0

var customers =  
from customer in context.tblAccounts  
join assoc in context.tblAccountAssociations on customer.AccountCode equals assoc.ChildCode  
where customer.AccountType == "S" **&&** assoc.AssociationType == "DS"  
orderby customer.AccountType  
select new { Customer = customer, Assoc = assoc };

edited Jun 05 '12 at 06:23

Gert Arnold

105,341
31
202
291

answered Jun 04 '12 at 05:40

Manvinder

4,495
16
53
100

score 0 · Answer 3 · answered Jun 05 '12 at 07:18

Yes, "and" should be "&&", but the answer to your question is that in Linq the predicate assoc.AssociationType == "DS is not part of the join.

In SQL you could make it part of a join statement

...
FROM tblAccounts c
INNER JOIN tblAccountAssociations a ON
    c.AccountCode = a.ChildCode AND a.AssociationType == "DS" 
...

but in Linq statments you just add it as a predicate, the way you did (apart from the "and" issue).

In SQL, in terms of execution plan (performance), it does not matter whether you add it to the JOIN phrase or put it into a separate WHERE condition.

Use LINQ to Join on Multiple Fields

3 Answers3