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
– Moho, Commented Oct 30, 2015 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
– Solomon Rutzky, Commented Oct 30, 2015 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
– Alex, Commented Oct 30, 2015 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
– Solomon Rutzky, Commented Oct 30, 2015 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
– Alex, Commented Oct 30, 2015 at 15:22

Alex · Accepted Answer · 2015-10-30 18:07:36Z

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

Collectives™ on Stack Overflow

Code first Entity Framework 6.1 Custom Aggregate Function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related