score:2

Accepted answer

a general solution for this sort of issue is to use predicatebuilder to dynamically construct the appropriate predicate.

first, build the predicate:

expression<func<order, bool>> predicate = predicatebuilder.true<order>();

if (filter.orderdate != null)
    predicate = predicate.and(o => o.ord_date == filter.orderdate);

if (filter.custid != null)
    predicate = predicate.and(o => o.custid == filter.custid);

...

and then you query becomes:

var filtered = db.orders.where(predicate);

var query = from o in filtered 
            join c in db.customers on c.id equals o.custid
            select new {o,c};      

score:0

maybe change it to this:

from o in db.orders
join c in db.customers on c.id equals o.custid
where o.ord_date == ( filter.orderdate ?? o.ord_date) &&
  c.id == (filter.custid ?? c.id) &&
  o.productid == (filter.productid ?? o.productid)
select new {o,c}

using the ?? operator keeps things a little tidier. it's basically like a coalesce operator for .net

other then that, i'm not sure what else you could change.

score:2

given that the conditions do not depend on the query, you could have the conditionals out of the query and build it gradually:

var query = from o in db.orders
            join c in db.customers on c.id equals o.custid
            select new {o,c};
if(filter.orderdate != null)
{
    query = query.where(x => x.o.ord_date == filter.orderdate);
}
if(filter.custid != null)
{
    query = query.where(x => x.c.id == filter.custid);
}
if(filter.productid != null)
{
    query = query.where(x => x.o.productid == filter.productid);
}

Related Query