frc frc - 1 year ago 66
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download