GROUP BY and string CONCAT #26838
Description
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