LINQ Lambda where in subquery

Question

I´m trying do something like this :

Select * from A where id in (Select id_a from B)

But in LINQ

db.A().Join(db.B(), a => a.id, b => b.id_a, (a , b) => new { a, b}).....

I can do a JOIN. Is the best way? Or i have another options?.

I´m using Entity Framework

Thanks

Welcome to StackoverFlow. Please [take the tour](https://stackoverflow.com/tour), read about [how to ask good questions](https://stackoverflow.com/help/how-to-ask) and learn about [how to create a Minimal, Complete and Verifiable Example](https://stackoverflow.com/help/mcve). — Gaurang Dave, Apr 10 '18 at 08:59
I think what you are doing is correct. Hope you are getting result you want — Gaurang Dave, Apr 10 '18 at 09:01
Are you using EntityFramework? Do `db.A()` and `db.B()` return `IQueryable`? — haim770, Apr 10 '18 at 09:02
Yes, i´m using EntityFramework and both return IQueryable. But didn´t work in the examples of below — Sebastian, Apr 11 '18 at 14:44

score 1 · Answer 1 · answered Apr 10 '18 at 19:30

From my SQL to LINQ recipe:

For translating SQL to LINQ query comprehension:

Translate subselects as separately declared variables.
Translate IN to .Contains() and NOT IN to !...Contains(), using literal arrays or array variables for constant lists.
SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.

So, for your SQL,

var id_aInB = from b in db.B select b.id_a;
var ans = from a in db.A where id_aInB.Contains(a.id) select a;

score 0 · Answer 2 · answered Apr 10 '18 at 09:04

0

Using subquery in LINQ lambda

var q = db.A.Where(a=> db.B.Select(b=>b.id_a).toList().Contains(a.Id)).Select(a=>a);

answered Apr 10 '18 at 09:04

Gaurang Dave

3,956
2
15
34

Don't use `ToList` when using LINQ to SQL. Don't do worthless `Select(a => a)`. – NetMage Apr 10 '18 at 19:21

score 0 · Answer 3 · answered Apr 10 '18 at 09:45

In Linq there are many ways to express that. That SQL can also be expressed in different ways and probably the best is to use an EXISTS query instead, like:

Select * from A 
where EXISTS (Select * from B where A.id = B.id_a)

That could be written in Linq as:

db.A.Where( a => db.B.Any( b => a.Id == b.Id_a ) );

score 0 · Answer 4 · answered May 16 '19 at 09:22

Maybe you need this:

var result=db.A.Select(c=>new {c,listId=db.B.Select(s=>s.id_a)}).Where(w=>w.listId.Contains( w.c.id)).Select(c=>c.c);

Or you can use LINQ like this

from a in db.A
let listId = from b in db.B
select b.id_a
where listId.Contains(a.id)
select a

By the way, use LINQPad,you can get the right lamda by LINQ search

LINQ Lambda where in subquery

4 Answers4