CODEWITHSUNDEEP
.netvb.netlinqentity-framework
test
test

Reputation: 2639

Appending dynamic where statements in LINQ to Entities but as an OR operator instead of AND

Looking for how to effectively do this in LINQ to Entities:

Dim query = context.Entities.AsQueryable()

For Each key In keys
    Dim key1 = key(0)
    Dim key2 = key(1)
    Dim key3 = key(2)

    ' if record has all three keys of any of the 
    ' keys it should return the record
    query = query.OrWhere(Function(e) e.Key1Column = key1 AndAlso
                                      e.Key2Column = key2 AndAlso
                                      e.Key3Column = key3)
Next

Hoping to get pretty clean SQL generated from this. Something like this:

SELECT * 
FROM Entities
WHERE (Key1Column = @key1_value1 AND 
       Key2Column = @key1_value2 AND 
       Key3Column = @key1_value3) 

      OR

      (Key1Column = @key2_value1 AND 
       Key2Column = @key2_value2 AND 
       Key3Column = @key2_value3)

-- etc...

I know I could do it with a union on each where expression but that won't be very optimal.

Upvotes: 0

Views: 146

Answers (1)

mr100
mr100

Reputation: 4428

I am C# programmer so I will present solution in C# and then try to translate it to VB. In C# it looks like that:

var query = context.Entities.AsQueryable();

ParameterExpression p = Expression.Parameter(typeof(Entity), "entity");
Expression whereExpression = Expression.Constant(false);

foreach(string key in keys)
{
    string key1 = key[0];
    string key2 = key[1];
    string key3 = key[2];
    whereExpression = Expression.Or(whereExpression,
                Expression.And(
                    Expression.And(
                        Expression.Equal(Expression.Property(p, "Key1Column"),
                            Expression.Constant(key1)),
                        Expression.Equal(Expression.Property(p, "Key2Column"),
                            Expression.Constant(key2))),
                    Expression.Equal(Expression.Property(p, "Key3Column"),
                            Expression.Constant(key3))));
}
query = query.Where(Expression.Lambda<Func<Entity, bool>>(whereExpression, p));

In VB it should be something like this:

Dim query = context.Entities.AsQueryable();

Dim p As ParameterExpression = Expression.Parameter(typeof(Entity), "entity");
Dim whereExpression As Expression = Expression.Constant(false);

For Each key in keys
    Dim key1 = key(0);
    Dim key2 = key(1);
    Dim key3 = key(2);
    whereExpression = Expression.Or(whereExpression,
                Expression.And(
                    Expression.And(
                        Expression.Equal(Expression.Property(p, "Key1Column"),
                            Expression.Constant(key1)),
                        Expression.Equal(Expression.Property(p, "Key2Column"),
                            Expression.Constant(key2))),
                    Expression.Equal(Expression.Property(p, "Key3Column"),
                            Expression.Constant(key3))));
Next
query = query.Where(Expression.Lambda(Of Func( Of Entity, bool))(whereExpression, p));

Upvotes: 1

Related Questions