CODEWITHSUNDEEP
c#entity-framework
frc
frc

Reputation: 588

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?

Upvotes: 3

Views: 922

Answers (4)

DavidG
DavidG

Reputation: 119056

Entity Framework doesn't actually query the database until you materialise the data, for example with ToList() or iterating over the results. So you can build up your query as you go without hitting the database:

var query = db.Products.Include(x => x.Category);

if(catId != null && catId != 0)
{
    //Add a where clause
    query = query.Where(x => x.CategoryId == catId);
}

productVM = query
    .ToList()
    .Select(x => new ProductVM(x));

Note that I removed the ToArray call as that also materialises the data which means each subsequent method on the data is acting on the entire table from the database.

Upvotes: 3

Yves Tkaczyk
Yves Tkaczyk

Reputation: 531

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.

Upvotes: 1

Sampath
Sampath

Reputation: 65940

Another way.

var products = db.Products.Include(x => x.Category).ToArray()

if (catId == null || catId == 0)
{
    productVM = products.Select(x => new ProductVM(x)).ToList();
}
else
{
    productVM = products.Where(x => x.CategoryId == catId)
                        .Select(x => new ProductVM(x)).ToList();
}

Upvotes: 1

T-moty
T-moty

Reputation: 2797

Simply re-assign query:

var query = db.Products;

if (condition)
    query = query.Where(criteria);

var list = query.ToList();

Upvotes: 0

Related Questions