How to create Stuff and XML Path in EFCORE 5.0?

[trickyrat]

Hi, Community.

I have three tables with many-to-many relationships.
Content -> ContentChannel->Channel
I want to get a content with its all channels in a row.
I tried the following code

	var query = from content in contentQuery
			join contentChannel in contentChannelQuery on content.Id equals contentChannel.ContentId
			join channel in channelQuery on contentChannel.ChannelId equals channel.Id
			group channel by content into g
			select new 
			{
				Content = g.Key,
				Channels = string.Join("|", g.Select(y => y.ChannelName))
			};

But throwed

System.InvalidOperationException: The LINQ expression 'DbSet<Content>()
    .Join(
        inner: DbSet<ContentChannel>(), 
        outerKeySelector: c => c.Id, 
        innerKeySelector: c0 => c0.ContentId, 
        resultSelector: (c, c0) => new TransparentIdentifier<Content, ContentChannel>(
            Outer = c, 
            Inner = c0
        ))
    .Join(
        inner: DbSet<Channel>(), 
        outerKeySelector: ti => ti.Inner.ChannelId, 
        innerKeySelector: c1 => c1.Id, 
        resultSelector: (ti, c1) => new TransparentIdentifier<TransparentIdentifier<Content, ContentChannel>, Channel>(
            Outer = ti, 
            Inner = c1
        ))
    .GroupBy(
        keySelector: ti0 => ti0.Outer.Outer, 
        elementSelector: ti0 => ti0.Inner)' 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

And the same code works in efcore 2.2
So I want to know what I should do to achieve this functionality in efcore 5.0.
Thankes

[AndriySvyryd]

@smitpatel cc

[smitpatel]

string.Join causes client side evaluation over elements of grouping. This differs from SQL GROUP BY hence not translated. See https://docs.microsoft.com/en-us/ef/core/querying/complex-query-operators#groupby to learn more about supported GroupBy scenarios. Since this particular query is not generating results of type IGrouping, #13805 may be able to translate it.