Code first Entity Framework 6.1 自定义聚合函数 [英] Code first Entity Framework 6.1 Custom Aggregate Function

问题描述

我在 SQL Server 上有一个自定义 CLR 聚合函数来计算百分位数.是否可以通过实体框架调用我的自定义聚合函数?映射如何配置以允许这样做?

I have a custom CLR Aggregate function on SQL Server to calculate percentiles. Is it possible to call my custom aggregate function through Entity Framework? How is the mapping configured to allow this?

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

I have tried using codefirstfunctions similar to what is described on Entity Framework 6 Code First Custom Functions, however the functions seem to only be allowed to take scaler parameters, where my function is an aggregate function so will need to take a list of items (similar to how Sum, Averagg and Count work).

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

The Aggregate functions has the following signature, taking in the value we want the median from and the percentile (50 is median, 25 lower quartile, 75 upper quartile)

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

我已尝试添加 DbFunctionAttribute，但不完全确定如何先使用代码将其连接到实体框架存储模型.

I have tried adding a DbFunctionAttribute, but not entirely sure how to hook it up to entity framework store model using code first.

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

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

<小时>

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

---

What I am looking for is to be able to write something like

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

这将映射到 SQL 之类的

Which will map to SQL like

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

推荐答案

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

As @srutzky alluded to in the comments, EF doesnt seem to like binding to aggregate functions with multiple parameters. So you have to change percentile function to a median function or whatever fixed percentile you are interested (you will need to update your SqlClr function so the parameters match as well)

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# 函数完全匹配.

The next step is letting EF know that a the database has a function called median We can do this in our DbContext. Create a new convention to access the the dbModel then we add the function in the dbModel. You must make sure the parameters and the parameter types match both the SQL and the C# function exactly.

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;
        }
    }
}

一切就绪后，您应该能够按预期调用您的函数

With all that in place you should just be able to call your function as expected

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

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

Note: I am already assume you have loaded your SqlClr function which I have not covered here

这篇关于Code first Entity Framework 6.1 自定义聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！