ASP.NET Core 3.1 - EF Core can't translate GroupBy and Join

Question

I recently updated my web application and I regret all my most complex requests no longer weigh. I saw that a lot of people had this problem but I don't quite understand how to solve them. I know that other posts talk about that but I don't see how to adapt it to my requests. I have two types of requests that are problematic:

1st problem (solved):

 //error in this part "GroupBy(i => i.CodeMemo).Where(g => g.Count() == 1)"
var MemoMonde = await _context.MemoEmploye.GroupBy(i => i.CodeMemo).Where(g => g.Count() == 1)
                .Select(g => g.FirstOrDefault()).Select(emp => new
                {

                    emp.CodeMemo,
                    emp.Lu,
                    emp.Actif,
                    Memo = new
                    {
                        emp.CodeMemoNavigation.Titre,
                        emp.CodeMemoNavigation.Contenu,
                        emp.CodeMemoNavigation.DateEcheance,
                        emp.CodeMemoNavigation.Urgent,
                        emp.CodeMemoNavigation.CodeEmploye,
                        emp.CodeMemoNavigation.CodeSociete,
                        emp.CodeMemoNavigation.DateCreation,
                        emp.CodeMemoNavigation.ToutLeMonde,
                        emp.CodeMemoNavigation.Supprime,
                        emp.CodeMemo



                    },
                    destinataireE = new
                    {
                        emp.CodeEmployeNavigation.Nom,
                        emp.CodeEmployeNavigation.Prenom,
                        emp.CodeEmployeNavigation.CodeEmploye,
                        emp.CodeEmployeNavigation.Identifiant
                    },
                    employe = emp.CodeMemoNavigation.CodeEmploye != null
                        ? _context.Employe.Select(soc => new { soc.Nom, soc.Prenom, soc.Identifiant, soc.CodeEmploye }).FirstOrDefault(code =>
                            code.CodeEmploye == emp.CodeMemoNavigation.CodeEmploye)
                        : null,

                    societe = emp.CodeMemoNavigation.CodeSociete != null
                        ? _context.Societe.Select(soc => new { soc.Nom, soc.CodeSociete }).FirstOrDefault(code =>
                              code.CodeSociete == emp.CodeMemoNavigation.CodeSociete)
                        : null
                    ,
                    photo = _context.MemoFichier.Select(file => new
                    {
                        file.CodeMemo,
                        file.CodeFichier,
                        file.CodeFichierNavigation.Chemin,
                        file.CodeFichierNavigation.Nom
                    }).Where(fi => fi.CodeMemo == emp.CodeMemo).ToList()
                }).Where(t =>
                    t.destinataireE.CodeEmploye == codeEmploye && !t.Memo.Supprime && t.Actif)
                .OrderByDescending(r => r.Memo.DateCreation).ToListAsync();

Solution to get non-unique value count of table in ef core 3 without GroupBy :

var memoSANS = _context.MemoEmploye.Where(x => x.CodeEmploye != codeEmploye).ToList();
var memoSANS1 = memoSANS.Select(x => x.CodeMemo).ToList();
var memoAVEC = _context.MemoEmploye.Where(x => x.CodeEmploye == codeEmploye && !memoSANS1.Contains(x.CodeMemo)).ToList();
var memoAVEC1 = memoAVEC.Select(x => x.CodeMemo).ToList();
and && memoAVEC1.Contains(t.CodeMemo) to the big request

and the second problem :

var MemoEmp = await _context.MemoEmploye.Select(mr => new
            {
                mr.CodeEmployeNavigation.Identifiant,
                mr.CodeMemo,
                mr.CodeEmployeNavigation.Nom,
                mr.CodeEmployeNavigation.Prenom,
                mr.Lu,
                mr.Actif,
                mr.CodeEmploye,
                Memo = new
                {
                    mr.CodeMemoNavigation.Titre,
                    mr.CodeMemoNavigation.Contenu,
                    mr.CodeMemoNavigation.DateEcheance,
                    mr.CodeMemoNavigation.Urgent,
                    mr.CodeMemoNavigation.CodeEmploye,
                    mr.CodeMemoNavigation.CodeSociete,
                    mr.CodeMemoNavigation.DateCreation,
                    mr.CodeMemoNavigation.ToutLeMonde,
                    mr.CodeMemoNavigation.Supprime,

                    mr.CodeMemoNavigation.CodeMemo
                },
                destinataireE = mr.CodeMemoNavigation.MemoEmploye.Select(a =>
                    new
                    {
                        a.CodeEmployeNavigation.Nom,
                        a.CodeEmployeNavigation.Prenom,
                        a.CodeEmployeNavigation.Identifiant,
                        a.CodeEmployeNavigation.CodeRole,
                        a.CodeEmployeNavigation.CodeEmploye
                    }).ToList(),
                employe = new
                {
                    mr.CodeMemoNavigation.CodeEmployeNavigation.Nom,
                    mr.CodeMemoNavigation.CodeEmployeNavigation.Prenom,
                    mr.CodeMemoNavigation.CodeEmployeNavigation.Identifiant
                },
                societe = mr.CodeMemoNavigation.CodeSociete != null ? _context.Societe.Select(soc => new { soc.Nom, soc.CodeSociete }).FirstOrDefault(code => code.CodeSociete == mr.CodeMemoNavigation.CodeSociete): null
                ,
                photo = _context.MemoFichier.Select(file => new
                {
                    file.CodeMemo,
                    file.CodeFichier,
                    file.CodeFichierNavigation.Chemin,
                    file.CodeFichierNavigation.Nom
                }).Where(fi => fi.CodeMemo == mr.CodeMemo).ToList()
            }).Where(me => me.CodeEmploye == codeEmploye && !me.Memo.Supprime && me.Actif && !MemoMonde.Select(r => r.Memo.CodeMemo).DefaultIfEmpty().Contains(me.Memo.CodeMemo) && !Archi.Select(r => r.Memo.CodeMemo).ToList().Contains(me.Memo.CodeMemo) && me.destinataireE.Count() != 1).ToListAsync();
//error in this part : "!Archi.Select(r => r.Memo.CodeMemo).ToList().Contains(me.Memo.CodeMemo)" & "!MemoMonde.Select(r => r.Memo.CodeMemo).DefaultIfEmpty().Contains(me.Memo.CodeMemo)"
//the error comes from me.destinataireE.Count() != 1 we can't specify condition about a nested request

solved : the error comes from me.destinataireE.Count() != 1 we can't specify condition about a nested request

I just spent hours there without results, I desperately seek help thanks in advance

Answer 1

I know it's an old post that i bumped into, but let us make something clear. When you use a group by in SQL it wants to use a COUNT, SUM, AVG, ... things like that. Creating a pivot result of objects (if i call this by the right name) is something completely different and cannot be done in SQL. BUT you can create a query with pagination and everything and high performance that actually create a pivot result of objects. I'll share my code here of a query that i once created and tested with 1 milion records and it was still a high performance request. So like in the comments before, your query was already bad from the beginning because you should have seen the warnings where everything is loaded into memory and when you have that in scenarios where you have a giant database your program will go very slow.

"Think out of the box"

public async Task<IPage<ITranslationRecord>> GetTranslationRecordsAsync(
        string countryCode,
        string applicationKey,
        bool onlyMissingTranslations,
        string searchItem,
        int pageNumber,
        int pageSize,
        CancellationToken cancellationToken) {
        _logger.LogInformation(
            "{Repository} {Method} query data for Country {CountryCode} and Application {ApplicationKey}.",
            GetType().Name,
            MethodBase.GetCurrentMethod().Name,
            countryCode,
            applicationKey);
        pageNumber -= 1;// The user want to see page 1 but we calculate starting on 0

        if (pageNumber < 0) {
            pageNumber = 0;
        }

        IQueryable<Culture> cultures = _globalizationDbContext.Cultures
            .Where(x => x.Name == "en" || x.Country.TwoLetterCode == countryCode);

        int numberOfCultures = await cultures.CountAsync(cancellationToken);

        IQueryable<Translation> cultureResourceTranslations = GetCultureResourceTranslationsQuery(
            cultures,
            _globalizationDbContext.Resources);

        if (!string.IsNullOrWhiteSpace(searchItem)) {
            cultureResourceTranslations = cultureResourceTranslations.Where(x =>
                x.Resource.ResourceApplication.Name.Contains(searchItem) ||
                x.Resource.ResourceSubject.Name.Contains(searchItem) ||
                x.Resource.Key.Contains(searchItem) ||
                (x.ResourceTranslation != null && x.ResourceTranslation.Value.Contains(searchItem)));
        } else if (!string.IsNullOrWhiteSpace(applicationKey)) {
            cultureResourceTranslations = cultureResourceTranslations.Where(x => x.Resource.ResourceApplication.Name == applicationKey);
        }

        if (onlyMissingTranslations) {
            cultureResourceTranslations = cultureResourceTranslations.Where(x => x.ResourceTranslation == null);
        }

        // This becomes the selected cultures again, empty or not, filtered or not, for every FILTERED resource.
        var resourceTranslations = GetCultureResourceTranslationsQuery(
            cultures,
            cultureResourceTranslations.Select(x => x.Resource))
            .Select(rt => new {
                CultureCode = rt.Culture.Name,
                ResourceApplication = rt.Resource.ResourceApplication.Name,
                ResourceSubject = rt.Resource.ResourceSubject.Name,
                ResourceKey = rt.Resource.Key,
                Parameters = rt.Resource.ResourceExampleParameters.OrderBy(x => x.Index).Select(x => x.Value),
                ModifiedAt = (DateTime?)rt.ResourceTranslation.ModifiedAt,
                Translation = rt.ResourceTranslation.Value
            });

        int numberOfMaximumRecords = (await resourceTranslations.CountAsync(cancellationToken)) / numberOfCultures;
        int numberOfMaximumPages = (numberOfMaximumRecords - (numberOfMaximumRecords % pageSize)) / pageSize;

        if (pageNumber > numberOfMaximumPages) {
            pageNumber = numberOfMaximumPages;
        }

        var result = await resourceTranslations
            .OrderBy(x => x.ResourceApplication)
            .ThenBy(x => x.ResourceSubject)
            .ThenBy(x => x.ResourceKey)
            .Skip(pageNumber * pageSize * numberOfCultures)
            .Take(pageSize * numberOfCultures)
            .ToListAsync(cancellationToken);

        List<ITranslationRecord> translationRecords = result
            .GroupBy(x => new {
                x.ResourceApplication,
                x.ResourceSubject,
                x.ResourceKey
            })
            .Select(x => new TranslationRecord() {
                ResourceApplication = x.Key.ResourceApplication,
                ProductKey = x.Key.ResourceSubject + '.' + x.Key.ResourceKey,
                Parameters = x.Select(y => y.Parameters).First().ToList(),
                Translations = x
                    .Select(y => new TranslationRecordPair() {
                        CultureCode = y.CultureCode,
                        Translation = y.Translation
                    } as ITranslationRecordPair)
                    .ToList()
            } as ITranslationRecord)
            .ToList();

        return new Page<ITranslationRecord>(pageNumber + 1, numberOfMaximumRecords, numberOfMaximumPages + 1, translationRecords);
    }

    private IQueryable<Translation> GetCultureResourceTranslationsQuery(
        IQueryable<Culture> cultures,
        IQueryable<Resource> resources) {
        var cultureResources =
           from r in resources.Distinct()
           from c in cultures
           select new {
               Resource = r,
               Culture = c
           };

        return from rc in cultureResources
               from rt in _globalizationDbContext.ResourceTranslations
                   .Where(x => x.CultureId == rc.Culture.Id)
                   .Where(x => x.ResourceId == rc.Resource.Id)
                   .DefaultIfEmpty()
               select new Translation {
                   Culture = rc.Culture,
                   Resource = rc.Resource,
                   ResourceTranslation = rt
               };
    }