How do I implement a dynamic 'where' clause in LINQ?

Question

I want to have a dynamic where condition.

In the following example:

var opportunites =  from opp in oppDC.Opportunities
                    join org in oppDC.Organizations 
                        on opp.OrganizationID equals org.OrgnizationID
                    where opp.Title.StartsWith(title)
                    select new
                    {
                        opp.OpportunityID,
                        opp.Title,
                        opp.PostedBy,
                        opp.Address1,
                        opp.CreatedDate,
                        org.OrganizationName
                    };

Some times I have Title and sometimes I don't. And also I want to add date in where clause dynamically.

For example, like this SQL:

string whereClause;
string SQL = whereClause == string.Empty ? 
     "Select * from someTable" : "Select * from someTable" + whereclause

Answer 1

You can rewrite it like this:

 var opportunites =  from opp in oppDC.Opportunities
                            join org in oppDC.Organizations on opp.OrganizationID equals org.OrgnizationID
                            select new
                            {
                                opp.OpportunityID,
                                opp.Title,
                                opp.PostedBy,
                                opp.Address1,
                                opp.CreatedDate,
                                org.OrganizationName
                            };

if(condition)
{
   opportunites  = opportunites.Where(opp => opp.Title.StartsWith(title));
}

EDIT: To answer your question in the comments, yes, you can keep appending to the original Queryable. Remember, this is all lazily executed, so at this point all it's doing it building up the IQueryable so you can keep chaining them together as needed:

if(!String.IsNullOrEmpty(title))
{
   opportunites  = opportunites.Where(.....);
}

if(!String.IsNullOrEmpty(name))
{
   opportunites  = opportunites.Where(.....);
}

Answer 2

You can dynamically add a where clause to your IQueryable expression like this:

var finalQuery = opportunities.Where( x => x.Title == title );

and for the date similarly.

However, you will have to wait to create your anonymous type until after you've finished dynamically added your where clauses if your anonymous type doesn't contain the fields you want to query for in your where clause.

So you might have something that looks like this:

var opportunities =  from opp in oppDC.Opportunities
                    join org in oppDC.Organizations on 
                    opp.OrganizationID equals org.OrgnizationID
                    select opp                            

if(!String.IsNullOrEmpty(title))
{
   opportunities = opportunities.Where(opp => opp.Title == title);
}

//do the same thing for the date

opportunities = from opp in opportunities
                select new
                        {
                            opp.OpportunityID,
                            opp.Title,
                            opp.PostedBy,
                            opp.Address1,
                            opp.CreatedDate,
                            org.OrganizationName
                        };

Answer 3

The WHERE clause could be done something like

//...
where string.IsNullOrEmpty(title) ? true : opp.Title.StartsWith(title)
//...

Dynamically returning records I don't think is possible in LINQ since it needs to be able to create a consistent AnonymousType (in the background)

Answer 4

Because queries are composable, you can just build the query in steps.

var query = table.Selec(row => row.Foo);

if (someCondition)
{
    query = query.Where(item => anotherCondition(item));
}

Answer 5

If you know in advance all possible where queries like in the SQL example you have given you can write the query like this

from item in Items
where param == null ? true : ni.Prop == param
select item;

if you don't know all possible where clauses in advance you can add where dymically for example like this:

query = query.Where(item => item.ID != param);

Answer 6

The following questions and answers address this quite well:

Dynamic where clause in LINQ - with column names available at runtime
Is there a pattern using Linq to dynamically create a filter?

Answer 7

Use this:

bool DontUseTitles = true; // (Or set to false...    
var opportunites =  from opp in oppDC.Opportunities
                    join org in oppDC.Organizations 
                        on opp.OrganizationID equals org.OrgnizationID
                    where (DontUseTitles | opp.Title.StartsWith(title))
                    select new
                    {
                        opp.OpportunityID,
                        opp.Title,
                        opp.PostedBy,
                        opp.Address1,
                        opp.CreatedDate,
                        org.OrganizationName
                    };

Why it works? If DontUseTitles is true, it selects all because "(DontUseTitles | opp.Title.StartsWith(title))" evaluates to true. Otherwise, it uses the second condition and just returns a subset.

Why does everyone always make things more complex than they need to be? :-)