EF Core 5 Grouping subquery under join throws exception

[vovapabyr]

Hello,

After GroupBy() subquery inside .Join() throws the following exception:

Unable to translate collection subquery in projection since the parent query doesn't project key columns of all of it's tables which are required to generate results on client side. This can happen when trying to correlate on keyless entity or when using 'Distinct' or 'GroupBy' operations without projecting all of the key columns.

We have a file storage system, represented as a regular tree-like structure. Each file can have any number of tags attached to it.
We should be able to search files based on tags. Users can select a number of tags and as a result, they should get a list of files that at least have one of the tags. Here are the domain entities:

public class Data
{
    public string Id { get; set; }
    public string Name { get; set; }
    ...

    public virtual ICollection<Tag> Tags { get; set; }
    ...
}

public class Tag
{
    public string DataId { get; set; }
    public string Value { get; set; }
  
    public virtual Data Data { get; set; }
}

Where (Id) column is the primary key of the Data table and (DataId, Value) is the composite primary key of the Tags table.
And here is the code with which we try to search data based on tags:

var tags = searchModel.Tags.ToList();
var tagsQuery = _context.DataTags.Where(t => tags.Contains(t.Value)).GroupBy(t => t.DataId).Select(gr => gr.Key);
var dataSearchQuery = _context.Data.Join(tagsQuery, d => d.Id, dataId => dataId, (d, dataId) => d);

As you can see we don't group on all key columns, that's why I believe we get this exception and it relates to the breaking change introduced in ef core 5 link. But, the problem is that grouping is critical over here to avoid duplication of data in the search result (when there is a data which has more than one target tags). Also, we need to do it on the server-side as we have millions of files and the above code is part of the server-side pagination which happens further.
Is there any workaround to this problem? Would really appreciate any help.

Many thanks!

EF Core version: 5.0.2
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
IDE: Visual Studio 2019 16.10.4

[ajcvickers]

@maumar to find dupe.

[maumar]

dupe of #22049