Linq joins 3 tables with a condition

Question

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

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

tableA has columns int1 , int2 and intB . intB is associated with tableB .

problem: int1 or int2 of tableA may be intZ , and it must match a single tableC .

I need an OR condition, but I have no idea where to put it. Does this go to the where section? Or in the equals formula?

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

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

edit: Ok, now I think it is more clear. intZ must be connected to intC from tableC , and this number can be int1 or int2 of tableA .

Answer 1

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

 from a in tableA from b in tableB.Where(x => xA == aA || xB == aB) select new { a, b }; 

Answer 2

You cannot use the "or" condition in joins in LINQ, since it only supports equijoins. But you should be able to do this in the where clause without any problems. For instance:

 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 }; 

Answer 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 }; 

Answer 4

During my life I could not get. Wherever I work in my request (perhaps how I use LinqPad), but I managed 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 } 

Answer 5

Try the following: -

 var result= tableA.SelectMany(a => tableB.Where(x => xA == aA || xB == aB), (a, b) => new {a, b});