C#•12mo ago

Is it possible to build a custom Expression<Func<T, bool>> by chaining "Or" operations to use in EF?

Hi all, this is my first post here. I want to check if it's possbile to chain Expression.Or operations to be able to use it in EF 6 (Or in a IEnumerable.Where() in general. Let me explain the context: - I have two entities called Orders and Users. Users can create multiple orders, and one Order is related to one single User. - Orders have a "CreatedDate" field (so that it is populated with DateTime.Now in the database) and also "CreatedBy" representing the username that created it. - Users have a field called "DateDisabled" nullable. When it is null, I can bring all his orders, but if it has a value, i will just bring orders dating from before he was disabled: order.DateCreated <= user.DateDisabled. To simplify:

public class OrderSummaryDpid {
    public int Id { get; set; }
    public DateTime CreatedDate { get; set; }
    public string CreatedBy { get; set; }
}

public class User {
   public DateTime? DateDisabled {get; set;}
   public string UserName {get;set;}
}

public class OrderSummaryDpid {
    public int Id { get; set; }
    public DateTime CreatedDate { get; set; }
    public string CreatedBy { get; set; }
}

public class User {
   public DateTime? DateDisabled {get; set;}
   public string UserName {get;set;}
}

a filter to bring orders from a single user would be :

var dateDisabled = user.DateDisabled.HasValue ? userDateDisabled.Value : DateTime.MaxValue; // overkill? yes

Expression<Func<OrderSummaryDpid, bool>> filter = order =>
  order.CreatedDate <= dateDisabled &&
  order.CreateBy == user.UserName;

var userOrders = _repository.Where(filter); // IQueryable<OrderSummaryDpid>

var dateDisabled = user.DateDisabled.HasValue ? userDateDisabled.Value : DateTime.MaxValue; // overkill? yes

Expression<Func<OrderSummaryDpid, bool>> filter = order =>
  order.CreatedDate <= dateDisabled &&
  order.CreateBy == user.UserName;

var userOrders = _repository.Where(filter); // IQueryable<OrderSummaryDpid>

since we can have many users, we would have many filter, one for each user: var manyUsersOrders = _repository.Where(filter1 || filter2 || filter3 || ... || filterN); Here comes the question: since it's not known how many users there are in the array, How can I create such an expression that chain Or operations like that dinamically? I tried to build Expressions and use Expression.Or to join them together, but It returns a BinaryExpression and I don't know exactly how to use it to build the final expression. Any help?

15 Replies

Jimmacle•12mo ago

you can chain multiple .Where calls and they will essentially be combined dunno about or though PepeHmmm

dateDisabled vary by filter or is it the same for every filter? if it's the same, just check if the user ID is in a collection of user IDs

UnmeiOP•12mo ago

it will vary for each user I found a possible solution to this:

UnmeiOP•12mo ago

https://gist.github.com/tncbbthositg/4350639

Gist

Extension method for combining predicates and extension method for ...

Extension method for combining predicates and extension method for or conditions in linq queries. - gist:4350639

UnmeiOP•12mo ago

In this gist, this person combines two expressions of same type by a combination which can be a BinaryExpression.Or for example Could be like expr1.Combine(BinaryExpression.OrElse, expr2); Can be reduced to expr1.Or(expr2) when chaning the implementation a bit

static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> predicate, Expression<Func<T, bool>> withPredicate)
{
    var invocation = Expression.Invoke(withPredicate, predicate.Parameters);
    var combined = Expression.OrElse(predicate.Body, invocation);
    return Expression.Lambda<Func<T, bool>>(combined, predicate.Parameters);
}

static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> predicate, Expression<Func<T, bool>> withPredicate)
{
    var invocation = Expression.Invoke(withPredicate, predicate.Parameters);
    var combined = Expression.OrElse(predicate.Body, invocation);
    return Expression.Lambda<Func<T, bool>>(combined, predicate.Parameters);
}

It Works! So that's the solution:

Expression<Func<OrderSummaryDpid>> usersFilter = order => true == false; // it renders false intentionally;
foreach (var user in users) {
    var dateDisabled = user.DateDisabled.HasValue ? user.DateDisabled.Value : DateTime.MaxValue;

    Expression<Func<OrderSummaryDpid, bool>> singleUserFilter = order =>
        order.CreatedDate <= dateDisabled &&
        order.CreateBy == user.UserName;
  
    usersFilter = usersFilter.Or(singleUserFilter); // applies Expression.OrElse to these two
}

var orders = _ordersRepository.Where(usersFilter); // IQueryable<OrderSummaryDpid>
var ordersQueryForDebug = orders.ToQueryString();

return orders.ToList(); // materialize records

Expression<Func<OrderSummaryDpid>> usersFilter = order => true == false; // it renders false intentionally;
foreach (var user in users) {
    var dateDisabled = user.DateDisabled.HasValue ? user.DateDisabled.Value : DateTime.MaxValue;

    Expression<Func<OrderSummaryDpid, bool>> singleUserFilter = order =>
        order.CreatedDate <= dateDisabled &&
        order.CreateBy == user.UserName;
  
    usersFilter = usersFilter.Or(singleUserFilter); // applies Expression.OrElse to these two
}

var orders = _ordersRepository.Where(usersFilter); // IQueryable<OrderSummaryDpid>
var ordersQueryForDebug = orders.ToQueryString();

return orders.ToList(); // materialize records

ordersQueryForDebug will render SQL Select statement correctly with the "where" clause just as expected. It also creates all the constants needed for the expressions created, like user.UserName and dateDisabled

PixxelKick•12mo ago

Instead of hydrating the users into memory, just join user on order in your where

Orders
    .Where(o => UserIds.Contains(o.User.Id) &&
o.User.DateDisabled == null ||
o.User.DateDisabled >= o.DateCreated)
    .Select(OrderQueryDTO.FromQuery)
    .ToList();

Orders
    .Where(o => UserIds.Contains(o.User.Id) &&
o.User.DateDisabled == null ||
o.User.DateDisabled >= o.DateCreated)
    .Select(OrderQueryDTO.FromQuery)
    .ToList();

Not 100% sure if that's your filter but that's the general idea of how you'd do it

UnmeiOP•12mo ago

Thanks for the help. That would be a viable solution too. I was avoiding to try and use Contains since Orders.CreateBy and User.UserName are not PK/FK correlations, thus it could impact performance somehow. Database in this project is all messed up and I can’t change it now 🥲

PixxelKick•12mo ago

Hydrating into memory will be way worse, as you'll have to then do many individual queries instead of just 1 single query.

UnmeiOP•12mo ago

Not individual queries, but individual “where” clauses for each user. In the tests I made, it would create two variables for each user, their username and they dateDisabled and use them in the where clause. A query filtering by 46 users is executed in roughly 1.2 seconds, which isn’t that bad if compared to the ideal scenario of having the PK/FKs and letting database management figure it out. I will still go back to this and test what happens if I build relations between the tables in EF’s configuration and make the query normally, but multiple where clauses is acceptable since it was a production defect.

PixxelKick•12mo ago

Is the "where" clause logically the same for each user though? It sounds like it should be in which case you should be able to just write that as 1 single query that fetches all of the data in a single shot largely speaking the only time you cant easily do that is when recursion is involved and you run into the turing problem, aside from that though you can most often get everything done in 1 big query which usually is many orders of magnitude more performant than many small queries

UnmeiOP•12mo ago

where clauses are different for each user and again, it's many where clauses, not many small queries/selects/sub-selects in the end, it generate one single query

PixxelKick•12mo ago

Can you show an example of the where clauses that are different for 2 users?

UnmeiOP•12mo ago

declare username1 varchar(20) = 'foo_bar';
declare dateDisabled1 datetime = '9999-12-31-23:59:59.000';
declare username2 varchar(20) = 'john_doe';
declare dateDisabled2 datetime = '2023-10-12-13:22:37.784';

select * from Orders o
where o.Status = 'CAN' and
 (
   (o.createdUser = username1 and o.dateDisabled = dateDisabled1) or
   (o.createdUser = username2 and o.dateDisabled = dateDisabled2) or
 )
order by o.Id;

declare username1 varchar(20) = 'foo_bar';
declare dateDisabled1 datetime = '9999-12-31-23:59:59.000';
declare username2 varchar(20) = 'john_doe';
declare dateDisabled2 datetime = '2023-10-12-13:22:37.784';

select * from Orders o
where o.Status = 'CAN' and
 (
   (o.createdUser = username1 and o.dateDisabled = dateDisabled1) or
   (o.createdUser = username2 and o.dateDisabled = dateDisabled2) or
 )
order by o.Id;

it would be something like that. EF generates the query when using ToQueryString() but I don't have the exact query right here. It is almost the same as this one, only differences are what it bring in the select and order by statements

PixxelKick•12mo ago

What would be the source of dateDisabled1 and dateDisabled2?

UnmeiOP•12mo ago

it' the value of the variable of the line 3 of this snippet here. One for each user

PixxelKick•12mo ago

I'm looking for the actual higher level use case here Ideally in C#

Gaming

Programming

Is it possible to build a custom Expression<Func<T, bool>> by chaining "Or" operations to use in EF?