CODEWITHSUNDEEP
c#entity-frameworklinq
Shaho
Shaho

Reputation: 182

How to have IN clause in linq to EF having two tables?

I'm writing a query in order to select some data in tabla A where the column C1 is in the column C2 in table B.

It's easy to have such a query in SQL. The code is something like this:

select * from A where C1 in (Select C2 from B)

What I've tried is like this code:

var InCluse = from p in B select new { p.C2 };
var query = from p in (from q in A
                       where InClause.Contains(q.C1)
                       select q)
            select p;

But there is an error which says:

Severity Code Description Project File Line Suppression State Error CS1929 'IQueryable<>' does not contain a definition for 'Contains' and the best extension method overload 'AsyncEnumerable.Contains(IAsyncEnumerable, int)' requires a receiver of type 'IAsyncEnumerable' "ProjectName" "ClassFileAddress" 261 Active

There are questions like mine, But the difference between this one and the others is that the other questions are about using "IN" clause in order to set a condition to an array of some data, not a table.

Upvotes: 0

Views: 129

Answers (2)

koryakinp
koryakinp

Reputation: 4125

Why not to simply join A and B ?

var res = ctx.A.Join(ctx.B, q => q.C1, q => q.C2, (q, w) => q);

Upvotes: 1

NetMage
NetMage

Reputation: 26907

The SQL can be pretty much literally translated to LINQ:

var ans = from a in A
          where (from b in B select b.C2).Contains(a.C1)
          select a;

You can also use join to accomplish the same result:

var ans = from a in A
          join b in B on a.C1 equals b.C2
          select a;

Upvotes: 1

Related Questions