Get a list of elements by their ID in entity framework
Solution 1
var listOfRoleId = user.Roles.Select(r => r.RoleId);
var roles = db.Roles.Where(r => listOfRoleId.Contains(r.RoleId));
Solution 2
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.
Solution 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.)
Related videos on Youtube
Comments
-
Shawn Mclean almost 3 years
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.
-
Shawn Mclean about 13 yearsI cannot use r.RoleId because Roles is a class and RoleId is an int.
-
Slauma about 13 yearsIdea is good! You could insert a
.Select(ur => ur.RoleId).
between...user.Roles...
and....Contains...
. I'm curious if the query works then. -
moi_meme about 13 yearsShould be, check the generated SQL, or try it in LinqPad
-
Ladislav Mrnka about 13 yearsIf the
user
is loaded entity and if it has loadedRoles
it will be one query. -
Jess over 8 yearsThis works fine for small data sets, but if the list of ID's is large, you will want to do it another way. stackoverflow.com/questions/1869753/…
-
Amit Andharia over 7 yearsCan 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.
-
Rolando Retana over 7 years@AmitAndharia Did you solve that problem? I am getting the same error when using joins.
-
Amit Andharia over 7 years@RolandoPérez - Yes, use the Contains as shown in the answer above
-
Shehroze Malik over 2 yearsWorks like a charm :)