C# EF Linq syntax query with multiple conditions and in clause

Question

I have a linq with query syntax on entity framework. This is the sql

Select * from x
join y on y.a = x.a
join p on p.b = x.b and x.n in (3,4)
--other joins

this is the linq

var k = from typeX in x
join typeY in y on y.a equals x.a
join typeP in p on p.b = x.b  ???? (AND x.n in 3,4???)
//Other joins

These DOESN'T WORK

join p in p on x.b equals p.b and x.n.Contains(3,4)

Even or, || and && does not work and aren't recognized as keywords

Also using parenthesis ( with || or, and inside) don't work.

What am I missing?

I need to do a join with multiple condition of which one is an "IN CLAUSE".

Thanks

Been a while, but I think you can put the 3,4 logic in the where clause rather than the join. — jlew, Mar 06 '19 at 16:22
Look at [this post](https://stackoverflow.com/questions/857973/linq-to-entities-sql-in-clause) for an example of the proper syntax for using LINQ `Contains()` in the same manner as SQL `IN()`. If you read the accepted answer carefully you will find a nice explanation about the way this construct is 'inverted' from the way we are used to seeing / reading it. — David Tansey, Mar 06 '19 at 16:32

Stemado · Accepted Answer · 2019-03-07T00:36:15.223

Here is a DotNetFiddle with a working example of what I took from your question:

You can place 3,4 into an array and add it to your LINQ query then check if the array contains x.n:

int[] myNumbers = new int[] {3, 4};

join p in pData on x.b equals p.b where myNumbers.Contains(x.n)

Here is the full query (variables are slightly different than yours so it is easier for me to read them, but should be the same logic):

    int[] myN = new int[] {3, 4};
    var result = from typeX in theX
        join typeY in theY on typeX.a equals typeY.a
        join typeP in theP on typeX.b equals typeP.b where myN.Contains(typeX.n)
        join typeD in theD on typeX.z equals typeD.z
        select typeX;

this pointed me to the right path but I could not use this solution anyway. I have to add null checks everywhere otherwise query would crash. Very unpractical. I added Associations in EF edmx files and got the result through .Include("Entity") — Liquid Core, Mar 08 '19 at 16:10

score 0 · Answer 2 · answered Mar 07 '19 at 15:40

I'm not really familiar with query syntax for LINQ, hence I'll show how to do it using Method syntax.

You want to join several tables, and after some joins, you want to join with table P. But you don't want to join all previous join results with table P, you only want to use those previous join results that that have an x.n equal to 3 or 4.

If you will throw away all previous join results with x.N value neither 3, nor 4, then it's no use to put them in the previous join result anyway:

var requiredNvalues = new int[] {3, 4};
var result = X
    .Where(x => requiredNvalues.Contains(x.N)  // keep only the required x elements

    // Join the remaining elements of X with Y:
    .Join(Y,                   
    x => x.A,      // from every x in X take the A
    y => y.A,      // from every y in Y take y.A

    // ResultSelector: when they match make one new object containing matching X and Y
    (x, y) => new  {X=x, Y=y})

   // join  with third table:
   .Join(Z,

   previousJoinResult => previousJoinResult.X.Id,
                            // from every item in previous join result take X.Id
   z => z.xId,              // from every z from Zcollection take Xid,

   // ResultSelector: when they match make one new object containing matching X Y Z
   (previousJoinResult, z) => new {X = previousJoinResult.X, Y = previousJoinResult.Y, Z = z}
   ... etc, other joins

Coninue joining in a similar way until you want to join P:

   .Join(P,
   previousJoinResult => previousJoinResult.X.b, 
                             // from every X from the previous join result take b
   p => p.b                  // from every p take b

   // resultSelector: select X Y Z ... P
   (previousJoinResult, p) => new
   {
       X = previousJoinResult.X,
       Y = previousJoinResult.Y,
       Z = previousJoinResult.Z,
       ...
       P = p;
   });

Simple comme bonjour!

C# EF Linq syntax query with multiple conditions and in clause

2 Answers2