Is there a way to simplify these linq statements using an .Include()?-CodePudding

Currently I am doing a keyword search on the Plates table (Name column) but also have a Search (searching on SearchTerm column) table which contains Plat Id's that I also want to search and return the corresponding platforms.

The code below works but I'd like to simplify the logic using an .Include statement if possible although I'm not quite sure how. Any help would be greatly appreciated.

            if (!string.IsNullOrEmpty(request.Keyword))
            {
                var searchTermPlateIds = await _db.Search
                    .Where(x=> x.SearchTerm.ToLower().Contains(request.Keyword.Trim().ToLower()))
                    .Select(x => x.PlatformId)
                    .ToListAsync(ct);
                
                var plateFromPlateIds = await _db.Plate
                    .OrderBy(x => x.Name)
                    .Where(x => searchTermPlateIds.Contains(x.Id) && x.Status != PlateStatus.Disabled)
                    .ToListAsync(ct);
                
                plates = await _db.Plates
                    .OrderBy(x => x.Name)
                    .Where(x => !string.IsNullOrEmpty(request.Keyword.Trim()) && x.Name.ToLower().Contains(request.Keyword.Trim().ToLower()) && x.Status != PlateStatus.Disabled)
                    .ToListAsync(ct);

                plates = plates.Union(platesFromPlateIds).ToList();
            }

CodePudding user response：

Remember simple thing, Include ONLY for loading related data, not for filtering.

What we can do here - optimize query, to make only one request to database, instead of three.

var query = _db.Plates
    .Where(x => x.Status != PlateStatus.Disabled);

if (!string.IsNullOrEmpty(request.Keyword))
{
    // do not materialize Ids
    var searchTermPlateIds = _db.Search
        .Where(x => x.SearchTerm.ToLower().Contains(request.Keyword.Trim().ToLower()))
        .Select(x => x.PlatformId);
    
    // queryable will be combined into one query
    query = query
        .Where(x => searchTermPlateIds.Contains(x.Id);
}

// final materialization, here you can add Includes if needed. 
var plates = await query
    .OrderBy(x => x.Name)
    .ToListAsync(ct);