如何简化AutoMapper Profile配置以匹配手动映射的SQL结果？

需求背景 #

需要配置AutoMapper Profile，实现和手动映射完全一致的效果，同时解决生成SQL中出现多余CAST(0 AS bit)的问题。

手动映射实现 #

public static IQueryable<EmployeeDto> ConvertToDto(this IQueryable<Employee> employees)
{
    return (from employee in employees
            select new EmployeeDto()
            {
                BusinessEntityId = employee.Person.BusinessEntityId,
                FirstName = employee.Person.FirstName,
                LastName = employee.Person.LastName,
                BirthDate = employee.Person.BirthDate,
                PersonDto = new PersonDto()
                {
                    BusinessEntityId = employee.Person.BusinessEntityId,
                    FirstName = employee.Person.FirstName,
                    LastName = employee.Person.LastName,
                    BirthDate = employee.Person.BirthDate,

                },
            });
}

手动映射的查询与生成SQL #

执行查询：

IQueryable<EmployeeDto> employees = context.Employees.ConvertToDto().AsQueryable();

生成的SQL：

SELECT [p].[BusinessEntityId], [p].[FirstName], [p].[LastName], [p].[BirthDate]
FROM [PersonCollection].[Employees] AS [e]
INNER JOIN [PersonCollection].[People] AS [p] ON [e].[BusinessEntityId] = [p].[BusinessEntityId]
ORDER BY [p].[BusinessEntityId]
OFFSET @__TypedProperty_0 ROWS FETCH NEXT @__TypedProperty_1 ROWS ONLY

相关类定义 #

DTO类

public class EmployeeDto : PersonDtoBase
{
    public EmployeeDto()
    {
    }    
    public PersonDto Person { get; set; }
}

public class PersonDtoBase
{ 
    public int BusinessEntityId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime BirthDate { get; set; }    
}

源实体类

public class Employee
{
    [Key] public int BusinessEntityId { get; set; }
    public Guid RowguidEmployee { get; set; }
    [Column(TypeName = "datetime")] 
    public DateTime ModifiedDateEmplyee { get; set; }

    [ForeignKey(nameof(BusinessEntityId))][InverseProperty(nameof(PersonCollection.Person.Employee))] 
    public Person Person { get; set; }    
}

现有配置及问题 #

现有AutoMapper Profile配置：

public class DtoProfile : Profile
{
    public DtoProfile()
    {
        CreateProjection<Person, PersonDto>();
        CreateMap<Employee, EmployeeDto>( MemberList.Destination)
                .ForMember(e => e.BusinessEntityId, o => o.MapFrom(o => o.Person.BusinessEntityId))
                .ForMember(e => e.FirstName, o => o.MapFrom(o => o.Person.FirstName))
                .ForMember(e => e.LastName, o => o.MapFrom(o => o.Person.LastName))
                .ForMember(e => e.BirthDate, o => o.MapFrom(o => o.Person.BirthDate))   
    }        
}

执行查询：

IQueryable<EmployeeDto> employees = context.Employees.ProjectTo<EmployeeDto>(mapper.ConfigurationProvider).AsQueryable();

生成的SQL出现多余的CAST(0 AS bit)：

SELECT CAST(0 AS bit), [p].[BusinessEntityId], [p].[FirstName], [p].[LastName], [p].[BirthDate]
FROM [PersonCollection].[Employees] AS [e]
INNER JOIN [PersonCollection].[People] AS [p] ON [e].[BusinessEntityId] = [p].[BusinessEntityId]
ORDER BY [p].[BusinessEntityId]
OFFSET @__TypedProperty_0 ROWS FETCH NEXT @__TypedProperty_1 ROWS ONLY

提问：在此场景下，是否有更简洁的AutoMapper Profile配置方式？

解决方案 #

1. 简化基础字段映射，消除多余SQL字段 #

多余的CAST(0 AS bit)是因为AutoMapper默认尝试映射未配置的属性，生成了默认占位值。可以通过统一映射源的方式简化配置：

public class DtoProfile : Profile
{
    public DtoProfile()
    {
        // 配置Person到PersonDto的投影规则
        CreateProjection<Person, PersonDto>();

        // 配置Employee到EmployeeDto的映射
        CreateMap<Employee, EmployeeDto>(MemberList.Destination)
            // 将EmployeeDto继承的PersonDtoBase字段统一映射自Employee.Person
            .ForMember(dest => dest, opt => opt.MapFrom(src => src.Person))
            // 显式映射PersonDto属性
            .ForMember(dest => dest.Person, opt => opt.MapFrom(src => src.Person));
    }
}

2. 完全对齐手动映射的极简方案 #

如果想完全复刻手动映射的SQL生成逻辑，可以直接用CreateProjection定义构造逻辑，避免AutoMapper自动生成多余内容：

public class DtoProfile : Profile
{
    public DtoProfile()
    {
        CreateProjection<Person, PersonDto>();

        // 直接模拟手动映射的构造逻辑
        CreateProjection<Employee, EmployeeDto>()
            .ConstructUsing(src => new EmployeeDto
            {
                BusinessEntityId = src.Person.BusinessEntityId,
                FirstName = src.Person.FirstName,
                LastName = src.Person.LastName,
                BirthDate = src.Person.BirthDate,
                Person = new PersonDto
                {
                    BusinessEntityId = src.Person.BusinessEntityId,
                    FirstName = src.Person.FirstName,
                    LastName = src.Person.LastName,
                    BirthDate = src.Person.BirthDate
                }
            });
    }
}

关键注意事项 #

• 针对IQueryable场景优先使用CreateProjection而非CreateMap，AutoMapper会针对LINQ to Entities做专门优化。
• 确保所有DTO属性都有明确的映射规则，避免AutoMapper生成默认占位值。
• 若不需要映射Employee自身的字段，可通过.ForMember(dest => dest.xxx, opt => opt.Ignore())明确忽略。

内容的提问来源于stack exchange，提问作者demis15