Efficiency with the Linq Contains method where parameter is an array

Question

Given an entity that looks like this:

 public ICollection<UserActivity> Activities { get; set; }
 public ICollection<UserTraining> Training { get; set; }

And wanting to filter Users based on an array of IDs, i.e. calling the following method:

public User[] GetUsers(int[] activityIds, int[] trainingIds)

What is the most efficient way of writing the above method implementation. If I was passing in a non-array integer it would be trivial, but I want to know the best way of writing it to avoid multiple database calls.

I'm using Linq to Entities.

The operation should return users who have any of the activity or training IDs, not all of them.

Using Contains is the best way I've found. Here's a previous posting that covers it pretty well: http://stackoverflow.com/questions/2369022/using-contains-in-linq-to-sql — jfrankcarr, Jul 19 '13 at 03:33
@jfrankcarr Thanks, but Contains does not contain an overload for an array. — Sam, Jul 19 '13 at 03:35
There's an IEnumerable/Array extension for Contains. System.Linq brings it in. — jfrankcarr, Jul 19 '13 at 03:42
Is the task to get all the users who have partaken in a given activity OR training? If so, is there a User property on both `UserActivity` and `UserTraining` — David Colwell, Jul 19 '13 at 03:44
@DavidColwell No, there are no properties back to the user on those objects as it is many-to-many; the operation should be an OR, not AND — Sam, Jul 19 '13 at 03:47
The LINQ implementation is important here. Is this linq to objects, or Entities/SQL/...? Further, you say it's an OR, but what about the match of the Ids? Should a user have _exactly all_ activities in `activityIds`, _at least all_ of them, or _some_ of them? — Gert Arnold, Jul 19 '13 at 10:06

score 2 · Answer 1 · answered Jul 19 '13 at 10:34

public User[] GetUsers(int[] activityIds, int[] trainingIds)
{
    return _context.Users
        .Where(u => u.Activities.Any(a => activityIds.Contains(a.Id)) ||
                    u.Training.Any(t => trainingIds.Contains(t.Id)))
        .ToArray();
}

Efficiency with the Linq Contains method where parameter is an array

1 Answers1