LINQ & Many to Many Relationships

Question

So I'm writing a "dynamic" Linq query. I've created an "options" class that holds all of the dynamic options that can be a part of the query. Some of these option properties are List objects, which hold IDs of entities that I want to return that are part of many-to-many relationships in SQL Server. A quick code example and descriptions of the tables might help (seriously pared down for brevity).

Table Cars: Id int PK, Model varchar(50), Year int

Table Colors: Id int PK, Name varchar(50)

Table CarsXColors: CarId int PK, ColorId int PK

public IEnumerable<Car> Search(SearchOptions options)
{
    var query = from car in ctx.Cars select car;

    // This works just fine
    if (options.MaxMileage.HasValue) query = query.Where(x => x.Mileage <= options.Mileage.Value);

    // How do I implement this pseudo code. options.Colors is a List<int>
    if (options.Colors.Count > 0)
    {
        query = query.Where(  -- select cars that are in the List<int> of colors --);
    }


    return query;
}

Answer 1

I see that you already got the answer, but also consider this alternative:

var query = 
    from car in ctx.Cars 
    where options.Colors.Count <= 0 || car.CarsXColors.Any(y => options.Colors.Contains(y.Id))
    select car;

This style is quite common as it generates the same SQL statement throughout so that database engines or LINQ can actually cache the query and/or query plan for faster response.

The database query optimizer will automatically eliminate the WHERE clause if options.Colors is empty, so you are not paying any performance penalties here.