EntityFramework Core LINQ expression unable to be translated to SQL-CodePudding

I'm attempting to make a nested group join (category > subcategory > item). And return all of that data through my controller as lists of data below one another, like:

[
    {
        "name":"cat1",
        "subcategories":[
            {
                "name":"subcat1",
                "items":[
                    {
                        "name":"item1"
                    }
                ]
            }
        ]
    }
]

However the query I'm attempting to write is unable to be converted to SQL. It keeps saying it cannot be translated to SQL because of a client evaluation issue. I've checked the documentation about client evaluation but I can't seem to find any plausible cause as to why this is still happening. Supposedly it's because of code which cannot be transformed into SQL and has to be executed on the client itself, which needs to be configured explicitly, I want to avoid this if at all possible and execute the entire query on the server at once.

Is it even possible in the way I'm attempting to run it (with 2 GroupJoins nested), or do I need to switch to another way?

var categories = Db.WorkListCategories
    .Where(w => w.CompanyId == null || w.CompanyId.Value == companyId);

var subCategories = Db.WorkListSubCategories
    .Where(w => w.Category!.CompanyId == null || w.Category!.CompanyId.Value == companyId);

var workLists = Db.WorkLists
    .Where(w => w.SubCategory!.Category!.CompanyId == null || w.SubCategory.Category.CompanyId.Value == companyId);

return await categories
    .GroupJoin(subCategories, o => o.Id, i => i.CategoryId, (category, subCats) => new WorkListCategoryFull<WorkListSubCategoryFull>
    {
        Id = category.Id,
        Name = category.Name,
        IsEditable = category.IsEditable,
        SubCategories = subCats
            .GroupJoin(workLists, o => o.Id, i => i.SubCategoryId, (subCategory, workLists) => new WorkListSubCategoryFull
            {
                Id = subCategory.Id,
                Name = subCategory.Name,
                WorkLists = workLists
                    .Select(s1 => new WorkList
                    {
                        Id = s1.Id,
                        Name = s1.Name
                    })
                    .ToList()
            })
            .ToList()
    })
    .ToListAsync(cancellationToken);

For better visualization I've added the schema as an image below:

CodePudding user response：

GroupJoin is less supported operator in EF Core. Use it only for LEFT JOIN. Your query can be rewritten in more simple way:

...

return await categories
    .Select(category => new WorkListCategoryFull<WorkListSubCategoryFull>
    {
        Id = category.Id,
        Name = category.Name,
        IsEditable = category.IsEditable,
        SubCategories = subCategories
            .Where(subCategory => subCategory.CategoryId == category.Id)
            .Select(subCategory => new WorkListSubCategoryFull
            {
                Id = subCategory.Id,
                Name = subCategory.Name,
                WorkLists = workLists
                    .Where(wl => subCategory.Id == wl.SubCategoryId)
                    .Select(wl => new WorkList
                    {
                        Id = wl.Id,
                        Name = wl.Name
                    })
                    .ToList()
            })
            .ToList()
    })
    .ToListAsync(cancellationToken);