Code first Entity Framework 6.1 Custom Aggregate Function

Question

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

can you show what you've tried and the CLR method signature? — Moho, Oct 30 '15 at 13:48
From the available documentation, my guess is that the attribute needs to be `[DbFunction("CodeFirstDatabaseSchema", "Percentile")]` and the signature should be `public static Double Percentile(Double value, Int16 tile)`. — Solomon Rutzky, Oct 30 '15 at 14:42
@srutzky thats what the SQL function signature is equivalent to, but becasue its an aggregate fucntion to be able to use it within C# the C# signature needs to take a list of items so it can aggregate it, similar to the signature of Sum() — Alex, Oct 30 '15 at 14:49
I guess you can just add the IEnumerable<> to both input params and test that out, right? :-) I looked at the source code for that CodePlex project and UDAs are not a supported type. But given that there is no DB discovery needed for the return type like it is for result sets, I was thinking it might not matter. But I would suspect that you need the `IEnumerable` around both input params, not just the first one, right? — Solomon Rutzky, Oct 30 '15 at 15:07
@srutzky first paramter is the actual thing we are grouping, second paramter is just a constant of the percentile we want. — Alex, Oct 30 '15 at 15:22
There is a built in function EF function `SqlFunctions.ChecksumAggregate` that does do an aggregate so it seems possible, but not sure how to update EFs internal model of functions available on the database — Alex, Oct 30 '15 at 15:24

score 2 · Accepted Answer · answered Oct 30 '15 at 18:07

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

You should also be able to use: `var edmFloatListType = PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Single).GetCollectionType() ` — trs79, Oct 11 '16 at 16:08

Code first Entity Framework 6.1 Custom Aggregate Function

1 Answers1

Linked