none
EF eager loading in complex query RRS feed

  • Question

  • I've been struggling with this for a couple of days, I have a pretty complex EF query and it,s working well with lazy loading but gives me performance issues. i'm trying to use eager loading, but two children entities are not being loaded and I can't find a way to include them. The entities which are not loading are Section1Sujet and Section2Description which are childrens of Section1 and Section2. The query object contains filter criteria. If I place the statement not to use lazy loading, the children are not loading, if not, the query is OK. Here's the query:

    var part1 =
                            (from plainte in CurrentDbContext.Plaintes
                             join serv in CurrentDbContext.Services
                                 on plainte.ServiceRecevantID equals serv.ID
    
                             join plainteCom in CurrentDbContext.PlainteCommentaires on plainte.ID equals plainteCom.PlainteID into plcom
    
    
                             /* ***** Sujets ************************************** */
                             join ps1 in CurrentDbContext.Section1 on plainte.ID equals ps1.PlainteID into sec1res
                             from sec1Results in sec1res.DefaultIfEmpty()
                             from s1s in sec1Results.Sujets.DefaultIfEmpty()
    
                             join ps2 in CurrentDbContext.Section2 on plainte.ID equals ps2.PlainteID into sec2res
                             from sec2Results in sec2res.DefaultIfEmpty()
                             from s2s in sec2Results.Sujets.DefaultIfEmpty()
    
    
                             /* ************************************************* */
                             join ps3 in CurrentDbContext.Section3 on plainte.ID equals ps3.PlainteID into sec3res
                             from sec3Results in sec3res.DefaultIfEmpty()
    
                             join ps6 in CurrentDbContext.Section6 on plainte.ID equals ps6.PlainteID into sec6res
                             from sec6Results in sec6res.DefaultIfEmpty()
                             from s6t in sec6Results.Section6Traitement.DefaultIfEmpty()
    
                             where (plainte.Date.HasValue) && (plainte.Numero.Trim().Length > 0) && (plainte.Statut.Trim().Length > 0)
    
                              && ((string.IsNullOrEmpty(query.NoPlainte)) || ((!string.IsNullOrEmpty(query.NoPlainte)) && (plainte.Numero.Contains(query.NoPlainte.Trim()))))
    
                              && (plainte.Date >= query.StartDate && plainte.Date <= query.EndDate)
    
                              && ((string.IsNullOrEmpty(query.CodeCollecte)) || ((!string.IsNullOrEmpty(query.CodeCollecte)) && (plainte.CodeCollecte == query.CodeCollecte.Trim())))
    
                             /* ***** Categorie de la plainte ************************************** */
                             && ((query.SelectedCategorie == CategoriePlainte.Inconnu) || ((query.SelectedCategorie != CategoriePlainte.Inconnu) && (plainte.Categorie == query.SelectedCategorie.ToString())))
    
                             /* ***** Statut de la plainte - a une incidence sur le service a chercher ************************************** */
                             && ((query.SelectedStatutPlainte == StatutPlainte.Inconnu) || ((query.SelectedStatutPlainte != StatutPlainte.Inconnu) && (plainte.Statut == query.SelectedStatutPlainte.ToString())))
                             /* ***** Service ************************************** */
                             && ((query.SelectedServiceId <= 0)
                                 ||
                                 ((query.SelectedServiceId > 0) && (query.SelectedStatutPlainte == StatutPlainte.EnCreation) && (plainte.ServiceRecevantID == query.SelectedServiceId))
                                 ||
                                 ((query.SelectedServiceId > 0) && (query.SelectedStatutPlainte == StatutPlainte.EnCategorisation) && (sec3Results.ServiceID == query.SelectedServiceId))
                                 ||
                                 ((query.SelectedServiceId > 0) && (query.SelectedStatutPlainte == StatutPlainte.EnTraitement) && (sec3Results.ServiceID == query.SelectedServiceId))
                                 ||
                                 ((query.SelectedServiceId > 0) && (query.SelectedStatutPlainte == StatutPlainte.EnApprobation) && (sec3Results.ServiceID == query.SelectedServiceId))
                                 ||
                                 (
                                     ((query.SelectedServiceId > 0) && (query.SelectedStatutPlainte == StatutPlainte.Inconnu) || (query.SelectedStatutPlainte == StatutPlainte.Abandon) || (query.SelectedStatutPlainte == StatutPlainte.Fermee))
                                     &&
                                     ((plainte.ServiceRecevantID == query.SelectedServiceId) || (sec3Results.ServiceID == query.SelectedServiceId) || (s6t.ServiceID == query.SelectedServiceId))
                                 )
                                )
                             //************ Recherche par sujet -- section 1 ou section 2
                             && ((query.SelectedSujetId <= 0) ||
                                 (((query.SelectedSujetId > 0) && ((s1s != null) && (s1s.ID == query.SelectedSujetId)))
                                 ||
                                 ((query.SelectedSujetId > 0) && ((s2s != null) && (s2s.ID == query.SelectedSujetId))))
                             )
                             //**************************
                             orderby plainte.Date descending
    
                             select new SearchPlainteResult()
                             {
                                 StatutText = plainte.Statut,
                                 PlainteComs = plcom,
                                // Sujets = subjects,
                                 Sec1 = sec1Results,
                                 Sec2 = sec2Results,
                                 CategorieText = plainte.Categorie,
                                 DateCreation = plainte.Date,
                                 DateChangementStatut = plainte.DateChangementStatut,
                                 Numero = plainte.Numero,
                                 PlainteId = plainte.ID,
                                 ServiceId = (int)(((plainte.Statut == StatutPlainte.EnCreation.ToString()) && (plainte.ServiceRecevantID != null)) ? plainte.ServiceRecevantID :
                                                ((plainte.Statut == StatutPlainte.EnCategorisation.ToString()) && (sec3Results.ServiceID != null)) ? sec3Results.ServiceID :
                                                ((plainte.Statut == StatutPlainte.EnTraitement.ToString()) && (sec3Results.ServiceID != null)) ? sec3Results.ServiceID :
                                                ((plainte.Statut == StatutPlainte.EnApprobation.ToString()) && (sec3Results.ServiceID != null)) ? sec3Results.ServiceID : plainte.ServiceRecevantID),
                                 Service = (from s in CurrentDbContext.Services
                                            where s.ID == (int)(((plainte.Statut == StatutPlainte.EnCreation.ToString()) && (plainte.Statut != null)) ? plainte.ServiceRecevantID :
                                                ((plainte.Statut == StatutPlainte.EnCategorisation.ToString()) && (sec3Results.ServiceID != null)) ? sec3Results.ServiceID :
                                                ((plainte.Statut == StatutPlainte.EnTraitement.ToString()) && (sec3Results.ServiceID != null)) ? sec3Results.ServiceID :
                                                ((plainte.Statut == StatutPlainte.EnApprobation.ToString()) && (sec3Results.ServiceID != null)) ? sec3Results.ServiceID : plainte.ServiceRecevantID)
                                            select s.Texte).FirstOrDefault(),
                                 StatutDelais = PlainteStatutDelais.Unknown
                             }).ToList() //subquery pour grouper les commentaire avec un join
                             .Select(q => new SearchPlainteResult()
                              {
                                 
                                  StatutText = q.StatutText,
                                  PlainteCom = String.Join(" | ", q.PlainteComs.Select(n => n.Texte)),
                                  SujetS1 = (q.Sec1 == null ? "": String.Join(" | ", q.Sec1.Sujets.Select(s => s.Texte))),
                                  DescS2 = (q.Sec2 == null ? "" : String.Join(" | ", q.Sec2.Section2Description.Select(s => s.Texte))),
                                  Description = ( q.Categorie == CategoriePlainte.Commentaire ? String.Join(" | ", q.PlainteComs.Select(n => n.Texte)) :
                                                  q.Categorie == CategoriePlainte.InsatisfactionCat2 ? (q.Sec1 == null ? "" : String.Join(" | ", q.Sec1.Sujets.Select(s => s.Texte))) :
                                                  (q.Sec2 == null ? "" : String.Join(" | ", (from s in q.Sec2.Section2Description where s.Texte.Trim() != "" select s.Texte.Trim())))).Left(40),
                                  DescTooLong = (q.Categorie == CategoriePlainte.Commentaire ? String.Join(" | ", q.PlainteComs.Select(n => n.Texte)) :
                                                  q.Categorie == CategoriePlainte.InsatisfactionCat2 ? (q.Sec1 == null ? "" : String.Join(" | ", q.Sec1.Sujets.Select(s => s.Texte))) :
                                                  (q.Sec2 == null ? "" : String.Join(" | ", (from s in q.Sec2.Section2Description where s.Texte.Trim() != "" select s.Texte.Trim())))).Length > 40 ? true : false,
                                  CategorieText = q.CategorieText,
                                  CategorieCompress = (q.Categorie == CategoriePlainte.Commentaire ? "C" :
                                                   q.Categorie == CategoriePlainte.Indetermine ? "I" :
                                                   q.Categorie == CategoriePlainte.InsatisfactionCat1 ? "I1" :
                                                   q.Categorie == CategoriePlainte.InsatisfactionCat2 ? "I2" :
                                                   q.Categorie == CategoriePlainte.Qualite ? "Q" : "IC"),
                                 DateCreation = q.DateCreation,
                                  DateChangementStatut = q.DateChangementStatut,
                                  //Numero = q.Numero.Substring(5,q.Numero.Length - 5),
                                  Numero = q.Numero,
                                  PlainteId = q.PlainteId,
                                  Service = q.Service,
                                  ServiceId = q.ServiceId,
    
                              });
    


    Michel

    Monday, July 4, 2016 2:07 PM

Answers

  • Hi Michel Bernier,

    >>I have a pretty complex EF query and it,s working well with lazy loading but gives me performance issues. i'm trying to use eager loading, but two children entities are not being loaded and I can't find a way to include them.

    According to your description, it seems that you want to use eager loading in your complex query. As we know, eager loading is the process whereby a query for one type of entity also loads related entities as part of the query. Eager loading is achieved by use of the Include method.

    I create a simple as below for your reference.

    var part1 = from p in db.Employees
                                join ps1 in db.Employers.Include("JobPost") on p.EmployeeID equals ps1.Id into sec1res
                                from sec1Results in sec1res.DefaultIfEmpty()
                                select new
                                {
                                    id = p.EmployeeID,
                                    tes = sec1Results.FullName,
                                    jps = sec1Results.JobPosts
                                };
    
                    foreach (var item in part1)
                    {
                        Console.WriteLine("{0}--{1}", item.id, item.tes);
                        foreach (var it1 in item.jps)
                        {
                            Console.WriteLine("{0}", it1.Title);
                        }
                    }
                    Console.ReadKey();

    For more information about eager loading, please refer to

    https://msdn.microsoft.com/en-sg/data/jj574232.aspx

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 5, 2016 2:15 AM
    Moderator