How LINQ many to many with OR search?

Question

Hello. I have the following table structure:

Table Users

ID  | Name
1   | John
2   | Ted
3   | Alice
4   | Barney

and table UserLanguages

ID  | UserID | Language
1   | 1      | 1
2   | 1      | 5
3   | 2      | 2
4   | 2      | 3
5   | 3      | 3
6   | 4      | 4
7   | 4      | 5

I check languages 2,3 and 4 and I want to get users: 2,3,4. I want to get users who have at least something of chosen languages (or 2 or 3 or 4) How get with LINQ?

Answer 1

Something like this should work:

var query = from u in db.Users
            join l in db.UserLanguages on u.ID equals l.UserID
            group l.Language by u into langs
            where langs.Any(l => l == 2 || l == 3 || l == 4)
            select langs.Key;

---

My suggestion of using predicate builder is overly complicated. IEnumerable<T>.Contains() apparently works in LINQ-to-SQL (which I didn't know). This should be much easier.

var languages = new[] { 2, 3, 4 };
var query = from u in db.Users
            join l in db.UserLanguages on u.ID equals l.UserID
            group l.Language by u into langs
            where langs.Any(l => languages.Contains(l))
            select langs.Key;

Answer 2

You can use something like Jon Skeet suggested in your earlier question (http://stackoverflow.com/questions/3870666/how-search-linq-with-many-parametrs-in-one-column)

This should work, but it's not tested:

int[] languages = new[] {2, 3, 4};
var query = dataContext.UserLanguages
    .Where(ul => languages.Contains(ul.Language))
    .Select(ul => ul.User)
    .Distinct();