linq join 3 tables with or condition

Question

I need to create a statement in LINQ with 3 tables and OR condition.

My function receives an integer, lets call it intZ. I have 3 tables: tableA, tableB and tableC.

tableA has columns int1, int2 and intB. intB is related to tableB.

problem: int1 or int2 of tableA can be intZ and it has to match with one tableC record.

I need an OR condition, but I have no idea where to place it. Does it go in the where clause? Or in the equals clause?

At the moment, I know how to join 3 tables, but the condition is killing me.

What is the difference between the two ways to create statements in linq? Is there a performance impact?

edit: Okay, now I think it's more clear. intZ has to be related with intC from tableC, and this number can be int1 or int2 of tableA.

Solution 1

Just add it to a Where. In Linq2Sql this will be translated to an inner join (with or) on tableB

from a in tableA
from b in tableB.Where(x => x.A == a.A || x.B == a.B)
select new { a, b };

Solution 2

You can't use an "or" condition in joins in LINQ, as it only supports equijoins. But you should be able to do it in a where clause with no problems. For example:

var query = from rowC in tableC
            where rowC.intC == intZ
            from rowA in tableA
            where rowA.int1 == rowC.intC || rowA.int2 == rowC.intC
            join rowB in tableB on rowA.intB equals rowB.intB
            select new { rowA, rowB, rowC };

Solution 3

This may be helpful.

var locations = from r1 in 
          (from a in context.A
          join b in context.B
          on a.ID equals b.ID
          select new
          {
            a.Prop1,
            a.Prop2,
            b.Prop3,
            b.ID
          })
          join c in context.C
          on r1.ID equals c.ID
          select new
          {
            r1.Prop1,
            r2.Prop2,
            r2.Prop3,
            c.Prop4
          };

Solution 4

For the life of me, I couldn't get the .Where to work in my query (perhaps it's how I'm using LinqPad) but I was able to get the following to work:

from s in Stores
join a in Areas on s.AreaID equals a.ROWID
join r in Regions on a.RegionID equals r.ROWID
join e in Employees on 1 equals 1     // <-- produces a cartesian product
join t in Titles on e.TitleID equals t.ROWID
where e.AreaID == a.ROWID || e.RegionID == r.ROWID // <--filters the data based on OR stmt
where s.StoreNum == 469
select new { r.RegionName, a.AreaName, s.StoreNum, s.StoreName, t.JobCode, e.FirstName, e.LastName }

View more solutions

Comments

[Guillermo Varini]

the tableC comes in a 3rd join i suppose, where i have to match intz == tableC.id or rowA.int1 == tableC.id or rowa.int2 == tableC.id. how should u put tableC.id in this?

[Jon Skeet]

@GuillermoVarini: Sorry, it's still really clear how all of this is meant to hang together. It would be clearer if you could give a full example, ideally with more meaningful names than int2 etc.

[Guillermo Varini]

i never used this type of query statement on linq. what is the x? i saw this before but i dont know the same so i can read more about it. can u show me a link or a name? thx

[Magnus]

x is a row in tableB (but only inside there where clause) I use it to join with table a on columns A OR B

[Guillermo Varini]

ok, its has a picture now and check where its says edit: i think now is much more clear.

[Guillermo Varini]

con u provide me the name of this type of query? its has different structure from the one im being using. thx

[Jon Skeet]

@GuillermoVarini: Okay, have a look now. Looks like it should be okay.