How to use 'In' SQL keyword in Entity Framework?

Question

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?

This isn't much help, but I'm on my phone and can't check code. You want to look into the LET keyword. — Mister Epic
– Mister Epic, Commented Aug 26, 2013 at 1:28

Karl Anderson · Accepted Answer · 2013-08-26 01:29:23Z

4

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;

answered Aug 26, 2013 at 1:29

Karl Anderson

34.9k12 gold badges69 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Euro Micelli · Accepted Answer · 2013-08-26 01:30:22Z

1

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};

answered Aug 26, 2013 at 1:30

Euro Micelli

34.2k8 gold badges54 silver badges75 bronze badges

Comments

Alastair Pitts · Accepted Answer · 2013-08-26 01:17:41Z

-1

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

answered Aug 26, 2013 at 1:17

Alastair Pitts

19.6k9 gold badges72 silver badges98 bronze badges

Collectives™ on Stack Overflow

How to use 'In' SQL keyword in Entity Framework?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related