entity-framework - Code first Entity Framework 6.1 自定义聚合函数

Question

我在 SQL Server 上有一个自定义 CLR 聚合函数来计算百分位数。是否可以通过 Entity Framework 调用我的自定义聚合函数？映射如何配置为允许这样做？

我尝试使用类似于 Entity Framework 6 Code First Custom Functions 中描述的 codefirstfunctions ，但是这些函数似乎只允许使用缩放器参数，其中我的函数是一个聚合函数，因此需要获取一个项目列表(类似于 Sum、Averagg 和 Count 的工作方式)。

聚合函数具有以下签名，采用我们想要从中位数和百分位数的值(50 是中位数，25 是下四分位数，75 是上四分位数)

CREATE AGGREGATE [dbo].[Percentile]
(@value [float], @tile [smallint])
RETURNS[float]
EXTERNAL NAME [SqlFuncs].[Percentile]
GO

我尝试添加一个 DbFunctionAttribute，但不完全确定如何首先使用代码将其连接到 Entity Framework 存储模型。

[DbFunction("SqlServer", "Percentile")]

public static double? Percentile(IEnumerable<int?> arg, int tile)
{
    throw new NotSupportedException("Direct calls are not supported.");
}

我正在寻找的是能够写出类似的东西

paymentsTable
    .GroupBy(x=>x.CustomerId)
    .Select(new{
            Median = MyDbContext.Percentile(x.Select(g=>g.Amount), 50)
    });

这将映射到 SQL 之类的

SELECT [dbo].[Percentile](Amount, 50) as Median
FROM Payments
GROUP BY CustomerId

Answer 1

正如@srutzky 在评论中提到的那样，EF 似乎不喜欢绑定(bind)到具有多个参数的聚合函数。因此，您必须将百分位函数更改为中值函数或您感兴趣的任何固定百分位(您需要更新 SqlClr 函数，以便参数也匹配)

public class MySqlFunctions
{
    [DbFunction("dbo", "Median")]
    public static float? Median(IEnumerable<float?> arg)
    {
        throw new NotSupportedException("Direct calls are not supported.");
    }
}

下一步是让 EF 知道数据库有一个名为 median 的函数。我们可以在 DbContext 中执行此操作。创建一个新的约定来访问 dbModel，然后我们在 dbModel 中添加函数。您必须确保参数和参数类型与 SQL 和 C# 函数完全匹配。

public class EmContext : DbContext
{    
    ...

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        //Register a convention so we can load our function
        modelBuilder.Conventions.Add(new AddMedianFunction());

        ...

    }

    public class AddMedianFunction : IConvention, IStoreModelConvention<EntityContainer>
    {
        public void Apply(EntityContainer item, DbModel dbModel)
        {
            //these parameter types need to match both the database method and the C# method for EF to link
            var edmFloatType = PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Single);

            //CollectionType constructor is internal making it impossible to get a collection type. 
            //We resort to reflection instantiation.
            var edmFloatListType = CreateInstance<CollectionType>(edmFloatType);

            var medianfunction = EdmFunction.Create("Median", "dbo", DataSpace.SSpace, new EdmFunctionPayload
            {
                ParameterTypeSemantics = ParameterTypeSemantics.AllowImplicitConversion,
                IsComposable = true,
                IsAggregate = true,
                Schema = "dbo",
                ReturnParameters = new[]
                {
                    FunctionParameter.Create("ReturnType", edmFloatType, ParameterMode.ReturnValue)
                },
                Parameters = new[]
                {
                    FunctionParameter.Create("input", edmFloatListType, ParameterMode.In),
                }
            }, null);

            dbModel.StoreModel.AddItem(medianfunction);
            dbModel.Compile();       
        }

        public static T CreateInstance<T>(params object[] args)
        {
            var type = typeof(T);
            var instance = type.Assembly.CreateInstance(
                type.FullName, false,
                BindingFlags.Instance | BindingFlags.NonPublic,
                null, args, null, null);
            return (T)instance;
        }
    }
}

有了所有这些，您应该能够按预期调用您的函数

paymentsTable
    .GroupBy(x=>x.CustomerId)
    .Select(new{
            Median = MySqlFunctions.Median(x.Select(g=>g.Amount))
    });

注意:我已经假设您已经加载了我没有在这里介绍的 SqlClr 函数