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
From my SQL to LINQ recipe:
For translating SQL to LINQ query comprehension:
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;
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);
ToList when using LINQ to SQL. Don't do worthless Select(a => a).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 ) );
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
db.A()anddb.B()returnIQueryable?