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?

Answer 1

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'.