CODEWITHSUNDEEP
c#linqlinq-to-sql
Waheed
Waheed

Reputation: 10206

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

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

Upvotes: 13

Views: 20416

Answers (7)

Bill Berlington
Bill Berlington

Reputation: 2414

I was searching for creating a dynamic where clause in LINQ and came across a very beautifull solution on the web which uses ExpressionBuilder in C#.

I am posting it here since none of the above solution uses this approach. It helped me. Hope it helps you too http://www.codeproject.com/Tips/582450/Build-Where-Clause-Dynamically-in-Linq

Upvotes: 1

BFree
BFree

Reputation: 103740

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

Upvotes: 23

Kev
Kev

Reputation: 119806

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?

Upvotes: 1

Stilgar
Stilgar

Reputation: 23561

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

Upvotes: 1

Joseph
Joseph

Reputation: 25513

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

Upvotes: 5

Daniel Brückner
Daniel Brückner

Reputation: 59645

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

Upvotes: 1

hugoware
hugoware

Reputation: 36397

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)

Upvotes: 1

Related Questions