Idan Shechter Idan Shechter - 4 months ago 14
SQL Question

Convert a Llinq query from All matches to OR (at least one tag needs to match)

This query returns door IDs which the the door_id has all its tags matching.

List<decimal> matchingDoors db.tags
.Where(x => x.user_id == userId && (null == SystemId|| x.syid == SystemId))
.GroupBy(x => x.door_id)
.Where(x => tags.All(y =>
x.Any(z => z.name == y)))
.Select(x => x.Key).ToList<decimal>();


How can I change the query to return results that match at least one tag ('OR')?

Answer

You should use Contains() method on tags collection. Here you are:

List<decimal> matchingDoors  db.tags
    .Where(x => x.user_id == userId && (null == SystemId|| x.syid == SystemId)) 
    .GroupBy(x => x.door_id)        
    .Where(x => x.Any(y => tags.Contains(y))) //this is where magic
    .Select(x => x.Key).ToList<decimal>();