Aggregate functions with multiple parameters

[kemsky]

This is a follow up for #85 [Add ability to have custom aggregate functions with multiple parameters].

It became obvious that it was not fully implemented, specifically, I tried to make builtin STRING_AGG function work. This function takes additional argument - separator.

It turns out that System.Data.Entity.Core.Query.PlanCompiler.Normalizer.VisitCollectionAggregateFunction unconditionally drops all argument nodes except the first one (code reference).

I've tried to pass remaining child nodes to the CreateNode method and it worked just fine:

var aggExprNode = m_command.CreateNode(aggregateOp, Enumerable.Repeat(unnestVarRefNode, 1).Concat(n.Children.Skip(1)).ToList());

So this is one-line fix that enables using STRING_AGG which is super useful. I can try to create a PR if possible.

Example:

SELECT 
    [Limit1].[C1] AS [C1]
    FROM ( SELECT TOP (1) 
        (SELECT 
            STRING_AGG([Extent4].[Name], N',') AS [A1]
            FROM  [dbo].[AttorneyLanguages] AS [Extent3]
            LEFT OUTER JOIN [dbo].[Languages] AS [Extent4] ON ([Extent4].[IsDeleted] = 0) AND ([Extent3].[LanguageId] = [Extent4].[Id])
            WHERE ([Extent3].[IsDeleted] = 0) AND ([Project1].[Id] = [Extent3].[AttorneyId])) AS [C1]
        FROM ( SELECT 
            [Extent1].[Id] AS [Id], 
            (SELECT 
                COUNT(1) AS [A1]
                FROM [dbo].[AttorneyLanguages] AS [Extent2]
                WHERE ([Extent2].[IsDeleted] = 0) AND ([Extent1].[Id] = [Extent2].[AttorneyId])) AS [C1]
            FROM [dbo].[Attorneys] AS [Extent1]
            WHERE [Extent1].[IsDeleted] = 0
        )  AS [Project1]
        WHERE [Project1].[C1] > 1
    )  AS [Limit1]

[ajcvickers]

This issue has been closed because EF6 is no longer being actively developed. We are instead focusing on stability of the codebase, which means we will only make changes to address security issues. See the repo README for more information.

[kemsky]

Implemented STRING_AGG without your help, thank you MS.

[joseJositoJosete]

Hi Kemsky
My name is Jose, im from Spain and I don't speak English, I hope you understand me haha.

I need Implement STRING_AGG in EF6 and I see your comments, but I don't understand, can you write more information.

Thanks

[kemsky]

@joseJositoJosete, it is quite complicated, due to multiple issues with EF6.

You need EntityFramework.Functions package or implement your own convention.

   // AggregateFunctions.cs

    [Function(FunctionType.AggregateFunction, "STRING_AGG", StoreFunctionName = "STRING_AGG")]
    [return: Parameter(DbType = "nvarchar(max)", ClrType = typeof(string))]
    public static string StringAgg(this IEnumerable<string> queryable)
    {
        return string.Join(", ", queryable);
    }

// MyContext.cs

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
      // you have to skip this code when DbMigrator or migration generator is running, 
      // by adding some static field or AsyncLocal to your implementation of IDatabaseInitializer<MyContext>.
      if (...) {
            modelBuilder.Conventions.Add(new FunctionConvention(typeof(AggregateFunctions)));
      }
      // ...
}

Implement and register IDbCommandTreeInterceptor :

// DbCommandTreeInterceptorImpl.cs

public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
    {
        if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace && interceptionContext.Result is DbQueryCommandTree queryCommand)
        {
            var originalQuery = queryCommand.Query;

            var dbFunctionsVisitor = new DbFunctionsVisitor();

            var newQuery = originalQuery.Accept(dbFunctionsVisitor);

            interceptionContext.Result = new DbQueryCommandTree(queryCommand.MetadataWorkspace, queryCommand.DataSpace, newQuery);
        }
}

Implement visitor:

// DbFunctionsVisitor.cs

internal sealed class DbFunctionsVisitor : DefaultExpressionVisitor
{
   private static readonly string StringAggFunction = "CodeFirstDatabaseSchema.STRING_AGG";

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

[joseJositoJosete]

Hi
Wou you are a good programmer and good person, everybody told me it's impossible and you did :).

I have some questions, can you help me.i implement your code and send me an error in this line

GroupBy(l => new { l.IdExpediente }).Select(a => new { a.Key.IdExpediente, dd= a.Select(s => s.Investigador.Apellido1).StringAgg() });

"The specified method 'System.String StringAgg(System.Collections.Generic.IEnumerable`1[System.String])' on the type 'ISCIII.AESEG.DAL.AggregateFunctions' cannot be translated into a LINQ to Entities store expression.'"

I think its becauese even OnModelCreating doestn execute, its because i dont use code first , i use edmx file.

In your code you wrote two types "DbFunctionAggregate" and "DbExpressionListType" I don't have these types, and I write "CollectionType" and "AggregateFunctions" , its OK

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

Thanks

[kemsky]

@joseJositoJosete, I can not help you with Edmx, I have never used it.

[joseJositoJosete]

Don't worry you helped me :)

[MrZander]

@joseJositoJosete Were you able to make this work with an EDMX model?

[MrZander]

After much trial and error, I got it to work.

I had to add a manual Function definition to the edmx file, in the SSDL section.

      <Function Name="STRING_AGG" ReturnType="varchar(max)" Aggregate="true" BuiltIn="true" NiladicFunction="false" IsComposable="true" StoreFunctionName="STRING_AGG">
        <Parameter Name="expression" Type="Collection(nvarchar(max))" />
      </Function>

Then I had to make a static function, note the .Store in the namespace:

        [DbFunction("MyEFModel.Store", "STRING_AGG")]
        public static string StringAggregateComma(IEnumerable<string> stringvalues)
        {
            throw new NotSupportedException("Direct calls are not supported");
        }

Then I used the interception that kemsky posted above to inject the second parameter since EF doesn't allow multi-parameter aggregates.
I had to make a minor change with the ", " parameter to force it to be an varchar instead of nvarchar, since our DB only uses varchar.
Also, one last note, if you have any other interceptors, put this one last. EF throws an error complaining about too many arguments if another command tree interceptor visits the tree after this one.

Hopefully this saves someone else 8 hours.

[joseJositoJosete]

Thanks, you are a Good programer :). I resolved create a view in sql server , but your Idea is better . I dont like views. Maybe you have a prolem. If you chage database and update edmx , you will lose the chages. Thanks for all :).

…

________________________________ De: MrZander ***@***.***> Enviado: miércoles, 30 de octubre de 2024 22:41 Para: dotnet/ef6 ***@***.***> Cc: joseJositoJosete ***@***.***>; Mention ***@***.***> Asunto: Re: [dotnet/ef6] Aggregate functions with multiple parameters (Issue #2090) After much trial and error, I got it to work. I had to add a manual Function definition to the edmx file, in the SSDL section. <Function Name="STRING_AGG" ReturnType="varchar(max)" Aggregate="true" BuiltIn="true" NiladicFunction="false" IsComposable="true" StoreFunctionName="STRING_AGG"> <Parameter Name="expression" Type="Collection(nvarchar(max))" /> </Function> Then I had to make a static function, note the .Store in the namespace: [DbFunction("MyEFModel.Store", "STRING_AGG")] public static string StringAggregateComma(IEnumerable<string> stringvalues) { throw new NotSupportedException("Direct calls are not supported"); } Then I used the interception that kemsky posted above to inject the second parameter since EF doesn't allow multi-parameter aggregates. I had to make a minor change with the ", " parameter to force it to be an varchar instead of nvarchar, since our DB only uses varchar. Also, one last note, if you have any other interceptors, put this one last. EF throws an error complaining about too many arguments if another command tree interceptor visits the tree after this one. Hopefully this saves someone else 8 hours. — Reply to this email directly, view it on GitHub<#2090 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BHWZN5MJ6SRBYR3VFILW6ILZ6FG63AVCNFSM6AAAAABQ26OAVOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINBYGQ3DSNRVHE>. You are receiving this because you were mentioned.Message ID: ***@***.***>