Can I map the first child in a collection efficiently with AutoMapper & EF Core?

[stevendarby]

I would like to project the first child in a collection to another type with AutoMapper & EF, however my first attempt at this results in SQL which performs a sub-query for each property I'm selecting from it, plus an extra sub-query to see if it exists or not.

Another attempt, where I manually map the collection to my type and then do the FirstOrDefault() - the SQL for this is better, selecting from Address only once.

Is there a way to achieve something like this with AutoMapper, so I don't need to do the manual part?

Thanks

Example code (AutoMapper 11.0.1, EF Core 6.0.3)

using AutoMapper;
using AutoMapper.QueryableExtensions;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;

{
    using var context = new PersonContext();
    context.Database.EnsureCreated();

    // Auto map both Person and Address
    var mapper = new MapperConfiguration(x =>
    {
        x.CreateMap<Person, PersonDto>()
            .ForMember(dto => dto.FirstAddress,
                o => o.MapFrom(entity => entity.Addresses
                    .OrderBy(a => a.Id)
                    .FirstOrDefault()));
        x.CreateMap<Address, AddressDto>();
    });
    var people = context.People.ProjectTo<PersonDto>(mapper).ToList();

    // Auto map Person, manually map Addresses
    mapper = new MapperConfiguration(x =>
    {
        x.CreateMap<Person, PersonDto>()
            .ForMember(dto => dto.FirstAddress,
                o => o.MapFrom(entity => entity.Addresses
                    .OrderBy(a => a.Id)
                    .Select(a => new AddressDto { Line1 = a.Line1, Line2 = a.Line2 })
                    .FirstOrDefault()));
    });
    people = context.People.ProjectTo<PersonDto>(mapper).ToList();
}

public class PersonContext : DbContext
{
    public DbSet<Person> People { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=.;Database=BugDemo;Trusted_Connection=True;")
            .LogTo(x => Debug.WriteLine(x), LogLevel.Information);
}

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Address> Addresses { get; set; }
}

public class PersonDto
{
    public string Name { get; set; }
    public AddressDto FirstAddress { get; set; }
}

public class Address
{
    public int Id { get; set; }
    public string Line1 { get; set; }
    public string Line2 { get; set; }
    public Person Person { get; set; }
}

public class AddressDto
{
    public string Line1 { get; set; }
    public string Line2 { get; set; }
}

First query SQL

SELECT [p].[Name], CASE
    WHEN NOT (EXISTS (
        SELECT 1
        FROM [Address] AS [a]
        WHERE [p].[Id] = [a].[PersonId])) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END, (
    SELECT TOP(1) [a0].[Line1]
    FROM [Address] AS [a0]
    WHERE [p].[Id] = [a0].[PersonId]
    ORDER BY [a0].[Id]), (
    SELECT TOP(1) [a1].[Line2]
    FROM [Address] AS [a1]
    WHERE [p].[Id] = [a1].[PersonId]
    ORDER BY [a1].[Id])
FROM [People] AS [p]

Second query SQL

SELECT [p].[Name], [t0].[Line1], [t0].[Line2], [t0].[c]
FROM [People] AS [p]
LEFT JOIN (
    SELECT [t].[Line1], [t].[Line2], [t].[c], [t].[PersonId]
    FROM (
        SELECT [a].[Line1], [a].[Line2], 1 AS [c], [a].[PersonId], ROW_NUMBER() OVER(PARTITION BY [a].[PersonId] ORDER BY [a].[Id]) AS [row]
        FROM [Address] AS [a]
    ) AS [t]
    WHERE [t].[row] <= 1
) AS [t0] ON [p].[Id] = [t0].[PersonId]

[lbargaoanu]

Check the execution plan. That looks good to me. You can set DoNotAllowNull for FirstAddress, but this is really an EF Core question.
You should also check the SQL execution plan. Perhaps the optimizer can handle that query better than it looks.

[stevendarby]

@lbargaoanu, thanks for your reply. I have some more info / findings - some of which is just in case anyone coming across the same issue finds it useful.

I can see DoNotAllowNull eliminates that extra EXISTS sub-query, which is good, though does mean that when a person has no address, the FirstAddress is populated with a defaulted objected rather than null. For my case this is fine.

As for the execution plan / optimisation, I think this is going to vary with the amount of data and I haven't taken an extensive look, but interesting result with a small set of data. The first attempt (now with added DoNotAllowNull), which leads to multiple queries for each property, performs slightly better than the 2nd attempt which joins once with the row_number and partition approach.

The two expressions' debug views are:

First attempt using AutoMapper only

.Call System.Linq.Queryable.Select(
    .Call System.Linq.Queryable.Select(
        .Extension<Microsoft.EntityFrameworkCore.Query.QueryRootExpression>,
        '(.Lambda #Lambda1<System.Func`2[Person,Proxy_System.Object_-206385454___FirstAddress_Name]>)),
    '(.Lambda #Lambda2<System.Func`2[Proxy_System.Object_-206385454___FirstAddress_Name,PersonDto]>))

.Lambda #Lambda1<System.Func`2[Person,Proxy_System.Object_-206385454___FirstAddress_Name]>(Person $dtoPerson) {
    .New Proxy_System.Object_-206385454___FirstAddress_Name(){
        __FirstAddress = .Call System.Linq.Enumerable.FirstOrDefault(.Call System.Linq.Enumerable.OrderBy(
                $dtoPerson.Addresses,
                .Lambda #Lambda3<System.Func`2[Address,System.Int32]>)),
        Name = $dtoPerson.Name
    }
}

.Lambda #Lambda2<System.Func`2[Proxy_System.Object_-206385454___FirstAddress_Name,PersonDto]>(Proxy_System.Object_-206385454___FirstAddress_Name $dtoLet)
{
    .New PersonDto(){
        FirstAddress = .New AddressDto(){
            Line1 = ($dtoLet.__FirstAddress).Line1,
            Line2 = ($dtoLet.__FirstAddress).Line2
        },
        Name = $dtoLet.Name
    }
}

.Lambda #Lambda3<System.Func`2[Address,System.Int32]>(Address $a) {
    $a.Id
}

Second attempt with AutoMapper + manual address projection

.Call System.Linq.Queryable.Select(
    .Extension<Microsoft.EntityFrameworkCore.Query.QueryRootExpression>,
    '(.Lambda #Lambda1<System.Func`2[Person,PersonDto]>))

.Lambda #Lambda1<System.Func`2[Person,PersonDto]>(Person $dtoPerson) {
    .New PersonDto(){
        FirstAddress = .Call System.Linq.Enumerable.FirstOrDefault(.Call System.Linq.Enumerable.Select(
                .Call System.Linq.Enumerable.OrderBy(
                    $dtoPerson.Addresses,
                    .Lambda #Lambda2<System.Func`2[Address,System.Int32]>),
                .Lambda #Lambda3<System.Func`2[Address,AddressDto]>)),
        Name = $dtoPerson.Name
    }
}

.Lambda #Lambda2<System.Func`2[Address,System.Int32]>(Address $a) {
    $a.Id
}

.Lambda #Lambda3<System.Func`2[Address,AddressDto]>(Address $a) {
    .New AddressDto(){
        Line1 = $a.Line1,
        Line2 = $a.Line2
    }
}

The two differ of course. I suppose for my own interest now, is it possible to use AutoMapper alone to produce an expression like the 2nd, should the SQL for the latter turn out to be better in some scenarios? I agree some of aspects of this might be better to question with the EF Core team, though I appreciate your thoughts on this. Thanks again.

[lbargaoanu]

There is no easy way that I can see.
You can have a private list on the dto, with Take(1) and have a calculated property for the first.
You can always play games with expressions, perhaps using a custom projection mapper, but that's going to get ugly :)

[stevendarby]

@lbargaoanu one last thing which I forgot to mention, which is a bit tangential, but I struggled to view the expressions in VS- trying to inspect them or add them to the Watch led to error CS7069: Reference to type 'MapperConfiguration' claims it is defined in 'AutoMapper', but it could not be found. Oddly no issue actually executing the queryable with that expression.

A colleague reported the same and I found downgrading to AutoMapper 10.1.1 fixed it for me. Is it a known issue, or would it be helpful to raise a separate issue for this?

[lbargaoanu]

Try the MyGet build. I use ReadableExpressions and have no such issues :)