Incrementally building a query

suggest change

Because LINQ uses deferred execution, we can have a query object that doesn’t actually contain the values, but will return the values when evaluated. We can thus dynamically build the query based on our control flow, and evaluate it once we are finished:

IEnumerable<VehicleModel> BuildQuery(int vehicleType, SearchModel search, int start = 1, int count = -1) {
    IEnumerable<VehicleModel> query = _entities.Vehicles
        .Where(x => x.Active && x.Type == vehicleType)
        .Select(x => new VehicleModel {
            Id = v.Id,
            Year = v.Year,
            Class = v.Class,
            Make = v.Make,
            Model = v.Model,
            Cylinders = v.Cylinders ?? 0
        });

We can conditionally apply filters:

if (!search.Years.Contains("all", StringComparer.OrdinalIgnoreCase))
    query = query.Where(v => search.Years.Contains(v.Year));

if (!search.Makes.Contains("all", StringComparer.OrdinalIgnoreCase)) {
    query = query.Where(v => search.Makes.Contains(v.Make));
}

if (!search.Models.Contains("all", StringComparer.OrdinalIgnoreCase)) {
    query = query.Where(v => search.Models.Contains(v.Model));
}

if (!search.Cylinders.Equals("all", StringComparer.OrdinalIgnoreCase)) {
    decimal minCylinders = 0;
    decimal maxCylinders = 0;
    switch (search.Cylinders) {
        case "2-4":
            maxCylinders = 4;
            break;
        case "5-6":
            minCylinders = 5;
            maxCylinders = 6;
            break;
        case "8":
            minCylinders = 8;
            maxCylinders = 8;
            break;
        case "10+":
            minCylinders = 10;
            break;
    }
    if (minCylinders > 0) {
        query = query.Where(v => v.Cylinders >= minCylinders);
    }
    if (maxCylinders > 0) {
        query = query.Where(v => v.Cylinders <= maxCylinders);
    }
}

We can add a sort order to the query based on a condition:

switch (search.SortingColumn.ToLower()) {
        case "make_model":
            query = query.OrderBy(v => v.Make).ThenBy(v => v.Model);
            break;
        case "year":
            query = query.OrderBy(v => v.Year);
            break;
        case "engine_size":
            query = query.OrderBy(v => v.EngineSize).ThenBy(v => v.Cylinders);
            break;
        default:
            query = query.OrderBy(v => v.Year); //The default sorting.
    }

Our query can be defined to start from a given point:

query = query.Skip(start - 1);

and defined to return a specific number of records:

if (count > -1) {
        query = query.Take(count);
    }
    return query;
}

Once we have the query object, we can evaluate the results with a foreach loop, or one of the LINQ methods that returns a set of values, such as ToList or ToArray:

SearchModel sm;

// populate the search model here
// ...

List<VehicleModel> list = BuildQuery(5, sm).ToList();

Found a mistake? Have a question or improvement idea? Let me know.

Table Of Contents