AutoMapper ProjectTo from a model with child list entity

Question

Is this possible in AutoMapper?

1. Select list of type X and filter child entity of type Y (return single value of Y)
2. ProjectTo to a flat DTO contains props from X and Y.

If it is not, then what is the best way to populate the DTO in this scenario, the tables are just for example, in the real scenario the tables have a lot of columns and I want to avoid reading the whole row just to get one or two props.

Below is a quick console App code in .Net 5.0

Project.csproj

    <Project Sdk="Microsoft.NET.Sdk">
      <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net5.0</TargetFramework>
      </PropertyGroup>
      <ItemGroup>
        <PackageReference Include="AutoMapper.Extensions.Microsoft.DependencyInjection" Version="11.0.0" />
        <PackageReference Include="Microsoft.EntityFrameworkCore" Version="5.0.0" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="5.0.0" />
      </ItemGroup>
    </Project>

Console App Testing Code

    using System;
    using System.Linq;
    using AutoMapper;
    using AutoMapper.QueryableExtensions;
    using Microsoft.EntityFrameworkCore;
    using System.Collections.Generic;
    
    namespace MappingTest
    {
        public class Parent
        {
            public int Id { get; set; }
            public string ParentName { get; set; }
            public List<Child> Children { get; set; } = new List<Child>();
        }
    
        public class Child
        {
            public int Id { get; set; }
            public int Age { get; set; }
            public string ChildName { get; set; }
            public Parent Parent { get; set; }
        }
    
        public class ParentDto
        {
            public int Id { get; set; }
            public string ParentName { get; set; }
            public string ChildName { get; set; }
        }
    
        public class DataContext : DbContext
        {
            public DbSet<Parent> Parents { get; set; }
            public DbSet<Child> Children { get; set; }
    
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseInMemoryDatabase("MyDb");
            }
        }
    
        internal class Program
        {
            public static void Seed(DataContext context)
            {
                context.Parents.Add(new Parent
                {
                    Id = 1,
                    ParentName = "John",
                    Children = new List<Child>()
                        {
                            new Child { Id = 1, ChildName = "Peter", Age = 10 },
                            new Child { Id = 2, ChildName = "Smith", Age = 20 }
                        }
                });
    
                context.Parents.Add(new Parent
                {
                    Id = 2,
                    ParentName = "Micheal",
                    Children = new List<Child>()
                        {
                            new Child { Id = 3, ChildName = "Wale", Age = 10 },
                            new Child { Id = 4, ChildName = "Robert", Age = 25 }
                        }
                });
    
                context.SaveChanges();
            }
    
            static void Main(string[] args)
            {
                var config = new MapperConfiguration((cfg) =>
                {
                    cfg.CreateMap<Parent, ParentDto>();
                    cfg.CreateMap<Child, ParentDto>();
                });
    
                var mapper = config.CreateMapper();
    
    
                using (var context = new DataContext())
                {
                    Seed(context);
    
                    var parent = context.Parents
                        // Filter children and get only the 10 years old (Peter and Wale)
                        // Project to the Dto and have the ChildName mapped to the Dto
                        // Note: Parent should have only one 10 years old child
                        .ProjectTo<ParentDto>(mapper.ConfigurationProvider)
                        .ToList();
    
                    foreach(var p in parent)
                    {
                        Console.WriteLine(string.Format("{0} - {1} - {2}",p.Id, p.ParentName, p.ChildName));
                    }
                }
            }
        }
    }

Could not find a similar scenario with a solution

Update #1

I'm really considering Dapper No Mapper, @Prolog your answer helped a lot, I managed to solve it correctly by using the other way around

    var config = new MapperConfiguration((cfg) =>
                {
                    cfg.CreateMap<Parent, ParentDto>();
    
                    cfg.CreateMap<Child, ParentDto>()
                        .IncludeMembers(e => e.Parent);
                });

And then ProjectTo like this

    var parents = context.Parents
                        .SelectMany(e => e.Children.Where(a => a.Age == 10))
                        .ProjectTo<ParentDto>(mapper.ConfigurationProvider)
                        .ToList();

But the generated SQL is funny

    SELECT [t].[ChildName], [t].[Id], [p0].[ParentName]
    FROM [Parents] AS [p]
    INNER JOIN (
        SELECT [c].[Id], [c].[ChildName], [c].[ParentId]
        FROM [Children] AS [c]
        WHERE [c].[Age] = 10
    ) AS [t] ON [p].[Id] = [t].[ParentId]
    LEFT JOIN [Parents] AS [p0] ON [t].[ParentId] = [p0].[Id]

Where the required SQL is very simple

SELECT p.Id, 
       p.ParentName, 
       c.ChildName
FROM dbo.Parents p
     LEFT JOIN dbo.Children c ON p.Id = c.Id
-- or INNER JOIN
WHERE c.Age = 10;

Answer 1

You can use IncludeMembers() to tell AutoMapper to try to fill properties of ParentDto will values from Child after it is done with mapping from Parent. Read more about this feature in AutoMapper documentation.

var config = new MapperConfiguration((cfg) =>
{
    cfg.CreateMap<Parent, ParentDto>()
        .IncludeMembers(src => src.Children.First());
    cfg.CreateMap<Child, ParentDto>();
});

Also, don't test your database-oriented projections with an In-Memory database, as it will hide all kind of query execution errors until you switch to a real database.

So if you want to filter out only to parents with a child that's 10 years old:

var parents = context.Parents
    .Where(x => x.Children.Any(x => x.Age == 10))
    .ProjectTo<ParentDto>(mapper.ConfigurationProvider)
    .ToList();

with Microsoft SQL-Server, such query will be produced:

SELECT [p].[id],
       [p].[parentname],
       (SELECT TOP(1) [c0].[childname]
        FROM   [children] AS [c0]
        WHERE  [p].[id] = [c0].[parentid]) AS [ChildName]
FROM   [parents] AS [p]
WHERE  EXISTS (SELECT 1
               FROM   [children] AS [c]
               WHERE  ( [p].[id] = [c].[parentid] )
                      AND ( [c].[age] = 10 ))