CODEWITHSUNDEEP
c#linqentity-framework-coreautomapper
JustAMartin
JustAMartin

Reputation: 13753

AutoMapper.ProjectTo with criteria query is being evaluated locally

I'm using Automapper queryable extensions: http://docs.automapper.org/en/stable/Queryable-Extensions.html#

I want to map my full entity to its simplified version with just a few fields. In this case, my frontend UI will not know about the base entity and will send filtering criteria for the simplified entity only. So, I'm using query.ProjectTo<MySimpleEntity> and then applying Where with the filtering criteria.

In this specific case, both entities implement my custom interface ITimeLimitedEntity which ensures ValidFrom and ValidTo fields.

When I don't use Automapper and apply criteria on the full entity, filtering works as expected and a proper SQL query is generated.

But as soon as I add ProjectTo<MySimpleEntity>, I get a warning (which I actually throw as exception to avoid silent client-side evaluations):

The LINQ expression 'where ((Convert(new MySimpleEntity() {Id = [dtoMyOriginalEntity].Id, Name = [dtoMyOriginalEntity].Name, ValidFrom = [dtoMyOriginalEntity].ValidFrom}, ITimeLimitedEntity).ValidFrom == null))'
 could not be translated and will be evaluated locally.'

So, the criteria are being applied on Automapper's internal dtoMyOriginalEntity which, of course, does not implement my ITimeLimitedEntity, and therefore LINQ adds Convert, which, obviously, cannot be translated to a valid SQL query, thus causing attempts to evaluate it in memory.

This is strange. When I debug the code:

 projQuery = query.ProjectTo<MySimpleEntity>(_mapper.ConfigurationProvider);

projQuery is IQueryable, which implements ITimeLimitedEntity - then why Linq needs to convert?

How do I tell Automapper or Linq that it should apply my where criteria on the final projected MySimpleEntity which already is ITimeLimitedEntity and not on some Automapper's internal DTO entity?

Update:

I created a simple dotnetfiddle example https://dotnetfiddle.net/X2RDGn Of course, it works just fine because it's using in-memory database. But I have commented the problematic areas that fail in the real world.

Update 2: I created a simplified console app gist.github.com/progmars/eeec32a533dbd2e1f85e551db1bc53f8 with real database, and Linq expression still is being executed locally when accessed through generic method, although debugger shows exact match for both expression bodies.

Update 3:

As some pointed out, the reason is Linq-to-entities itself and not Automapper. Therefore, a new, less convoluted reproducible case was created for a new SO question, and it was solved here: Why Linq "where" expression after Select gets evaluated locally when created through a generic method?

Upvotes: 1

Views: 1117

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205829

The problem has nothing to do with AutoMapper, but the predicate expression based on interface member. Convert is the expression equivalent of C# cast and as you can see, it's casting the entity type to the interface containing the property (dtoMyOriginalEntity is just the name of the lambda expression parameter).

This is a known behavior of interface constrained generic methods. The solution is to add class constraint. Applying it to your example:

public class CriteriaSpecificationForSomeable<T> : ISpecification<T> where T: class, ISomeable
// ----------------------------------------------------------------------------^^^
{
    private Expression<Func<T, bool>> _someCriteria;

    public CriteriaSpecificationForSomeable()
    {
        _someCriteria = e => (e.Some == "Hello");
    }

    public virtual Expression<Func<T, bool>> Criteria { get { return _someCriteria; } }
}

and the cast (Convert) is gone.

Btw, this is fixed in the latest EF Core 2.2.6 and the original code works there (probably your are using older EF Core version). But EF6 had the same problem, so generally it's safer to add that class constraint when creating expressions against interface constrained generic type.

Upvotes: 2

Related Questions