C# EF Core QueryableExtensions.FirstOrDefaultAsync strange behaviour

c# entity-framework-core expression generics


I have a generic repository to whom I pass an Expression<Func<TEntity, bool>> filter. For example this expression looks like:

filterExpr = part =>
    (filter.ProductId != null ? filter.ProductId == part.ProductId : true) &&
    (filter.SerialNumber != null ? filter.SerialNumber == part.SerialNumber : true);

In the repository I call QueryableExtensions.FirstOrDefaultAsync(filterExpr) on a DbSet that I've got from DbContext:

this.dbSet = context.Set<TEntity>()
// ....
IQueryable<TEntity> query = dbSet;
return query.FirstOrDefaultAsync(filter);

It works well if either ProductId or SerialNumber or both are defined. If all filter properties are null, I would expect the expression to evaluate to trueand therefore FirstOrDefaultAsync to return the first Element in the queried table. But instead EF core seems to try to load the whole table. My memory consumption goes up a couple of GB and then I receive a timeout. When I perform a UnitTest using an InMemoryDatabase I get null as result which is also not what I expect.

Edit: Here is the UnitTest as requested.

    public void GetPartWithEmptyFilterTest()
        var optionsBuilder = new DbContextOptionsBuilder<ProductStatusContext>();
        var context = new ProductStatusContext(optionsBuilder.Options);

        var part = new Part { SerialNumber = "abcdef" };


        var unitOfWork = new UnitOfWork(context);
        var service = new PartService(unitOfWork, null, null);

        service.GetPartAsync(new PartFilterResource()).Result.Should().Be(part);

Edit2: My current workaround is to test all filter Properties for a null value and evaluate the filte only if at least on propery is different from null

private bool AllPropertiesNull(object obj)
    return !obj.GetType().GetProperties().Any(propInfo => propInfo.GetValue(obj) != null);

And then:

if(filter != null && AllPropertiesNull(filter))
    filterExp = part => true;

But I'd like to avoid this.

8/16/2019 8:50:47 AM

Accepted Answer

Based on my experience with EF Core, the query translator is trying to eliminate the constant predicate expressions in order to produce criteria (or no criteria at all) similar to if you were using conditional Where.

However it seems to handle properly binary expressions (&&, ||) and fail doing that for conditional ? : expressions. You may report it to their GitHub issue tracker, but since they are working on EF Core 3.0, I don't think it would be addressed in earlier versions. And 3.0 is still in state where many things doesn't work, hence cannot be used to evaluate whether this would work or not in the final version.

The workaround I would suggest (and seems to work well in all versions) is to use the equivalent || based conditions, e.g.

filterExpr = part =>
    (filter.ProductId == null || filter.ProductId == part.ProductId) &&
    (filter.SerialNumber == null || filter.SerialNumber == part.SerialNumber); 
8/16/2019 9:13:17 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow