Using string_agg in the many-to-many relation-CodePudding

I have entities like Product(Id, Name) and Keyword(Id, Description), and there is a many-to-many relationship between them.

The essence of my task is the following, I need to do a full-text search on Name and Description columns, using EF CORE 6

I already have some SQL code that works fine.

SELECT a."Id", a."Name" as name, k.txt
FROM "Products" AS a
LEFT JOIN (
    SELECT x."ProductsId" as Id, string_agg(y."Description", ' ') as txt
    FROM "ProductKeywords" x
    JOIN "Keywords" y ON y."Id" = x."KeywordId"
    GROUP BY 1
) k ON a."Id" = k.Id
WHERE  to_tsvector(concat_ws(' ', a."Name", k.txt))
    @@ to_tsquery('Some text');

And I need to write some LINQ code that will do something similar, but I have a problem with string_agg, and I don't understand how to implement it in LINQ and EF CORE will reflect it correctly

I tried to do the following

var products = _context.Products
            .Select(e => new
            {
                Id = e.Id,
                Name = e.Name,
                Keywords = string.Join(" ", e.Keywords.Select(q => q.Description))
            }).Where(e => EF.Functions.ToTsVector(e.Keywords).Matches("Some text")).ToList();

But I get an error, and it's most likely because of string.Join

could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'

CodePudding user response：

Got the result, using linq2db

var query = _context.Products.ToLinqToDB()
    .LeftJoin(_context.ProductsKeywords.ToLinqToDB().GroupBy(r => r.ProductId).Select(e => new {
            Key = e.Key,
            Txt = e.StringAggregate(",", t => t.Keyword.Description).ToValue()
        }),
        (product, productKeyword) => product.Id == productKeyword.Key,
        (i, j) => new {
            Id = i.Id,
            Txt = j.Txt
        }).Select(e => new {
            Id = e.Id,
            Txt = EF.Functions.ToTsVector(e.Txt)
    }).Where(w => w.Txt.Matches("Some text"));