Is there a better way of writing this Linq Query

Example

from O in db.Orders
join C in db.Customers on C.Id equals O.CustID
Where O.ord_date == ( filter.OrderDate != null ? filter.OrderDate : o.ord_date) &&
  c.Id == (filter.CustId != null ? filter.CustId : c.Id) &&
  o.ProductId == ( filter.ProductId != null ? filter.ProductId : o.ProductID)
select new {o,c}

//select new {c.Name, C.JoinDate, O.Value, O.NoofLineItems }

When i trun on the profile it has a lot of case statements as i expect it to have. but i have lot more co开发者_StackOverflow中文版ntrol on the condition what i put in in C# how can i exersise my control on the where condition and only put the where condition when filter is available for it

This will improve my quality of sql which goes to the db.

Kind regards Vinay.

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};      

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);
}

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.