CODEWITHSUNDEEP
c#entity-framework-6user-defined-functions
Krk Črn
Krk Črn

Reputation: 153

EntityFramework 6.4.4 and SQL aggregate user defined function with multiple parameters

I have an aggregate UDF that I would like to use in Entity Framework's linq to SQL. Another function with just one argument works fine. If define two arguments (collection and a string) and try to execute, it fails with exception:

System.Data.Entity.Core.EntityCommandCompilationException: An error occurred while preparing the command definition. See the inner exception for details. ---> System.ArgumentException: The expression list has an incorrect number of elements.
Parameter name: argument
   at System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder.Internal.EnumerableValidator`3.Validate(IEnumerable`1 argument, String argumentName, Int32 expectedElementCount, Boolean allowEmpty, Func`3 map, Func`2 collect, Func`3 deriveName)
   at System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder.Internal.EnumerableValidator`3.Validate()
   at System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder.Internal.ArgumentValidation.CreateExpressionList(IEnumerable`1 arguments, String argumentName, Int32 expectedElementCount, Action`2 validationCallback)
.....

I understand this feature should be implemented by this pull: https://github.com/dotnet/ef6/pull/90 . So what am I missing?

My code:

   

     //Defining function for linq:
        public class CustomSqlFunctions
        {
            [DbFunction("CodeFirstDatabaseSchema", "GROUP_CONCAT_D")]
            public static string GROUP_CONCAT_D(IEnumerable source, string delimeter)
            {
                throw new NotSupportedException("Direct calls are not supported.");
            }
        }
    
    //adding function to model
      public class AddGROUP_CONCAT_DFunction : IStoreModelConvention
        {
            public void Apply(EdmModel item, DbModel dbModel)
            {
                var edmStringType2 = dbModel.ProviderManifest.GetStoreType(TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String))).EdmType;
                CollectionType edmStringListType2 = edmStringType2.GetCollectionType();
                
                string schema = item.Container.EntitySets.Select(s => s.Schema).Distinct().SingleOrDefault();
               
                EdmFunction GROUP_CONCAT_Dfunction = EdmFunction.Create(nameof(CustomSqlFunctions.GROUP_CONCAT_D), "CodeFirstDatabaseSchema", item.DataSpace, new EdmFunctionPayload
                {
                    ParameterTypeSemantics = ParameterTypeSemantics.AllowImplicitConversion,
                    IsAggregate = true,
                    IsComposable = true,
                    StoreFunctionName = nameof(CustomSqlFunctions.GROUP_CONCAT_D),
    
                    Schema = schema,
                    ReturnParameters = new[] { FunctionParameter.Create("ReturnType", edmStringType2, ParameterMode.ReturnValue) },
                    Parameters = new[] {
                        FunctionParameter.Create("input", edmStringListType2, ParameterMode.In),
                        FunctionParameter.Create("delimeter", PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String).GetEdmPrimitiveType(), ParameterMode.In)
                        },
                    
                }, null);
    
                item.AddItem(GROUP_CONCAT_Dfunction);
    
                dbModel.Compile();
            }
    
        }
    
    //in my DbContext  class
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                ...
                modelBuilder.Conventions.Add(new AddGROUP_CONCAT_DFunction());
                ...
            }

Upvotes: 1

Views: 309

Answers (1)

kemsky
kemsky

Reputation: 15279

EF6 does not support aggregate functions with more than one parameter (in reality fix is one line of code but they don't want to do that).

But it is possible to inject constant args using DefaultExpressionVisitor:

protected override DbFunctionAggregate VisitFunctionAggregate(DbFunctionAggregate aggregate)
{
    if (aggregate.Function.FullName == StringAggFunction)
    {
        IList<DbExpression> list = new List<DbExpression> { aggregate.Arguments.Single(), DbExpression.FromString(", ") };

        var args = Activator.CreateInstance(DbExpressionListType, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.CreateInstance, null, new object[] { list }, null);
        var func = (DbFunctionAggregate)Activator.CreateInstance(DbFunctionAggregateType, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.CreateInstance, null, new object[] { aggregate.ResultType, args, aggregate.Function, false }, null);

        return base.VisitFunctionAggregate(func);
    }

    return base.VisitFunctionAggregate(aggregate);
}

Upvotes: 1

Related Questions