Add the Where Clause Dynamically in Entity Framework

Add the where clause dynamically in Entity Framework

You can use a beautiful thing called PredicateBuilder. Use it like this

var pr = PredicateBuilder.False<User>();
foreach (var name in names)
{
    pr = pr.Or(x => x.Name == name && x.Username == name);
}
return query.AsExpandable().Where(pr);

EF Core & LINQ - How to build a dynamic Where clause using a string variable at run time?

You just add the .Where() calls to the end of your query, before materializing:

query = _context.Logs.Select(s => new ..);

if (EventCategory != null) query = query.Where(e => e.EventCategory == EventCategory);
if (LogLevel != null) query = query.Where(e => e.LogLevel == LogLevel);

var items = query.ToList();

C# Entity Framework - How to pass a value in the Where clause dynamically

Thank you so much for your responses. I found the following solution to be the most optimal:

I created a few static helper methods that each returned a Lambda Expression. For example, for ".Contains" I added the following method:

private static Expression<Func<T, bool>> GetContainsExpression<T>(string propertyName, string propertyValue)
    {
        var parameterExp = Expression.Parameter(typeof(T), "type");
        var propertyExp = Expression.Property(parameterExp, propertyName);
        MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
        var someValue = Expression.Constant(propertyValue, typeof(string));
        var containsMethodExp = Expression.Call(propertyExp, method, someValue);

        return Expression.Lambda<Func<T, bool>>(containsMethodExp, parameterExp);
    }

For .StartsWith the following method (similarly):

private static Expression<Func<T, bool>> GetStartsWithExpression<T>(string propertyName, string propertyValue)
    {
        var parameterExp = Expression.Parameter(typeof(T), "type");
        var propertyExp = Expression.Property(parameterExp, propertyName);
        MethodInfo method = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
        var someValue = Expression.Constant(propertyValue, typeof(string));
        var containsMethodExp = Expression.Call(propertyExp, method, someValue);

        return Expression.Lambda<Func<T, bool>>(containsMethodExp, parameterExp);
    }

And the same goes to ".Equals" and ".EndsWith".

Later on, in my click method above (in my question), I added the following lambda expressions:
(This one is for .Contains)

if (valCombo.Text.Equals("contains"))
                        {
                            dgvFiltered.DataSource = af.Accounts.Where(GetContainsExpression<Account>(fieldNameCombo.Text, txt1.Text))
                                .Where(x => x.A_Platform.Equals(platformCombo.Text) && x.A_Type.Equals(typeCombo.Text)).ToList();
                        }

This one is for .StartsWith:

if (valCombo.Text.Equals("starts with"))
                        {
                            dgvFiltered.DataSource = af.Accounts.Where(GetStartsWithExpression<Account>(fieldNameCombo.Text, txt1.Text))
                                .Where(x => x.A_Platform.Equals(platformCombo.Text) && x.A_Type.Equals(typeCombo.Text)).ToList();
                        }

And so on. Yes, it does make both the .Wheres in each code sample above an && expression. So, to tackle the || case where one Where is true and the other is not, I added the following if statement above them:

if (fieldNameCombo.Text == "")
                    {
                        dgvFiltered.DataSource = af.Accounts.Where(x => x.A_Platform.Equals(platformCombo.Text) && x.A_Type.Equals(typeCombo.Text)).ToList();
                    }

Hope this provides clarity on this issue. Let me know if you have any questions.

Entity Framework 6: Dynamically build search clause against DB

I assume you are able to build a sub-condition from each line in your GUI. For example, the first line would be similar to

string userInputName = "john";
Expression<Func<Person, bool>> condition1 = person => person.Name.Contains(userInputName);

And can be used as

var selection = db.Persons.Where(condition1).ToList();

For multiple sub-conditions, and is just a concatenation of Where conditions:

var conditions = new List<Expression<Func<Person, bool>>>() { condition1, condition2, ... };

IQueryable<Person> query = db.Persons;
foreach (var condition in conditions)
{
    query = query.Where(condition);
}
var selection = query.ToList();

The little trickier part comes with the or conditions. Suppose you have normalized your conditions into groups of alternative and conditions (disjunctive normal form), then you get multiple groups of valid results. For the sake of simplicity, I keep it at 2 groups here, but it can be generalized the same way as the and conditions:

The or can be represented by the Union operation on sub-queries.

var conditions1 = new List<Expression<Func<Person, bool>>>() { condition1, condition2, ... };
IQueryable<Person> query1 = // see above construction logic for 'and' conditions
var conditions2 = new List<Expression<Func<Person, bool>>>() { condition5, condition6, ... };
IQueryable<Person> query2 = // see above construction logic for 'and' conditions

IQueryable<Person> query = query1.Union(query2);
var selection = query.ToList();

A few last words: consider using some established filter/search frameworks instead. This approach is probably neither the prettiest nor the fastest you can find.

---

As requested, a small example with some in-memory data. Note this is not 100% equivalent to Linq-to-entities. For example, string comparison will deal with upper-lower case differently and maybe not every kind of condition is allowed for SQL.

public enum TypeOfContact
{
    Unknown,
    Email
}
public class Person
{
    public string Name { get; set; }

    public DateTime Birth { get; set; }

    public TypeOfContact ContactType { get; set; }

    public string ContactValue { get; set; }
}

public class Program
{
    static void Main(string[] args)
    {
        // test data to simulate a database table
        var Persons = new List<Person>
        {
            // + All conditions met
            new Person { Name = "john doe", Birth = new DateTime(2011, 1, 1), ContactType = TypeOfContact.Email, ContactValue = "john.doe@email.com" },
            // - Not in result
            new Person { Name = "danny doe", Birth = new DateTime(2012, 1, 1), ContactType = TypeOfContact.Email, ContactValue = "johndoe@hotmail.com" },
            // + Name contains john
            new Person { Name = "john doe", Birth = new DateTime(2013, 1, 1), ContactType = TypeOfContact.Unknown, ContactValue = "" },
            // + Birth, ContactType and ContactValue correct
            new Person { Name = "justin", Birth = new DateTime(2014, 1, 1), ContactType = TypeOfContact.Email, ContactValue = "slenderman@email.com" },
            // - Not in result because Name and Birth are wrong
            new Person { Name = "jonny", Birth = new DateTime(1979, 1, 1), ContactType = TypeOfContact.Email, ContactValue = "you-know-who@email.com" },
            // - Not in result
            new Person { Name = "jenny doe", Birth = new DateTime(2016, 1, 1), ContactType = TypeOfContact.Unknown, ContactValue = "" },
        }.AsQueryable();

        // single-line-conditions
        Expression<Func<Person, bool>> c1 = p => p.Name.Contains("john");
        Expression<Func<Person, bool>> c2 = p => p.Birth.Date >= new DateTime(1980, 1, 1);
        Expression<Func<Person, bool>> c3 = p => p.ContactType == TypeOfContact.Email;
        Expression<Func<Person, bool>> c4 = p => p.ContactValue.EndsWith("@email.com");

        // DNF groups: outer list = or; inner list = and
        // c1 or (c2 and c3 and c4)
        var conditionList = new List<List<Expression<Func<Person, bool>>>>
        {
            new List<Expression<Func<Person, bool>>>
            {
                c1,
            },
            new List<Expression<Func<Person, bool>>>
            {
                c2,
                c3,
                c4,
            },
        };

        var andSubResults = new List<IQueryable<Person>>();
        foreach (var andQueries in conditionList)
        {
            var subQuery = Persons;
            foreach (var andQuery in andQueries)
            {
                subQuery = subQuery.Where(andQuery);
            }
            andSubResults.Add(subQuery);
        }
        var query = andSubResults.FirstOrDefault();
        foreach (var subResult in andSubResults.Skip(1))
        {
            query = query.Union(subResult);
        }
        var selection = query.ToList();
        // just check the result in debugger
    }
}

Dynamic where clause in Linq to Entities

Linq's DeferredExecution to rescue. Linq query is not executed unless the data is requested from it.

var prod = from p in ctxt.products.expand("items\details")
        select p;

if (p1 != null)
{
    prod = prod.Where(p => p.x == p1);
}

if (p2 != null)
{
    prod = prod.Where(p => p.xx == p2);
}

// Execute the query

var prodResult = prod.ToList();

Dynamic filter for where clause in Entity Framework 6 (without LINQKit)

The (c) parameter of your both expressions are not the same, see the answer to this SO question.

You can get the expressions to use the same type parameter by using the Expression library.

First, create the type parameter:

var typeParameter = Expression.Parameter(typeof(Client), "c");

...then create the equal expressions by using the declared type parameter:

var nameProperty = Expression.Property(typeParameter, nameof(Client.Name));
var nameExpression = Expression.Equal(nameProperty, Expression.Constant(someName));

and:

var surnameProperty = Expression.Property(typeParameter, nameof(Client.Surname));
var surnameExpression = Expression.Equal(surnameProperty, Expression.Constant(someSurname));

You can then use the type parameter to create your filter and execute your query:

var expr = Expression.And(nameExpression, surnameExpression);
var dynamicFilter = Expression.Lambda<Func<Client, bool>>(expr, typeParameter);
var filteredClientsDynamicFilter = context.Clients.Where(dynamicFilter).ToList();

Entity Framework Dynamic Where Clause from Listobject

You need to translate your WhereClause object into Expression, then you can use it as where query. Here an example:

Type yourType = typeof(YourGeneric);
ParameterExpression pe = Expression.Parameter(yourType , "x");
Expression left = Expression.Property(pe, yourType.GetProperty(whereClause.ColumnName));
Expression right = Expression.Constant(whereClause.Value, typeof(int));
Expression result = Expression.Equal(left, right);

This is an example to compare an int property. You need some if (or switch) to understand property type and what type of comparation you need to do.

if (whereClause.Operator == "Greater Than") {
    result = Expression.GreaterThan(left, right);
}

When you finished use Expression in this way:

context.Set<YourGeneric>().Where(result);

I reccomend to use generics (if possible) to simplify code and work.
You can also concatenate more expressions:

Expression result4 = Expression.AndAlso(result1, result2);
Expression finalRes = Expression.Or(result3, result4);

---

Related Topics