frc frc - 17 days ago 5
C# Question

Append WHERE to query based on a condition

I have a query like so:

if (catId == null || catId == 0)
{
productVM = db.Products
.Include(x => x.Category)
.ToArray()
.Select(x => new ProductVM(x))
.ToList();
}
else
{
productVM = db.Products
.Include(x => x.Category)
.ToArray()
.Where(x => x.CategoryId == catId)
.Select(x => new ProductVM(x))
.ToList();
}


That works, but as you can see the only difference between the 2 queries is
.Where(x => x.CategoryId == catId)
.

Is there a more elegant way to write this?

Answer

You could expand the Where statement to include the test for null or 0 on catId. This might not work if the field catId in the database is nullable or can have a value of 0.

productVM = db.Products
    .Include(x => x.Category)
    .Where(x => catId == null || catId == 0 || x.CategoryId == catId)
    .Select(x => new ProductVM(x))
    .ToList();

Also you should remove the ToArray() as it queries the entire Products table from the database then perform the filtering and projecting in memory on the client.