代码第一实体框架6.1自定义集合函数 [英] Code first Entity Framework 6.1 Custom Aggregate Function

问题描述

我在SQL Server上有一个自定义CLR聚合函数来计算百分位数。可以通过实体框架调用自定义聚合函数吗？如何映射配置为允许这个？

我已经尝试使用类似于实体框架6代码第一个自定义函数，但是函数似乎只能被允许使用缩放器参数，其中我的函数是一个聚合函数将需要一个项目列表（类似于Sum，Averagg和Count的工作方式）。

聚合函数具有以下签名，使用我们想要的值中位数和百分位数（50是中位数，25低四分位数，75上四分位数）

  CREATE AGGREGATE [dbo]。 ] 
（@value [float]，@tile [smallint]）
 RETURNS [float] 
 EXTERNAL NAME [SqlFuncs]。[Percentile] 
 GO 
  

我已经尝试添加一个DbFunctionAttribute，但不完全确定如何挂接到实体框架存储模型usin g code first。

  [DbFunction（SqlServer，Percentile）] 
 
 public static双？百分位数（IEnumerable< int？> arg，int tile）
 {
抛出新的NotSupportedException（不支持直接调用）; 
} 
  





---

我正在寻找什么可以写出一些像

  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 
  pre> 
 
解决方案
由于@srutzky在评论中暗示，EF似乎并不喜欢绑定到具有多个参数的聚合函数。因此，您必须将百分位数函数更改为中值函数或您感兴趣的任何固定百分位数（您将需要更新您的SqlClr函数，以使参数匹配）
  public class MySqlFunctions 
 {
 [DbFunction（dbo，Median）] 
 public static float？中值（IEnumerable< float？> arg）
 {
抛出新的NotSupportedException（不支持直接调用）; 
} 
} 
  
下一步是让EF知道一个数据库有一个名为median的函数我们可以在DbContext中做到这一点。创建一个新的约定来访问dbModel，然后我们在dbModel中添加该函数。您必须确保参数和参数类型完全匹配SQL和C＃函数。
  public class EmContext：DbContext 
 {
 ... 
 
 protected override void OnModelCreating（DbModelBuilder modelBuilder）
 {
 base.OnModelCreating（modelBuilder）; 
 
 //注册一个约定，所以我们可以加载我们的函数
 modelBuilder.Conventions.Add（new AddMedianFunction（））; 
 
 ... 
 
} 
 
 public class AddMedianFunction：IConvention，IStoreModelConvention< EntityContainer> 
 {
 public void Apply（EntityContainer item，DbModel dbModel）
 {
 //这些参数类型需要匹配数据库方法和EF的C＃方法链接
 var edmFloatType = PrimitiveType.GetEdmPrimitiveType（PrimitiveTypeKind.Single）; 
 
 // CollectionType构造函数内部使得不可能获取集合类型。 
 //我们诉诸反思实例化。 
 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。返回值）
}，
参数= new [] 
 {
 FunctionParameter.Create（input，edmFloatListType，ParameterMode.In），
} 
 }， 空值）; 
 
 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）实例; 
} 
} 
} 
  
应该只能按照预期的方式调用你的函数
  paymentTable 
 .GroupBy（x => x.CustomerId） 
 .Select（new {
 Median = MySqlFunctions.Median（x.Select（g => g.Amount））
}）; 
  
注意：我已经假设你已经加载了我没有在这里覆盖的SqlClr函数
 
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?

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).

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
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)
    });
Which will map to  SQL like
SELECT [dbo].[Percentile](Amount, 50) as Median
FROM Payments
GROUP BY CustomerId

解决方案
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.");
    }
}
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))
    });
Note: I am already assume you have loaded your SqlClr function which I have not covered here

                        
这篇关于代码第一实体框架6.1自定义集合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！