Deploy statement in Entity Framework

Question

I am trying to implement this SQL query in Entity Framework

select * from students where student.fieldid in (select fieldid from fields where groupid = 10) 

I assume this is the approach:

 var fields = context.fields.where(t=>t.groupid = 10).toList(); var result = context.students.where(t=> fields.Contains(t.fieldid)).toList(); 

but it does not work!

Has anyone else tried to do something like this?

Answer 1

SQL IN equivalent to LINQ Contains .

 var names = new string[] { "Alex", "Colin", "Danny", "Diego" }; var matches = from person in people where names.Contains(person.Firstname) select person; 

So, the SQL statement:

 select * from students where student.fieldid in ( select fieldid from fields where groupid = 10) 

equivalent in LINQ:

 var fieldIDs= from Fids in db.fields where Fids.groupid==10 select Fids.fieldid; var results= from s in db.students where fieldIDs.Contains(s.fieldid) select s;