GROUP BY and string CONCAT

[SaurabhHarwande]

I am trying to write a Query which contains group by and Concatenates a VARCHAR column per Grouping. But I am not able to write a query which is completely translated to SQL. Any solutions/workaround that can help us with this?

The code is as follows

public static class EfExtensions
{
    [DbFunction("GROUP_CONCAT")]
    public static string GroupConcat(this IEnumerable<string> values, Func<string, string> func) => throw new Exception();
}
public class InvoicePaymentServiceModelBuilder : IModelBuilder
{
    public void Build(ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasDbFunction(typeof(EfExtensions).GetMethod(nameof(EfExtensions.GroupConcat)))
            .HasTranslation(args =>
            {
                return new SqlFunctionExpression("group_concat", typeof(string), args);
            });
    }
}

var runningBalanceDetails =
(
    from ot in _db.Set<OtherTransaction>()
    join tt in _db.Set<CustomerTransactionType>()
        on ot.TransactionTypeId equals tt.Id
    join acc in _db.Set<Account>()
        on ot.AccountNumber equals acc.AccountNumber
    join s in _db.Set<Company>().AllSupplierAliases(_db)
        on acc.SupplierId equals s.Id
    join mipd in _db.Set<MeasuringPointInvoicingPointDetail>()
        on acc.InvoicePointId equals mipd.InvoicingPointId
    join cl in _db.Set<CompanyLocationDetail>()
        on mipd.CompanyLocationId equals cl.Id
    where
        (filters.Accounts == null || filters.Accounts.Count == 0 || filters.Accounts.Contains(ot.AccountNumber)) &&
        (filters.Suppliers == null || filters.Suppliers.Count == 0 || filters.Suppliers.Contains(acc.SupplierId)) &&
        (filters.Sites == null || filters.Sites.Count == 0 || filters.Sites.Contains(mipd.CompanyLocationId)) &&
        ot.TaxPointDate.Date <= ToDate &&
        ot.CustomerID == filters.CustomerID
    group cl.Name
    by new
    {
        ot.Id,
        ot.AccountNumber,
        ot.TaxPointDate,
        ot.Value,
        ot.Description,
        acc.SupplierId,
        SupplierName = s.Name,
        SupplierLogoFileFormat = s.LogoFileFormat,
        tt.TransactionType
    }
    into _ot
    select new RunningBalanceDetail
    {
        AccountNo = _ot.Key.AccountNumber,
        SupplierId = _ot.Key.SupplierId,
        SupplierName = _ot.Key.SupplierName,
        SupplierLogo = _ot.Key.SupplierLogoFileFormat,
        TransactionDate = _ot.Key.TaxPointDate,
        TransactionValue = _ot.Key.Value,
        TransactionType = _ot.Key.TransactionType,
        MainTransactionType = ModuleWiseTransactionType.OtherTransaction.GetDisplayName(),
        Reference = _ot.Key.Description,
        //-----------------------------------------------
        CompanyLocationName = string.Join(",", _ot.Select(x => x)),
        // OR THIS INSTEAD
        CompanyLocationName = _ot.GroupConcat(x => x)),
        //-----------------------------------------------
        ReferenceId = _ot.Key.Id
    }
).ToList();

Expected behaviour: The string.Join or GroupConcat query should be translated to SQL as group_concat(cl.Name)

Actual behaviour: The Query is not translated and functions are executed locally.

EF Core version:
Database provider: Pomelo.EntityFrameworkCore.MySql
Target framework: netcoreapp2.1
Operating system: Windows 10
IDE: Microsoft Visual Studio Professional 2019 Version 16.11.7

[roji]

@SaurabhHarwande you cannot currently use custom aggregate methods in EF Core - support for that is tracked by #22957, and is in the plan for the 7.0.0 release. You definitely won't be able to use that on netcoreapp2.1 though.

[roji]

Duplicate of #22957

[SaurabhHarwande]

Thanks @roji