How can I reuse the logic in a Where call while working with Entity Framework Core in .NET?

Question

I am writing a database query using LINQ in .NET and I want to be able to not duplicate the code I put in my Where method calls. All my entities inherit BaseEntity and have in common the properties used in the Where call.

public class BaseEntity
{
    public Guid Id { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime UpdatedAt { get; set; }
    public DateTime? DeletedAt { get; set; }
}

public class A : BaseEntity
{
    public List<B> Bs { get; set; }
}

public class B : BaseEntity
{
    public A A { get; set; }
}

At the moment I have written a query that works but I am not satisfied that I need to duplicate code. It looks like this:

var result = _context.Set<A>().Include(a => a.Bs.Where(b => b.DeletedAt == null && b.UpdatedAt > dateTime ||
                                                            b.DeletedAt != null && b.DeletedAt > dateTime))
                              .Where(a => a.DeletedAt == null && a.UpdatedAt > dateTime ||
                                          a.DeletedAt != null && a.DeletedAt > dateTime)
                              .ToList();

As you can see the code in Where calls is pretty much the same, yet I couldn't find a way to put it in a place and be able to reuse it. I did try to create an extension for IQueryable but I wouldn't be able to use it in the Where inside the Include becuause Bs is not IQueryable. Obviously, just putting the logic in a method doesn't work because it can't be translated.

@PrestonGuillot I can't create Expressions because in the a.Bs.Where(...) it actually expects a Func, not an Expression. Yet, if i just put a Func in a variable and then use it, it wouldn't be able to translate the query. — stack_overflow_nickname
– stack_overflow_nickname, Commented Oct 7, 2023 at 23:09
You are wrong. The Where in the Include works and it is also necessary. I just need a way to reuse that statement as it will appear in many places and I don't want to have to copy it all over the place. — stack_overflow_nickname
– stack_overflow_nickname, Commented Oct 7, 2023 at 23:20
Unfortunately the C# compiler has no notion of "expression splicing", whereby you can syntactically compose expression trees. What you can do however is factor out the innermost Expression<Func<BaseEntity, Bool>>, pass it unchanged in the second position, and for the first position synthesize an appropriate Expression<Func<A, List<B>>> using the tools described here and the Expression<Func<BaseEntity, Bool>> you factored out. — user1726343
– user1726343, Commented Oct 7, 2023 at 23:50
You might find the information here relevant: stackoverflow.com/questions/40810227/… — user1726343
– user1726343, Commented Oct 7, 2023 at 23:52

Steve Py · Accepted Answer · 2023-10-09 00:41:24Z

0

A common expression should work, it possibly wasn't implemented correctly as they can be somewhat temperamental. For instance:

private Expression<Func<T, bool>> FilterByDate<T>(DateTime dateTime) where T : BaseEntity
{
    Expression<Func<T, bool>> expression = x => 
        (x.DeletedAt == null && x.UpdatedAt > dateTime) 
        || (x.DeletedAt != null && x.DeletedAt > dateTime)
    return expression;
}

Then in your query:

var result = _context.Set<A>().Include(a => a.Bs.Where(filterByDate<B>(dateTime))
    .Where(a => filterByDate<A>(dateTime))
    .ToList();

The tests I ran with a base class for dates looked to work for both the filtering of the items and the included children. It did require explicitly providing the type to the filter call.

answered Oct 9, 2023 at 0:41

Steve Py

36.3k4 gold badges34 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MonstraG Over a year ago

For me on OP's example this worked in .Where(a => ), but not in .Include(a => a.Bs.Where()), with following compilation error:

Cannot resolve method 'Where(Expression<Func<B,bool>>)', candidates are:  IEnumerable<B> Where<B>(this IEnumerable<B>, Func<B,bool>) (in class Enumerable) IEnumerable<B> Where<B>(this IEnumerable<B>, Func<B,int,bool>) (in class Enumerable)

For some reason, expects me to pass Func there, not Expression (and of course Func produces a runtime error) (C# 12, .net 8)

Steve Py Over a year ago

I'll have a look if I still have the example I had written for that, but it had worked for both the outer where and inner include where. If it's insisting on resolving to IEnumerable that may be something odd with how the relation is declared/resolved by EF (not a regular relational 1-many/many-many) or possibly a missing using for EF Core where that is called? Consider raising a new question with an actual example you have tried with the related entity declarations, expression method, and Linq query. It should work, though as mentioned it can be finnicky.

MonstraG Over a year ago

here you go: stackoverflow.com/questions/78651312/…

Collectives™ on Stack Overflow

How can I reuse the logic in a Where call while working with Entity Framework Core in .NET?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related