Select multiple entries based on Id list with linq

Question

I have a list containing the Id of my UserProfile table. How can I select all UserProfiles based on the list of Id i retrieved in var using LINQ ?

 var idList = new int[1, 2, 3, 4, 5]; var userProfiles = _dataContext.UserProfile.Where(......); 

I'm stuck right here. I can do this using for loops etc. But I would rather do it with LINQ .

Answer 1

You can use Contains() . If you are really trying to create an IN clause, this will be a little bit back, but this should do it:

 var userProfiles = _dataContext.UserProfile .Where(t => idList.Contains(t.Id)); 

I also assume that each UserProfile entry will have an int Id field. If this is not the case, you will have to configure accordingly.

Answer 2

The solution with .Where and .Contains has O (N square) complexity. A simple .Join should have much better performance (close to O (N) due to hashing). Therefore, the correct code is:

 _dataContext.UserProfile.Join(idList, up => up.ID, id => id, (up, id) => up); 

And now the result of my measurements. I created 100,000 UserProfiles and 100,000 identifiers. Joining took 32 ms and. Where with .Contains it took 2 minutes and 19 seconds! I used pure IEnumerable for this test to prove my claim. If you use List instead of IEnumerable, .Where and .Contains will be faster. In any case, the difference is significant. The fastest. Where. Consists of Set <>. It all depends on the complexity of the base wheels for. Check out this post to learn about linq complexity. Take a look at my test case below:

  private static void Main(string[] args) { var userProfiles = GenerateUserProfiles(); var idList = GenerateIds(); var stopWatch = new Stopwatch(); stopWatch.Start(); userProfiles.Join(idList, up => up.ID, id => id, (up, id) => up).ToArray(); Console.WriteLine("Elapsed .Join time: {0}", stopWatch.Elapsed); stopWatch.Restart(); userProfiles.Where(up => idList.Contains(up.ID)).ToArray(); Console.WriteLine("Elapsed .Where .Contains time: {0}", stopWatch.Elapsed); Console.ReadLine(); } private static IEnumerable<int> GenerateIds() { // var result = new List<int>(); for (int i = 100000; i > 0; i--) { yield return i; } } private static IEnumerable<UserProfile> GenerateUserProfiles() { for (int i = 0; i < 100000; i++) { yield return new UserProfile {ID = i}; } } 

Console output:

Elapsed time: 00: 00: 00.0322546

Expired. Where. Time: 00: 02: 19.4072107

Answer 3

It should be easy. Try the following:

 var idList = new int[1, 2, 3, 4, 5]; var userProfiles = _dataContext.UserProfile.Where(e => idList.Contains(e)); 

Answer 4

Good answers answer, but do not forget one IMPORTANT thing - they give different results!

  var idList = new int[1, 2, 2, 2, 2]; // same user is selected 4 times var userProfiles = _dataContext.UserProfile.Where(e => idList.Contains(e)).ToList(); 

This will return two rows from the database (and this may be correct if you only want a separate sorted list of users)

BUT , in many cases, you may need an od sorted list od. You should always think of it as an SQL query. Please see the shopping cart example to illustrate what happens:

  var priceListIDs = new int[1, 2, 2, 2, 2]; // user has bought 4 times item ID 2 var shoppingCart = _dataContext.ShoppingCart .Join(priceListIDs, sc => sc.PriceListID, pli => pli, (sc, pli) => sc) .ToList(); 

This will return 5 results from the database. Using 'contains' would be wrong in this case.