How to apply the SQL 'in' keyword in Entity Framework method syntax?

Question

How to apply the SQL in keyword in Entity Framework method syntax?

For example, if I want to write the query in Entity Framework

select roleName from Roles where rold_id in (select role_id from UserRoles where user_id = 1);

So, how to apply that in Entity Framework method syntax?

PhonicUK · Accepted Answer · 2015-08-02 19:25:17Z

5

The inner query would be done separately:

var inner = UserRoles.Where(r => r.user_id == 1)
                     .Select(r => r.role_id);

And then the outer would use the .Contains method of the inner.

var roleNames = Roles.Where(r => inner.Contains(r.role_id))
                     .Select(r => r.roleName);

You could merge it all into a single query, but this is the sanest way to do it. Entity Framework uses deferred queries so it will still do this efficiently.

Edit: Just for completeness sakes, here's a one-line version:

var roleNames = Roles.Where(r => UserRoles
                         .Where(ur => ur.user_id == 1)
                         .Select(ur => ur.role_id)
                     .Contains(r.role_id))
                     .Select(r => r.roleName);

In a nut shell, instead of it being 'B is in set A', it's more like 'Set A contains B'.

edited Aug 2, 2015 at 19:25

answered Aug 2, 2015 at 16:05

PhonicUK

13.9k4 gold badges47 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

How to apply the SQL 'in' keyword in Entity Framework method syntax?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related