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

Keep in mind that you aren't actually *writing* [T]SQL here... you are writing C# — Marc Gravell, Jun 16 '09 at 13:27

BFree · Accepted Answer · 2009-06-16T13:45:45.903

23

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

edited Jun 16 '09 at 13:45

answered Jun 16 '09 at 13:25

BFree

102,548
21
159
201

Ok fine. Can i append in the where clause. e.g. if (title != string.Empty) opportunites = opportunites.Where(opp => opp.Title.StartsWith(title)); if (name != string.Empty) opportunites = ..... Append new name in previous where ????? – Waheed Jun 16 '09 at 13:37
@Waheed absolutely. In fact, the example BFree is showing you is appending the Where clause onto the original IQueryable. Subsequent Where clauses will have an appending affect as long as you're doing something like opportunities = opportunities.Where(...) – Joseph Jun 16 '09 at 13:41
Way too complex. :-) See my solution that's posted below. – Wim ten Brink Jun 16 '09 at 14:55
This seems to work when the Where() contains a condition that uses the "oop" table, but when I use a condition based on a field in the "org" table, it won't compile. It says that the opp table doesn't contain such and such field. Any idea how to work around that? – Emmanuel Oct 28 '16 at 16:38

Joseph · Answer 2 · 2009-06-16T13:33:22.310

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

score 1 · Answer 3 · answered Jun 16 '09 at 13:27

1

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)

answered Jun 16 '09 at 13:27

hugoware

35,731
24
60
70

You'd need to check it, but that may result in very poorly performing TSQL (if it takes the test all the way to the database) – Marc Gravell Jun 16 '09 at 13:29
Good point - I figured since it delayed execution it might not bother. – hugoware Jun 16 '09 at 13:33

score 1 · Answer 4 · answered Jun 16 '09 at 13:30

1

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

answered Jun 16 '09 at 13:30

Daniel Brückner

59,031
16
99
143

score 1 · Answer 5 · answered Jun 16 '09 at 13:34

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

score 1 · Answer 6 · edited May 23 '17 at 12:34

1

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?

edited May 23 '17 at 12:34

Community

1
1

answered Jun 16 '09 at 13:42

Kev

118,037
53
300
385

score 1 · Answer 7 · answered Nov 20 '13 at 07:33

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

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

7 Answers7

Linked