50

How can I get all elements that are in another list by ID? I have List roles; I'd like to get all roles from the database that are in this list by their Id.

I'm using code-first.

I did this and it threw an error:

var roles = db.Roles.Where(r => user.Roles.Any(ur => ur.RoleId == r.RoleId));

RoleId is of type int.

Error:

Unable to create a constant value of type 'SampleMVC.Domain.Role'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Grigory Zhadko
  • 1,484
  • 1
  • 19
  • 33
Shawn Mclean
  • 56,733
  • 95
  • 279
  • 406

3 Answers3

119
var listOfRoleId = user.Roles.Select(r => r.RoleId);
var roles = db.Roles.Where(r => listOfRoleId.Contains(r.RoleId));
moi_meme
  • 9,180
  • 4
  • 44
  • 63
  • Should be, check the generated SQL, or try it in LinqPad – moi_meme Apr 11 '11 at 18:21
  • If the `user` is loaded entity and if it has loaded `Roles` it will be one query. – Ladislav Mrnka Apr 11 '11 at 20:13
  • 5
    This works fine for small data sets, but if the list of ID's is large, you will want to do it another way. http://stackoverflow.com/questions/1869753/maximum-size-for-a-sql-server-query-in-clause-is-there-a-better-approach – Jess Sep 02 '15 at 14:04
  • Can this work with joins too? I've tried the same thing, and its giving me this error : Cannot compare elements of type 'System.Collections.Generic.List`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]'. Only primitive types, enumeration types and entity types are supported. – Amit Andharia Sep 06 '16 at 04:26
  • @AmitAndharia Did you solve that problem? I am getting the same error when using joins. – Rolando Retana Sep 21 '16 at 01:07
  • @RolandoPérez - Yes, use the Contains as shown in the answer above – Amit Andharia Sep 21 '16 at 13:44
15

Something like this should work if user.Roles is a list of ints:

var roles = db.Roles.Where(r => user.Roles.Contains(r.RoleId));

That turns it into a "SELECT WHERE IN (x, y, z...)" in SQL.

Chris Smith
  • 151
  • 2
  • I cannot use r.RoleId because Roles is a class and RoleId is an int. – Shawn Mclean Apr 11 '11 at 17:21
  • 1
    Idea is good! You could insert a `.Select(ur => ur.RoleId).` between `...user.Roles...` and `....Contains...`. I'm curious if the query works then. – Slauma Apr 11 '11 at 17:45
3

You can't combine a local list with remote data, then there is nothing for the db to read from since the data is elsewere (on your client).

I think there might be better solution to what you're trying to do;

It seems like you're trying to fetch all roles assigned to a specific user. If that's the case i would suggest a solution where you're passing the current user id to the database and fetch the roles assigned with a INNER JOIN.

Depending on your database it might look something like this (if you're connecting users with roles through a table called 'UserRoles')

var roles = db.UserRoles.Where(x => x.UserID == <insert id>).Select(x => x.Role)

(Of course you could also create a stored procedure returning a list of 'Role' if you like directly in your db and map it.)

Jonas Stensved
  • 14,378
  • 5
  • 51
  • 80