C#•3y 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()

IEnumerable.Where()

IEnumerable.Where()

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
```
```
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
```
order.DateCreated <= user.DateDisabled
```
order.DateCreated <= user.DateDisabled
```
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;}
}

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>

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);

var manyUsersOrders = _repository.Where(filter1 || filter2 || filter3 || ... || filterN);

var manyUsersOrders = _repository.Where(filter1 || filter2 || filter3 || ... || filterN);

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
Or
Or
Or operations like that dinamically?

I tried to build Expressions and use

Expression.Or

Expression.Or

Expression.Or

Expression.Or to join them together, but It returns a

BinaryExpression

BinaryExpression

BinaryExpression

BinaryExpression and I don't know exactly how to use it to build the final expression. Any help?

UUnmei Hi all, this is my first post here. I want to check if it's possbile to chain Ex...

Jimmacle•12/19/23, 9:28 PM

you can chain multiple .Where.Where calls and they will essentially be combined

Jimmacle•12/19/23, 9:28 PM

dunno about or though PepeHmmm

Jimmacle•12/19/23, 9:29 PM

does dateDisableddateDisabled vary by filter or is it the same for every filter?

Jimmacle•12/19/23, 9:30 PM

if it's the same, just check if the user ID is in a collection of user IDs

JJimmacle does `dateDisabled` vary by filter or is it the same for every filter?

UnmeiOP•12/19/23, 9:30 PM

it will vary for each user

UnmeiOP•12/19/23, 9:54 PM

I found a possible solution to this:

UnmeiOP•12/19/23, 9:54 PM

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•12/19/23, 9:55 PM

In this gist, this person combines two expressions of same type by a combinationcombination which can be a BinaryExpression.OrBinaryExpression.Or for example

UnmeiOP•12/19/23, 9:58 PM

Could be like expr1.Combine(BinaryExpression.OrElse, expr2)expr1.Combine(BinaryExpression.OrElse, expr2);
Can be reduced to expr1.Or(expr2)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);
}

UnmeiOP•12/19/23, 10:44 PM

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

UnmeiOP•12/19/23, 10:47 PM

ordersQueryForDebugordersQueryForDebug 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.UserNameuser.UserName and dateDisableddateDisabled

PixxelKick•12/20/23, 3:20 AM

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

PixxelKick•12/20/23, 3:22 AM

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

PPixxelKick ``` Orders .Where(o => UserIds.Contains(o.User.Id) && o.User.DateDisabled ==...

UnmeiOP•12/21/23, 11:55 AM

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•12/21/23, 1:47 PM

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

UnmeiOP•12/21/23, 3:54 PM

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.

UnmeiOP•12/21/23, 3:57 PM

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•12/21/23, 4:12 PM

Is the "where" clause logically the same for each user though? It sounds like it should be

PixxelKick•12/21/23, 4:12 PM

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

PixxelKick•12/21/23, 4:13 PM

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•12/22/23, 8:05 PM

where clauses are different for each user

UnmeiOP•12/22/23, 8:06 PM

and again, it's many where clauses, not many small queries/selects/sub-selects

UnmeiOP•12/22/23, 8:06 PM

in the end, it generate one single query

UUnmei where clauses are different for each user

PixxelKick•12/23/23, 7:08 AM

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

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

UnmeiOP•12/23/23, 3:00 PM

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()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•12/23/23, 3:10 PM

What would be the source of dateDisabled1dateDisabled1 and dateDisabled2dateDisabled2?

UUnmei It Works! So that's the solution: ```csharp Expression<Func<OrderSummaryDpid>> ...

UnmeiOP•12/23/23, 3:11 PM

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

PixxelKick•12/23/23, 3:40 PM

I'm looking for the actual higher level use case here

PixxelKick•12/23/23, 3:40 PM

Ideally in C#

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

Similar Threads

Similar Threads

Similar Threads