How to use ‘In’ SQL keyword in Entity Framework?

This is my SQL command

SELECT KEY,NAME
from  COMPANY c 
WHERE     KEY IN (select KEY from USER_COMPANY  where UserId = @UserId)
order by NAME asc

So I want to convert it to Entity Framework.

I try like this

 var userCompany = (from u in db.USER_COMPANY
                         where u.UserId == UserId 
                         select(u.KEY));

            var user = (from c in db.COMPANY
                        where (c => userCompany.Contains(c.KEY)
                        select c);

but it is not working.

How to use the SQL IN keyword in Entity Framework?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Try this:

var query = from c in db.COMPANY
            where (from u in db.USER_COMPANY
                   where u.UserId == UserId
                   select u.KEY).Contains(c.KEY)
            orderby c.NAME
            select c.KEY, c.NAME;

Method 2

Note that this SQL query has the exact same meaning:

SELECT c.KEY, c.NAME
FROM COMPANY c
JOIN (SELECT DISTINCT KEY FROM USER_COMPANY where UserId = @UserId) u
ON U.KEY = C.KEY
ORDER BY c.NAME asc

So you should be able to just do:

var userCompany = (from u in db.USER_COMPANY
                   where u.UserId == UserId 
                   select(u.KEY)).Distinct();

var result = from c in db.COMPANY
             join u in userCompany
             on c.KEY = u.KEY
             select new {c.KEY, c.NAME};

Method 3

My understanding is that the translation from .Contains() to IN is only just being added to EF6.

According to this work item (http://entityframework.codeplex.com/workitem/245) and the release note: Improved performance of Enumerable.Contains in LINQ queries.

So look for it in EF6

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0