Cómo obtener COUNT DISTINCT en SQL traducido con EF Core

Actualización (EF Core 5.x):

A partir de la versión 5.0, expresión Select(expr).Distinct().Count() ahora es reconocido por EF Core y traducido al código SQL correspondiente COUNT(DISTINCT expr)) , por lo tanto, la consulta LINQ original se puede usar sin modificaciones.

Original (EF Core 2.x), la solución NO funciona con EF Core 3.x debido a la reescritura de canal de consulta:

EF (6 y Core) históricamente no es compatible con esta construcción de SQL estándar. Lo más probable es que se deba a la falta de un método LINQ estándar y a las dificultades técnicas para mapear Select(expr).Distinct().Count() a ella.

Lo bueno es que EF Core se puede ampliar reemplazando muchos de sus servicios internos con implementaciones derivadas personalizadas para anular los comportamientos requeridos. No es fácil, requiere mucho código de plomería, pero es factible.

Entonces, la idea es agregar y usar CountDistinct personalizado simple métodos como este

public static int CountDistinct<T, TKey>(this IQueryable<T> source, Expression<Func<T, TKey>> keySelector)
    => source.Select(keySelector).Distinct().Count();

public static int CountDistinct<T, TKey>(this IEnumerable<T> source, Func<T, TKey> keySelector)
    => source.Select(keySelector).Distinct().Count();

y deje que EF Core los traduzca de alguna manera a SQL. De hecho, EF Core proporciona una forma sencilla de definir (e incluso traducir de forma personalizada) funciones escalares de base de datos, pero lamentablemente no se puede usar para funciones agregadas que tienen una canalización de procesamiento independiente. Por lo tanto, debemos profundizar en la infraestructura de EF Core.

El código completo para la canalización de EF Core 2.x se proporciona al final. No estoy seguro de si vale la pena esforzarse porque EF Core 3.0 usará una canalización de proceso de consulta reescrita completa. Pero fue interesante y también estoy bastante seguro de que se puede actualizar para la nueva canalización (con suerte, más simple).

De todos modos, todo lo que necesita es copiar/pegar el código en un nuevo archivo de código en el proyecto, agregue lo siguiente al contexto OnConfiguring anular

optionsBuilder.UseCustomExtensions();

que conectará la funcionalidad a la infraestructura de EF Core y luego consultará de esta manera

var result = db.MyTable
    .GroupBy(x => x.PersonID, x => new { VisitStartDate = x.VisitStart.Date })
    .Select(g => new
    {
        Count = g.CountDistinct(x => x.VisitStartDate)
    }).ToList();

afortunadamente se traducirá al deseado

SELECT COUNT(DISTINCT(CONVERT(date, [x].[VisitStart]))) AS [Count]
FROM [MyTable] AS [x]
GROUP BY [x].[PersonID]

Tenga en cuenta la preselección de la expresión necesaria para el método agregado. Esta es la limitación/requisito actual de EF Core para todos los métodos agregados, no solo el nuestro.

Finalmente, el código completo que hace la magia:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.Expressions;
using Microsoft.EntityFrameworkCore.Query.ExpressionVisitors;
using Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Remotion.Linq;
using Remotion.Linq.Clauses;
using Remotion.Linq.Clauses.ResultOperators;
using Remotion.Linq.Clauses.StreamedData;
using Remotion.Linq.Parsing.Structure.IntermediateModel;

namespace Microsoft.EntityFrameworkCore
{
    public static partial class CustomExtensions
    {
        public static int CountDistinct<T, TKey>(this IQueryable<T> source, Expression<Func<T, TKey>> keySelector)
            => source.Select(keySelector).Distinct().Count();

        public static int CountDistinct<T, TKey>(this IEnumerable<T> source, Func<T, TKey> keySelector)
            => source.Select(keySelector).Distinct().Count();

        public static DbContextOptionsBuilder UseCustomExtensions(this DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                .ReplaceService<INodeTypeProviderFactory, CustomNodeTypeProviderFactory>()
                .ReplaceService<IRelationalResultOperatorHandler, CustomRelationalResultOperatorHandler>();
    }
}

namespace Remotion.Linq.Parsing.Structure.IntermediateModel
{
    public sealed class CountDistinctExpressionNode : ResultOperatorExpressionNodeBase
    {
        public CountDistinctExpressionNode(MethodCallExpressionParseInfo parseInfo, LambdaExpression optionalSelector)
            : base(parseInfo, null, optionalSelector) { }
        public static IEnumerable<MethodInfo> GetSupportedMethods()
            => typeof(CustomExtensions).GetTypeInfo().GetDeclaredMethods("CountDistinct");
        public override Expression Resolve(ParameterExpression inputParameter, Expression expressionToBeResolved, ClauseGenerationContext clauseGenerationContext)
            => throw CreateResolveNotSupportedException();
        protected override ResultOperatorBase CreateResultOperator(ClauseGenerationContext clauseGenerationContext)
            => new CountDistinctResultOperator();
    }
}

namespace Remotion.Linq.Clauses.ResultOperators
{
    public sealed class CountDistinctResultOperator : ValueFromSequenceResultOperatorBase
    {
        public override ResultOperatorBase Clone(CloneContext cloneContext) => new CountDistinctResultOperator();
        public override StreamedValue ExecuteInMemory<T>(StreamedSequence input) => throw new NotSupportedException();
        public override IStreamedDataInfo GetOutputDataInfo(IStreamedDataInfo inputInfo) => new StreamedScalarValueInfo(typeof(int));
        public override string ToString() => "CountDistinct()";
        public override void TransformExpressions(Func<Expression, Expression> transformation) { }
    }
}

namespace Microsoft.EntityFrameworkCore.Query.Internal
{
    public class CustomNodeTypeProviderFactory : DefaultMethodInfoBasedNodeTypeRegistryFactory
    {
        public CustomNodeTypeProviderFactory()
            => RegisterMethods(CountDistinctExpressionNode.GetSupportedMethods(), typeof(CountDistinctExpressionNode));
    }

    public class CustomRelationalResultOperatorHandler : RelationalResultOperatorHandler
    {
        private static readonly ISet<Type> AggregateResultOperators = (ISet<Type>)
            typeof(RequiresMaterializationExpressionVisitor).GetField("_aggregateResultOperators", BindingFlags.NonPublic | BindingFlags.Static)
            .GetValue(null);

        static CustomRelationalResultOperatorHandler()
            => AggregateResultOperators.Add(typeof(CountDistinctResultOperator));

        public CustomRelationalResultOperatorHandler(IModel model, ISqlTranslatingExpressionVisitorFactory sqlTranslatingExpressionVisitorFactory, ISelectExpressionFactory selectExpressionFactory, IResultOperatorHandler resultOperatorHandler)
            : base(model, sqlTranslatingExpressionVisitorFactory, selectExpressionFactory, resultOperatorHandler)
        { }

        public override Expression HandleResultOperator(EntityQueryModelVisitor entityQueryModelVisitor, ResultOperatorBase resultOperator, QueryModel queryModel)
            => resultOperator is CountDistinctResultOperator ?
                HandleCountDistinct(entityQueryModelVisitor, resultOperator, queryModel) :
                base.HandleResultOperator(entityQueryModelVisitor, resultOperator, queryModel);

        private Expression HandleCountDistinct(EntityQueryModelVisitor entityQueryModelVisitor, ResultOperatorBase resultOperator, QueryModel queryModel)
        {
            var queryModelVisitor = (RelationalQueryModelVisitor)entityQueryModelVisitor;
            var selectExpression = queryModelVisitor.TryGetQuery(queryModel.MainFromClause);
            var inputType = queryModel.SelectClause.Selector.Type;
            if (CanEvalOnServer(queryModelVisitor)
                && selectExpression != null
                && selectExpression.Projection.Count == 1)
            {
                PrepareSelectExpressionForAggregate(selectExpression, queryModel);
                var expression = selectExpression.Projection[0];
                var subExpression = new SqlFunctionExpression(
                    "DISTINCT", inputType, new[] { expression.UnwrapAliasExpression() });
                selectExpression.SetProjectionExpression(new SqlFunctionExpression(
                    "COUNT", typeof(int), new[] { subExpression }));
                return new ResultTransformingExpressionVisitor<int>(
                    queryModelVisitor.QueryCompilationContext, false)
                    .Visit(queryModelVisitor.Expression);
            }
            else
            {
                queryModelVisitor.RequiresClientResultOperator = true;
                var typeArgs = new[] { inputType };
                var distinctCall = Expression.Call(
                    typeof(Enumerable), "Distinct", typeArgs,
                    queryModelVisitor.Expression);
                return Expression.Call(
                    typeof(Enumerable), "Count", typeArgs,
                    distinctCall);
            }
        }

        private static bool CanEvalOnServer(RelationalQueryModelVisitor queryModelVisitor) =>
            !queryModelVisitor.RequiresClientEval && !queryModelVisitor.RequiresClientSelectMany &&
            !queryModelVisitor.RequiresClientJoin && !queryModelVisitor.RequiresClientFilter &&
            !queryModelVisitor.RequiresClientOrderBy && !queryModelVisitor.RequiresClientResultOperator &&
            !queryModelVisitor.RequiresStreamingGroupResultOperator;
    }
}