linq join tables with multiple columns with or condition

Question

I want to join 3 tables. From which two tables join with multiple columns by "or" condition then join 3rd table to the result of first two.

example as below:

    from t1 in db.table1
    from t2 in db.table2 
    where (t1.ISIN == t2.ISIN_GrowthDiv || t1.ISIN==t2.ISIN_DivReinv)  
    join t3 in table3 on t2.SchemeID equals t3.SchemeId

now tell me above query is correct or not?

Yes , I have checked the query in SQl profiler third table is crossjoin with above result query which I don't want. I want inner join — dev, Apr 23 '18 at 05:21
can you post your data (or a part of it) to let us check what happens? — Francesco B., Apr 23 '18 at 07:25
Are you sure you don't have duplicated data? Your code seems working... — Francesco B., Apr 23 '18 at 09:37
No I don't have duplicated data.Basically my Problem is third table is join as crossjoin to the result of first two tables & I Want innerjoin — dev, Apr 23 '18 at 09:54
Anyway try the last query in the edit section of my answer, that should take care of your problem. — Francesco B., Apr 23 '18 at 12:19
When I do this with LINQ to SQL for MS SQL Server, I get `CROSS JOIN` for the first two tables and `INNER JOIN` on the last table - what database engine/provider are you using? — NetMage, Apr 23 '18 at 18:55

Francesco B. · Answer 1 · 2018-04-23T12:18:06.657

Since you don't show your select clause, I will do a sample select; of course replace it with your own.

I have also created sample data, as you said you can't share yours, and stored in table1, table2 and table3:

var table1 = new List<Table1>()
{
    new Table1() { ISIN = 1 },
    new Table1() { ISIN = 4 }
};

var table2 = new List<Table2>() {
    new Table2() { ISIN_DivReinv = 0, ISIN_GrowthDiv = 1, SchemeID = 111 },
    new Table2() { ISIN_DivReinv = 1, ISIN_GrowthDiv = 0, SchemeID = 111 },
    new Table2() { ISIN_DivReinv = 1, ISIN_GrowthDiv = 0, SchemeID = 222 },
    new Table2() { ISIN_DivReinv = 2, ISIN_GrowthDiv = 2, SchemeID = 111 }
};

var table3 = new List<Table3>()
{
    new Table3() { SchemeId = 111 },
    new Table3() { SchemeId = 333 }
};

var Result =
    from t1 in table1
    from t2 in table2
    where (t1.ISIN == t2.ISIN_GrowthDiv || t1.ISIN == t2.ISIN_DivReinv)
    join t3 in table3 on t2.SchemeID equals t3.SchemeId
    select new { t1.ISIN, t2.ISIN_DivReinv, t3.SchemeId };

These are the results I'm getting:

As you can see, there's no cross-join; which is not surprising, since by default LINQ provides inner joins with the join operator and the only way to get cross joins is to just select without join, like in this answer.

EDIT: WHAT IF T3.SCHEMEID HAS DUPLICATES?

A "special" case is when t3 contains duplicated values, e.g. like

var table3 = new List<Table3>()
{
    new Table3() { SchemeId = 111 },
    new Table3() { SchemeId = 111 }
};

In this case you will get 4 rows instead of 2, since the inner join will properly occur for the two rows of t2 twice, one per matching value of t3.

This doesn't make it a "cross-join" anyway, but you can take distinct values of t3.SchemeId if your select doesn't need values from t3:

var Result =
    from t1 in table1
    from t2 in table2
    from t3 in table3.Select(f => f.SchemeId).Distinct()
    where (t1.ISIN == t2.ISIN_GrowthDiv || t1.ISIN == t2.ISIN_DivReinv) & t3 == t2.SchemeID
    select new { t1.ISIN, t2.ISIN_DivReinv, t2.SchemeID };

Yes Your Example works Fine, but when I tried it with my tables ......it again shows cross join in sql profiler. So I have changed my code & now I am fetching data using stored procedure — dev, Apr 25 '18 at 10:57
I think that `from t3 in table3.Select(f => f.SchemeId).Distinct()` might be the crucial part — Francesco B., Apr 25 '18 at 10:58

linq join tables with multiple columns with or condition

1 Answers1