C# Linq Joining with AND conditions

Question

I am trying to figure on how to include a AND condition in my Linq2SQL statement

So my SQL as it looks in SSMS

SELECT TBL1.Field1, TBL2.Field2 FROM TABLE1 AS TBL1
INNER JOIN TABLE2 AS TBL2 ON TBL1.TBL2ID = TBL2.ID 
INNER JOIN TABLE3 AS TBL3 ON TBL1.TBL3ID = TBL3.ID AND TBL3.SQID = 20
WHERE TBL1.ID = 3

And my LINQ looks as follows

var linq = (from tbl1 in Table1
            join tbl2 in Table2 on tbl1.tabl2ID equals tbl2.ID
            join tbl3 in Table3 on tbl1.tabl3ID equals tbl3.ID
            where tbl1.ID = 3
            select new { field1 = tbl1.field1, field2 = tbl2.field2 
            }).ToList();

So how can I use a AND in my second join for tbl3? Any suggestions please?

score 0 · Answer 1 · answered Jun 01 '18 at 10:59

0

Just put extra where clause. tbl3.SQID == 20

var linq = (from tbl1 in Table1
            join tbl2 in Table2 on tbl1.tabl2ID equals tbl2.ID
            join tbl3 in Table3 on tbl1.tabl3ID equals tbl3.ID
            where tbl1.ID == 3 && tbl3.SQID == 20
            select new { field1 = tbl1.field1, field2 = tbl2.field2 
            }).ToList();

answered Jun 01 '18 at 10:59

lucky

12,734
4
24
46

You can actually join with multiple conditions using `join tbl3 in Table3 on new { TBLID = tbl1.tabl3ID, SQID = tbl3.SQID } equals new { TBLID = tbl3.ID, SQID = 20 }` – SᴇM Jun 01 '18 at 11:02
There is no difference for this case. – lucky Jun 01 '18 at 11:03
In this case - yes, there is no difference, however, there is difference between `where` clause and `on` clause. – SᴇM Jun 01 '18 at 11:05
Of course, I mentioned it for this case. – lucky Jun 01 '18 at 11:05
Correct, but first of all OP was asking about `AND inside LINQ join`, second you don't know was that example a real project example, or was written for sake of example. – SᴇM Jun 01 '18 at 11:07

C# Linq Joining with AND conditions

1 Answers1