Is there an efficient way to only keep a single element of a child collection when mapping using AutoMapper?

user_11062587 0
Viewed 346

I have AutoMapper configured in my .NET 5.0 project with a map between an entity (Setting) and its DTO (SettingByProfileDto). The said entity has a child collection of another entity (SettingValue) (one to many). The child collection (SettingValues) of the first entity is mapped to a single item (another DTO : SettingValueDto) inside the DTO because I only need a specific item from this list.

For the mapping configuration, I use the following lines :

int profileId = default;

profile.CreateMap<Setting, SettingByProfileDto>()
    .ForMember(dto => dto.SettingValue, opt =>
    {
        opt.MapFrom(ss => 
            ss.SettingValues
            .FirstOrDefault(ssv => ssv.ProfileId == profileId)
        );
    });

When I want to retrieve the first entity I use the AutoMapper ProjectTo method in order to only request the fields the DTO has. I give to the ProjectTo method the value of the profileId parameter so the mapping can know on which Id the filter has to be done :

// ...
.Where(ss => ss.Id == request.Id)
.ProjectTo<SettingByProfileDto>(
    _mapper.ConfigurationProvider,
    new { profileId = request.ProfileId },
    dest => dest.SettingValue
) 
// ...

The result of the query and the mapping are both correct. However, the query sent to the database to fetch the results seems to be poorly optimized.

Here is the resulting query :

SELECT [s8].[Description], [s8].[DisplayName], [s8].[Id], [s8].[Name], CASE
    WHEN (
        SELECT TOP(1) [s].[Id]
        FROM [SettingValue] AS [s]
        WHERE ([s8].[Id] = [s].[SettingId]) AND ([s].[ProfileId] = @__profileId_1)) IS NULL THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END, (
    SELECT TOP(1) [s2].[Id]
    FROM [SettingValue] AS [s2]
    WHERE ([s8].[Id] = [s2].[SettingId]) AND ([s2].[ProfileId] = @__profileId_1)), (
    SELECT TOP(1) [s3].[ProfileId]
    FROM [SettingValue] AS [s3]
    WHERE ([s8].[Id] = [s3].[SettingId]) AND ([s3].[ProfileId] = @__profileId_1)), (
    SELECT TOP(1) [s4].[SettingId]
    FROM [SettingValue] AS [s4]
    WHERE ([s8].[Id] = [s4].[SettingId]) AND ([s4].[ProfileId] = @__profileId_1)), (
    SELECT TOP(1) [s7].[Value]
    FROM [SettingValue] AS [s7]
    WHERE ([s8].[Id] = [s7].[SettingId]) AND ([s7].[ProfileId] = @__profileId_1))
FROM [Setting] AS [s8]
WHERE [s8].[Id] = @__request_Id_0
ORDER BY (SELECT 1)
OFFSET @__p_2 ROWS FETCH NEXT @__p_3 ROWS ONLY

And here is the query expression that is generated by AutoMapper and then transformed to SQL :

DbSet<Setting>()
    .AsNoTracking()
    .Where(ss => ss.Id == __request_Id_0)
    .Select(dtoSetting => new Object_1800281414___SettingValue_Description_DisplayName_Id_Name{ 
        __SettingValue = dtoSetting.SettingValues
            .FirstOrDefault(ssv => ssv.ProfileId == __profileId_1), 
        Description = dtoSetting.Description, 
        DisplayName = dtoSetting.DisplayName, 
        Id = dtoSetting.Id, 
        Name = dtoSetting.Name, 
    }
    )
    .Select(dtoLet => new SettingByProfileDto{ 
        Description = dtoLet.Description, 
        DisplayName = dtoLet.DisplayName, 
        Id = dtoLet.Id,  
        Name = dtoLet.Name, 
        SettingValue = dtoLet.__SettingValue == null ? null : new SettingValueDto{ 
            Id = dtoLet.__SettingValue.Id, 
            ProfileId = dtoLet.__SettingValue.ProfileId, 
            SettingId = dtoLet.__SettingValue.SettingId, 
            Value = dtoLet.__SettingValue.Value 
        }
    }
    )
    .Skip(__p_2)
    .Take(__p_3)

I tried to replace the FirstOrDefault clause in the mapping configuration by a Where (with the same condition) and in this case, the query generated will use a LEFT JOIN which avoid repeating one WHERE per field. However, with this way, I can't map the child collection to a single item but only another collection (of dto).

My questions are the following :

  1. Is there a better (more efficient?) way to achieve what I want (only keep one item of the child collection) ?
  2. Is the above query considered optimized ? If so, I would be able to continue with my current way of doing

Thanks for your help !

entity-framework-core automapper sql-server sql c#
1 Answers

AutoMapper projections work by injecting Select calls with generated mapping selectors, or just selectors in the query expression tree where the destination type doesn't match the source type.

The problem is where are these injected. For instance, in your example it generates something like this (pseudo code, MapTo just marks the mapping injection point)

SettingValue = source.SettingValues
    .FirstOrDefault(ssv => ssv.ProfileId == profileId)
    .MapTo<SettingByProfileDto>()

Here with the predicate version of FirstOrDefault it has no choice (well, relatively, keep reading), but even if you rewrite it to the equivalent Where(predicate) + FirstOrDefault() chain, it still injects the mapping at the end

SettingValue = source.SettingValues
    .Where(ssv => ssv.ProfileId == profileId)
    .FirstOrDefault()
    .MapTo<SettingByProfileDto>()

which is too late, and that's why EF Core generates inefficient query.

Now, one may consider this to be EF Core query translation defect. But if the mapping is injected before the FirstOrDefault() call

SettingValue = source.SettingValues
    .Where(ssv => ssv.ProfileId == profileId)
    .MapTo<SettingByProfileDto>()
    .FirstOrDefault()

then Core produces optimal translation.

I didn't find a way to force AM to do that, and also it is good all that to happen transparently. So I wrote a little custom extension. What it does is to to plug into AutoMapper pipeline and transform appropriately the following Enumerable extension methods (both predicate and non predicate overloads) - First, FirstOrDefault, Last, LastOrDefault, Single, SingleOrDefault.

Here is the source code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using AutoMapper.Internal;
using AutoMapper.QueryableExtensions;
using AutoMapper.QueryableExtensions.Impl;


namespace AutoMapper
{
    public static class SingeResultQueryMapper
    {
        public static IMapperConfigurationExpression AddSingleResultQueryMapping(this IMapperConfigurationExpression config)
        {
            config.Advanced.QueryableBinders.Insert(0, new Binder());
            config.Advanced.QueryableResultConverters.Insert(0, new ResultConverter());
            return config;
        }

        static string[] TargetMethodNames => new[]
        {
            nameof(Enumerable.First),
            nameof(Enumerable.FirstOrDefault),
            nameof(Enumerable.Last),
            nameof(Enumerable.LastOrDefault),
            nameof(Enumerable.Single),
            nameof(Enumerable.SingleOrDefault),
        };

        static HashSet<MethodInfo> TargetMethods { get; } =
            (from method in typeof(Enumerable).GetTypeInfo().DeclaredMethods
             join name in TargetMethodNames
             on method.Name equals name
             select method).ToHashSet();

        static bool IsTarget(IMemberMap propertyMap) =>
            propertyMap.SourceType != propertyMap.DestinationType &&
            propertyMap.ProjectToCustomSource is null &&
            propertyMap.CustomMapExpression?.Body is MethodCallExpression call &&
            call.Method.IsGenericMethod &&
            TargetMethods.Contains(call.Method.GetGenericMethodDefinition());

        class ResultConverter : IExpressionResultConverter
        {
            public bool CanGetExpressionResolutionResult(ExpressionResolutionResult expressionResolutionResult, IMemberMap propertyMap)
                => IsTarget(propertyMap);

            public ExpressionResolutionResult GetExpressionResolutionResult(ExpressionResolutionResult expressionResolutionResult, IMemberMap propertyMap, LetPropertyMaps letPropertyMaps)
                => new(propertyMap.CustomMapExpression.ReplaceParameters(propertyMap.CheckCustomSource(expressionResolutionResult, letPropertyMaps)));
        }

        class Binder : IExpressionBinder
        {
            public bool IsMatch(PropertyMap propertyMap, TypeMap propertyTypeMap, ExpressionResolutionResult result)
                => IsTarget(propertyMap);

            public MemberAssignment Build(IConfigurationProvider configuration, PropertyMap propertyMap, TypeMap propertyTypeMap, ExpressionRequest request, ExpressionResolutionResult result, IDictionary<ExpressionRequest, int> typePairCount, LetPropertyMaps letPropertyMaps)
            {
                var call = (MethodCallExpression)result.ResolutionExpression;
                var selectors = configuration.ExpressionBuilder.CreateMapExpression(
                    new(propertyMap.SourceType, propertyMap.DestinationType, request.MembersToExpand, request),
                    typePairCount, letPropertyMaps.New());
                if (selectors == null) return null;
                var query = call.Arguments[0];
                var method = call.Method.GetGenericMethodDefinition();
                if (call.Arguments.Count > 1)
                {
                    // Predicate version of the method
                    // Convert query.Method(predicate) to query.Where(predicate).Method()
                    query = Expression.Call(typeof(Enumerable), nameof(Enumerable.Where), new[] { propertyMap.SourceType }, query, call.Arguments[1]);
                    method = TargetMethods.First(m => m.Name == method.Name && m.GetParameters().Length == 1);
                }
                method = method.MakeGenericMethod(propertyMap.DestinationType);
                foreach (var selector in selectors)
                    query = Expression.Call(typeof(Enumerable), nameof(Enumerable.Select), new[] { selector.Parameters[0].Type, selector.ReturnType }, query, selector);
                call = Expression.Call(method, query);
                return Expression.Bind(propertyMap.DestinationMember, call);
            }
        }
    }
}

Just put it in a code file inside the project where you configure AutoMapper, and then enable it with the provided configuration helper extension method (similar to Expression Mapping AutoMapper extension) like this

var mapper = new MapperConfiguration(cfg => {
    cfg.AddSingleResultQueryMapping();
    // The rest ...
}).CreateMapper();

or with DI

services.AddAutoMapper(cfg => {
    cfg.AddSingleResultQueryMapping();
}, /* assemblies with profiles */);

And that's all. Now your original DTO, mapping and ProjectTo will produce optimal SQL query translation (single LEFT OUTER JOIN).

user_52025630
Related