Dynamic Where Clause in Linq

Dynamic WHERE clause in LINQ

_{(source: scottgu.com)}

You need something like this? Use the Linq Dynamic Query Library (download includes examples).

Check out ScottGu's blog for more examples.

Dynamic where condition in LINQ

So, if flag is false you need all Jhoms, and if flag is true you need only the Jhoms in the IT department

This condition

!flag || (e.Field<string>("EmployeeDepartment") == "IT"

satisfies that criterion (it's always true if flag is false, etc..), so the query will become:

from e in employee    
where e.Field<string>("EmployeeName") == "Jhom"
  && (!flag || (e.Field<string>("EmployeeDepartment") == "IT")
select e.Field<string>("EmployeeID") 

also, this e.Field<string>("EmployeeID") business, smells like softcoding, might take a look into that. I guess

from e in employee    
where e.EmployeeName == "Jhom"
  && (!flag || (e.EmployeeDepartment == "IT")
select e.EmployeeID

would be more compact and less prone to typing errors.

---

EDIT: This answer works for this particular scenario. If you have lots of this kinds of queries, by all means investingate the patterns proposed in the other answers.

How to dynamically add or remove where clause in LINQ

Try condition:

where id == 0 || m.id == id

in case when id == 0, whole expression evaluates to true, otherwise it is false and second condition m.id == id would be checked.

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

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

LINQ with dynamic where clause in EFCore

I've not tested this but I believe you'd need to use expressions to achieve this. The following will build an expression to use as the predicate in the Where method:

    public static IQueryable<T> FilterBy<T>(
        this IQueryable<T> query, 
        string searchValue,
        Expression<Func<T, string>> memberExpression)
    {
        if (string.IsNullOrWhiteSpace(searchValue))
            return query;

        // must be a lambda expression
        LambdaExpression lambdaExpression = memberExpression as LambdaExpression;
        if (lambdaExpression == null)
            throw new ArgumentException($"Expression '{memberExpression}' is not a lambda expression.");

        // get the member
        Func<ParameterExpression, Expression> sourceExpression = source => Expression.Invoke(lambdaExpression, source);
        ParameterExpression sourceParameter = Expression.Parameter(typeof(T), "source");
        Expression sourceMember = sourceExpression(sourceParameter);

        // expression for the search value
        ConstantExpression valueExpression = Expression.Constant(searchValue);

        // expression to call the Contains method
        MethodInfo containsMethod = GetContainsMethod();
        MethodCallExpression callExpression = Expression.Call(null, containsMethod, sourceMember, valueExpression);

        // predicate expression
        Expression<Func<T, bool>> predicate = Expression.Lambda<Func<T, bool>>(callExpression, sourceParameter);

        return query.Where(predicate);
    }

    private static MethodInfo GetContainsMethod()
    {
        // get method
        MethodInfo genericContainsMethod = typeof(Queryable)
            .GetMethods(BindingFlags.Static | BindingFlags.Public)
            .First(m => m.Name == "Contains"
                     && m.IsGenericMethod
                     && m.GetParameters().Count() == 2);

        // apply generic types
        MethodInfo containsMethod = genericContainsMethod
            .MakeGenericMethod(new Type[] { typeof(string) });

        return containsMethod;
    }

C# DynamicLinq where clause with Any()

It's possible as soon as you follow the Expression Language rules.

For instance, string literals must be enclosed in double quotes:

query = db.Customers.Where("Categories.Any(Code == \"Retail\")");

System.Linq.Dynamic - Can I use IN clause in WHERE statement

From How to use “contains” or “like” in a dynamic linq query?

//edit: this is probably broken, see below
ids = new int[] {1,2,3,4};
dataContext.Table.Where("id.Contains(@0)", ids);

Aside: It is good practice to use placeholders in dynamic linq expressions. Otherwise you may open yourself to linq injection attacks (Is Injection Possible through Dynamic LINQ?)

---

EDIT:

actually I think I messed this up.
Unfortunately I cannot test this at the moment.
But I think the correct syntax in this case should be dataContext.Table.Where("@0.Contains(id)",ids);, not the other way around, and that version does not work out-of-the-box.

See here for a way to add this functionality to dynamic link. You need to modify the library for this.

---

Related Topics