Convert SQL query with an "In" condition to Lambda expression

Question

This may be quite simple, but unfortunately I have a difficulty in figuring out converting an SQL query with an "In" condition to lambda.

SELECT * 
FROM cm_wfapp 
WHERE recgid IN (select distinct wfapp_id from cm_wftrn where approver_id = 32060)

Is there any way to convert this?

Answer 1

For clearness you can do 2 queries in LINQ. Remeber that LINQ has defered execution model, so actual query will be generated in the moment of actual request.

So you can try to do something like this:

var ids = wfapp_id.Where(i=>i.approver_id == 32060); 

//after use a ids in yor final query. 
var result = cm_wfapp.Where(id=>ids.Contains(id.recgid)); 

This should produce optimal result, but you need to test it agains real DB and its data, to see if LINQ generates good SQL or not.

Answer 2

This is untested (for obvious reasona), but you could try:

var query = from app in db.wfapp
            where db.wftrn.Where(x => x.approver_id==32060)
                          .Select(x => x.wfapp_id).Contains(app.recgid)
            select app

Let me know whether it works, though.

Alternatively: consider writing it as SQL; most ORms offer a pass-thru mechanism.

Answer 3

You can get a List<int> for your sub query like:

List<int> listOfWfaap_id = dbContext.cm_wftrn
                                .Where(r=> r.approver_id == 32060)
                                .ToList();

Then use Contains to check for each ID like:

var result = dbContext.cm_wfapp
                    .Where(r=> listOfWfapp_id.Contains(r=> r.recgid);

You may see: Creating IN Queries With Linq To Sql

Answer 4

If you are using entity framework pre version 6 and .Contains() it is not natively supported:

Native support for Enumerable.Contains

Answer 5

You could build your own extension-method like this:

http://geekswithblogs.net/EltonStoneman/archive/2010/07/11/where-in-style-queries-in-linq-to-entities.aspx

which you can use like this:

var result = cm_wfapp.WhereIn(wfapp_id.Where(i=>i.approver_id == 32060));