CODEWITHSUNDEEP
entity-frameworklinq
user3607582
user3607582

Reputation: 79

How to Select By Value or Select All

How to select either by passed in value or all records if value passed is empty in EF Linq?

public Total GetTotal(string cid="")
{
      var res = from orders in db.Orders
      where orders.Date == date
      && orders.CustId.Contains(cid)
      group orders by new { orders.Date } into g
      select new Total
      {
          Orders = g.Count(),
          Amount = g.Sum(s => s.Amount)
      };
      return res.FirstOrDefault();
}

Upvotes: 0

Views: 677

Answers (2)

Felix
Felix

Reputation: 10078

There are many ways to do that. For me this is usually the simplest:

var resQry = db.Order.Where(o => o.Date == date);
if (cid != "") {
    resQry = resQry.Where(o => o.CustId.Contains(cid));
}
var res = resQry.GroupBy(o => o.Date)
    .Select(g => new Total {
         Orders = g.Count(),
         Amount = g.Sum(s => s.Amount)
     });
return res.FirstOrDefault();

Use LINQ functions; so you can chain two Where() clauses if necessary

Upvotes: 2

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131237

It's very easy to construct a LINQ query from parts, or omit AND conditions, if you use the fluent form. You can add or omit parts as necessary. In the fluent form, every call to Where, GroupBy, Select returns a new query.

What you wrote can be rewritten as :

var res=db.Orders
          .Where(orders=> orders.Date == date && orders.CustId.Contains(cid))
          .GroupBy(orders=>new { orders.Date })
          .Select(g=>new Total
                      {
                          Orders = g.Count(),
                          Amount = g.Sum(s => s.Amount)
                      })
          .FirstOrDefault();

The Where clause is equivalent to two chained Where calls:

.Where(orders=> orders.Date == date)
.Where(orders=>orders.CustId.Contains(cid))

In that form, you can check the parameter value and omit one of the calls if it's empty:

var query=db.Orders.Where(orders=> orders.Date == date);
if(!String.IsNullOrWhitespace(cid))
{
    query=query.Where(orders=>orders.CustId.Contains(cid));
}
var res=query.GroupBy(orders=>new { orders.Date })
             .Select(g=>new Total
                      {
                          Orders = g.Count(),
                          Amount = g.Sum(s => s.Amount)
                      })
             .FirstOrDefault();

This technique can be used to build a query dynamically (up to a point(, by using different GroupBy or Select calls as needed.

Upvotes: 1

Related Questions