Optimization of correlated subqueries with Automapper

automapper c# entity-framework-core linq

Question

Update: Automapper applies this automatically in simple cases since it already adds a ToList(). The issue I am seeing that led me to open this question turns out to be a more complicated issue (the SoftwareIds member is the culprit of N+1. See this.).


In EF Core 2.1, we gained support for adding ToList() on a LINQ subquery to buffer results and avoid N+1 database queries. (Docs) This works great on plain LINQ queries against a DbContext.

However, if I have an Automapper profile that results in N+1 queries:

    public MyMappingProfile() =>
        CreateMap<MyEntity, MyDto>().ForMember(e => e.MyCollectionProp, o => o.MapFrom(l => l.MyCollectionPropMany.Select(la => la.MyCollectionEntity)))

Adding ToList() throws an exception:

    public MyMappingProfile() =>
        CreateMap<MyEntity, MyDto>().ForMember(e => e.MyCollectionProp, o => o.MapFrom(l => l.MyCollectionPropMany.Select(la => la.MyCollectionEntity).ToList()))

System.NotSupportedException: 'Could not parse expression 'MyDto.MyCollectionPropMany.Select(la => la.MyCollectionEntity).ToList()': This overload of the method 'System.Linq.Enumerable.ToList' is currently not supported.'

Is there a way to enable subquery buffering in an Automapper profile?

Models:

public class MyEntity
{
    public int Id { get; set; }
    public ICollection<MyCollectionPropMany> MyCollectionPropManys { get; set; }
    ...
}

public class MyCollectionPropMany
{
    public int MyEntityId { get; set; }
    public MyEntity MyEntity { get; set; }
    public int MyCollectionPropId { get; set; }
    public MyCollectionProp MyCollectionProp { get; set; }
}

public class MyCollectionProp
{
    public int Id { get; set; }
    public ICollection<MyCollectionPropMany> MyCollectionPropManys { get; set; }
    ...
}

public class MyDto
{
    public int Id { get; set; }
    public IEnumerable<MyCollectionPropDto> MyCollectionPropDtos { get; set; }
    ...
}

public class MyCollectionPropDto
{
    public string Name { get; set; }
    ...
}

Automapper v7.0.1

The real scenario (I tried to simplify/make generic for SO): Source In this real example, the Languages and Tags members via many-to-many are currently generating N+1 queries.

1
2
9/19/2018 7:04:26 PM

Accepted Answer

It turns out that AutoMapper sometimes automatically adds ToList / ToArray to the projection expression when mapping enumerable types, sometimes doesn't.

The rule seems to be as follows. If the destination enumerable type is directly assignable from the source expression type, AutoMapper uses directly the source expression. In other words, if the following assignment is valid (pseudo code):

dst.Member = src.Expression;

In this case, it's up to you to include ToList or not in your mapping expression (thus opt-in for EF Core correlated query optimization).

In all other cases AutoMapper performs enumerable element mapping if needed and then adds either ToArray or ToList. There is no way to opt-out.

Shortly, if the destination enumerable element type if Dto (requires mapping), don't include ToList in source LINQ expression, if it is primitive or entity type, do include ToList to avoid N + 1 queries. All this applies if the destination collection type is IEnumerable<T>. Any other derived collection type like IReadOnlyCollection<T>, IReadOnlyList<T>, ICollection<T>, IList<T>, List<T>, T[] etc. will be handled automatically by AutoMapper in case the source expression returns IEnumerable<TSource>.

2
9/20/2018 7:25:36 AM


Related Questions





Related

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