Dynamic Where Clause (Or) in Linq to Entities

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 where clause (OR) in Linq to Entities

With LINQKit's PredicateBuilder you can build predicates dynamically.

var query = from u in context.Users select u;
var pred = Predicate.False<User>();

if (type.HasFlag(IdentifierType.Username))
    pred = pred.Or(u => u.Username == identifier);

if (type.HasFlag(IdentifierType.Windows))
    pred = pred.Or((u => u.WindowsUsername == identifier);

return query.Where(pred.Expand()).FirstOrDefault();
// or return query.AsExpandable().Where(pred).FirstOrDefault();

This is what the Expand is for:

Entity Framework's query processing pipeline cannot handle invocation expressions, which is why you need to call AsExpandable on the first object in the query. By calling AsExpandable, you activate LINQKit's expression visitor class which substitutes invocation expressions with simpler constructs that Entity Framework can understand.

Or: without it an expression is Invoked, which causes an exception in EF:

The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.

Later addition:

There is an alternative predicate builder that does the same but without Expand: http://petemontgomery.wordpress.com/2011/02/10/a-universal-predicatebuilder/

Dynamic Where Linq to Entities

Here is how you can rewrite your query using Dynamic Linq:

var propName = "CopyrightDate";
var propValue = "2006";
Model.Titles.Where(string.Format("{0}=@0", propName), propValue);

string.Format("{0}=@0", propName) produces the query string for the Where clause, which would be "CopyrightDate=@0" in this case. @0 specifies the parameter for the query, which becomes propValue.

Can I dynamically build a where clause in LINQ to Entities?

You can combine the two LINQ syntaxes:

private IQueryable<IUser> BuildQuery(IQueryable<IUser> users, string userName)
{
    users = users.Where(u => u.UserName == userName);
    return users;
}

Then when you call it:

var query = from u in this.BuildQuery(this.adapter.Users, userName)
            select u;

Hopefully this will point you in the right direction!

Dynamically construct where clause with Func T, string lambda - linq to entities

So, you're trying to merge your prototype item => item == criteriaItem. With a passed in string property expression, like (r) => r.SomeProperty.Name to create (r) => r.SomeProperty.Name == criteriaItem.

    Expression<Func<string, bool>> prototype = item => item == criteriaItem;
    var predicate = Expression.Lambda<Func<T, bool>>(
        ReplacingExpressionVisitor.Replace(
            prototype.Parameters[0],
            getItemString.Body,
            prototype.Body),
        getItemString.Parameters[0]);

And I think you're trying to do it this way so that criteriaItem is bound to an sql parameter, rather than being inlined as a string constant. But your question was a little hard to follow.

Dynamic joins in Linq according to the Select & Where Clause

If you have bad performance with the joins you just have to make sure that the condition is false when you don't need the information of the table.

For example, if you don't need users information you can do something like this:

DECLARE  @UsersNeeded bit=0;
SELECT * FROM Accounts a 
LEFT JOIN Addresses ad on ad.Account = a.Id
LEFT JOIN Users u ON 
@UsersNeeded=1 and u.Id = a.AccountManager
WHERE ad.Country = 'NL'

Linq will be something like this:

var usersNeeded = false;
    from account in _dbContext.Accounts

    join address in _dbContext.Addresses on account.ID equals address.Account into address_
    from address__ in address_.DefaultIfEmpty()

    join user in _dbContext.Users on 
    usersNeeded equals false &&
    account.AccountManager equals user.Id into user_
    from user__ in user_.DefaultIfEmpty();

You can make a list with the tables that you need (or you don't need) and add an extra condition on the join clause.

linq dynamic where clause with or condition on a INT column

You can use cs=>idList.Contains(cs.pkID)

var idList = new List<int>(){123, 456};   
var result = query.Where(cs => idList.Contains(cs.pkID));

This way you don't need any loop and it is equivalent to:

SELECT somecolumns FROM sometable WHERE pkId in (123, 456)

In your case, using Contains, makes more sense than creating dynamic where clause with OR.

Linq to Entities Dynamic Where Clause

You expression tree is equivalent to:

.Where(x => x.Column.Contains(likeValue"))

How would you like to make Contains/LIKE comparison with integers?!

For integers, you should use Expression.Equal instead of Contains method call:

Dim Param = Expression.Parameter(source.ElementType)
Dim columnProperty = Expression.PropertyOrField(Param, Column.Name)

Dim equalValue = Integer.Parse(value)

Dim equalValueExpression = Expression.Constant(equalValue, GetType(Integer))
Dim equalExpression = Expression.Equal(columnProperty, equalValueExpression )
Dim where = Expression.[Call](GetType(Queryable), "Where", New Type() {source.ElementType}, source.Expression, Expression.Lambda(equalExpression , Param))

I haven't tested that, but should work.

---

Related Topics