Is supported string aggregates on Entity Framework Core using NPGSQL?

Question

I'm trying to translate this PostgreSQL query to Linq using Npgsql:

var result = _dbContext.Substrates.FromSql(@$"SELECT 
                                                vs.*,
                                                STRING_AGG(DISTINCT p.product_ref, ', ') AS product_ref,
                                                STRING_AGG(DISTINCT cu.acr, ', ') AS acr
                                            FROM 
                                                portal_data.v_substrates vs
                                            LEFT JOIN portal_data.custart c ON c.article_id = vs.id_substrate
                                            LEFT JOIN portal_data.product_components pc ON pc.id_component = vs.id_substrate
                                            LEFT JOIN portal_data.products p ON p.id_product = pc.id_product
                                            LEFT JOIN portal_data.customers cu ON cu.id_customer = c.id_customer
                                            WHERE
                                                c.id_customer = {customerId}
                                            GROUP BY
                                                vs.id_substrate,
                                                vs.substrate_type,
                                                vs.substrate_creation_date,
                                                vs.substrate_status,
                                                vs.substrate_invent_qty,
                                                vs.record_copy_date,
                                                vs.mapped_status
                                            ");

As per the Npgsql documentation there is a translation function called EF.Functions.StringToArray() that, if I understood correctly, translates to string_agg(s, '|').

var substrates = from substrate in _dbContext.Substrates
                 join custart in _dbContext.CustomerArticles on substrate.IdSubstrate equals custart.ArticleId
                 join component in _dbContext.ProductComponents on substrate.IdSubstrate equals component.IdComponent
                 join product in _dbContext.Products on component.IdProduct equals product.IdProduct
                 join customer in _dbContext.Customers on custart.IdCustomer equals customer.Id                              
                 where custart.IdCustomer == customerId
                 select new SubstrateExtended
                 {
                     IdSubstrate = substrate.IdSubstrate,
                     SubstrateType = substrate.SubstrateType,
                     SubstrateCreationDate = substrate.SubstrateCreationDate,
                     SubstrateStatus = substrate.SubstrateStatus,
                     SubstrateInvetoryQuantity = substrate.SubstrateInvetoryQuantity,
                     RecordCopyDate = substrate.RecordCopyDate,
                     MappedStatus = substrate.MappedStatus,
                     ProductRef = EF.Functions.StringToArray(product.ProductRef, ", "),
                     Acr = EF.Functions.StringToArray(customer.Accronym, ", ")
                 };

But this doesn't work because EF.Functions.StringToArray() is returning an string[].

Is there a way to translate the SQL query to Linq without retrieve the full list of items for aggregation?

Answer 1

The equivalent is string.Join, where the second parameter is IEnumerable<string>.

But you are also missing a group by, and your joins in the original query were left join.

var substrates =
    from substrate in _dbContext.Substrates
    join custart1 in _dbContext.CustomerArticles on substrate.IdSubstrate equals custart.ArticleId into j1 from custart in j1.DefaultIfEmpty()
    join component1 in _dbContext.ProductComponents on substrate.IdSubstrate equals component.IdComponent into j2 from component in j2.DefaultIfEmpty()
    join product1 in _dbContext.Products on component.IdProduct equals product.IdProduct into j3 from product in j3.DefaultIfEmpty()
    join customer1 in _dbContext.Customers on custart.IdCustomer equals customer.Id into j4 from customer in j4.DefaultIfEmpty()
    where custart.IdCustomer == customerId
    group new { custart, component, product, customer } by substrate into g
    select new SubstrateExtended
    {
        IdSubstrate = substrate.IdSubstrate,
        SubstrateType = substrate.SubstrateType,
        SubstrateCreationDate = substrate.SubstrateCreationDate,
        SubstrateStatus = substrate.SubstrateStatus,
        SubstrateInvetoryQuantity = substrate.SubstrateInvetoryQuantity,
        RecordCopyDate = substrate.RecordCopyDate,
        MappedStatus = substrate.MappedStatus,
        ProductRef = string.Join(", ", g.Select(t => t.product.ProductRef).Distinct()),
        Acr = string.Join(", ", g.Select(t => t.customer.Accronym).Distinct())
    };

Having said that, it's probably better to rethink your joins and remove the distinct and group by, by using subqueries to pre-aggregate.

var substrates =
    from substrate in _dbContext.Substrates
    where custart.IdCustomer == customerId

    let acr = string.Join(", ",
        from custart in _dbContext.CustomerArticles
        join customer1 in _dbContext.Customers on custart.IdCustomer equals customer.Id
        where substrate.IdSubstrate == custart.ArticleId
        select customer.Accronym
    )

    let productRef = string.Join(", ",
        from component1 in _dbContext.ProductComponents
        join product1 in _dbContext.Products on component.IdProduct equals product.IdProduct
        where substrate.IdSubstrate == component.IdComponent
        select product.ProductRef
    )

    select new SubstrateExtended
    {
        IdSubstrate = substrate.IdSubstrate,
        SubstrateType = substrate.SubstrateType,
        SubstrateCreationDate = substrate.SubstrateCreationDate,
        SubstrateStatus = substrate.SubstrateStatus,
        SubstrateInvetoryQuantity = substrate.SubstrateInvetoryQuantity,
        RecordCopyDate = substrate.RecordCopyDate,
        MappedStatus = substrate.MappedStatus,
        ProductRef = productRef,
        Acr = acr
    };