locked
GroupJoin has flat results RRS feed

  • Question

  • User-323149085 posted

    Hello,

     im trying to query db (using EF core ) a table that has a child and the cild has a child , the results im getting are instead of "tree"  (x)1 father>3(x) sun>(x)1 grandson

    im getting  father  3 times and the proper grandson for each son 

    code: 

     public class QuettaReq
        {
            public QuettaReq()
            {
            }
    
            [Key] public int             Id         { get; set; }
            public       ApplicationUser User       { get; set; }
           //Properties 
            public virtual List<QuoteQuestion>  QuoteQuestions { get; set; }
    }
     public class QuoteQuestion
        {
            [Key]
            public int QuoteQuestionId { get; set; }
          
            public int QuoteId         { get; set; }
            public QuettaReq QuettaReq { get; set; }
    
            public string Question { get; set; }
            public IList<Answers> Answers { get; set; }
    
        }
     public class Answers
        {
            public int AnswersId { get; set; }
            public int QuoteQuestionId { get; set; }
            public string Answer { get; set; }
    
        }

    ViewModels

     public class ReqestWithQA
        {
            [Key] public int Id { get; set; }
           //Properties
        
    
            public virtual IList<QAViewModel>  QAViewModel { get; set; }
            //public virtual IList<AnsweToSeller> AToBuyer { get; set; }
            public int    QuettaReqId { get; set; }
            public string Question    { get; set; }
            
        }
     public class QAViewModel
        {
            public int QuoteQuestionId { get; set; }
            public int QuoteId { get; set; }
            public string Question { get; set; }
    
            public IList<Answers> Answers { get; set; }
            }
    

    Controller 

       IQueryable<ReqestWithQA>
                                viewModel =   _context.Quetta.Include(q => q.Category)
                                    .Where(d => d.OfferDate > DateTime.Now && d.CatId == suplayerCat)
                                    .Where(rq => !postOn.Contains(rq.Id)).Distinct()  
                                    .GroupJoin(
                                        _context.quoteQuestions.Include(t =>
                                            t.Answers),
                                        quetta => quetta.Id,
                                        qestion => qestion.QuoteId, (quetta, joinQestionQuetta) => new ReqestWithQA
                                        {
                                            ReqText         = quetta.ReqText,
                                            Qentity         = quetta.Qentity,
                                            CatId           = quetta.CatId,
                                            CatName         = quetta.Category.CatName,
                                            District        = quetta.District,
                                            ApplicationDate = quetta.ApplicationDate,
                                            DeliveryDate    = quetta.DeliveryDate,
                                            OfferDate       = quetta.OfferDate,
                                            TecnicalDetails = quetta.TecnicalDetails,
                                            Bedget          = quetta.Bedget,
                                            Id              = quetta.Id,
                                            QAViewModel = new[]
                                            {
                                                joinQestionQuetta
                                                    .Select(q => new QAViewModel
                                                    {
                                                        Question = q.Question,
                                                        Answers  = q.Answers,
                                                        QuoteId  = q.QuoteId
                                                    }).FirstOrDefault()
                                            }
                                        });// should be one quetta with 3 question & one question answer  instead 3 results each has one question .. 

    Thanks in advance 

    Tuesday, September 18, 2018 1:20 PM

Answers

  • User-271186128 posted

    hi john_mm,

    john_mm

    Im trying to  achieve as follow : Quetta (EG id=4) has multi question (EG id=2 , & id=3 (FK Quetta Id=4 ) & answer (Ed id=1 & id=3 FK question id= 2)

    According to your model, you have already configure one to many relationship for them. So, I suppose there is no need to use groupjoin to join the table, you could use the Include() and ThenInclude() method to get the related entities. Please refer to the following sample:

    1. Base on the following models (using your models, just add a 'virtual' in the QuoteQuestion class) to create database: 

        public class QuettaReq
        {
            public QuettaReq()
            {
            }
    
            [Key] public int Id { get; set; }
            public string UserName { get; set; }
            //Properties 
            public virtual List<QuoteQuestion> QuoteQuestions { get; set; }
        }
        public class QuoteQuestion
        {
            [Key]
            public int QuoteQuestionId { get; set; }
    
            public int QuoteId { get; set; }
            public QuettaReq QuettaReq { get; set; }
    
            public string Question { get; set; }
            public virtual IList<Answers> QuesAnswers { get; set; }
    
        }
        public class Answers
        {
            public int AnswersId { get; set; }
            public int QuoteQuestionId { get; set; }
            public string Answer { get; set; }
    
        }

    2. Initialized data: by default, the primary key is identity, there is no need to set value.

                IList<QuettaReq> QuettaReq = new List<QuettaReq>()
                {
                    new QuettaReq(){ UserName ="A",
                        QuoteQuestions = new List<QuoteQuestion>()
                        {
                            new QuoteQuestion(){  Question = "Question 1",
                                QuesAnswers = new List<Answers>()
                                {
                                    new Answers(){   Answer="Q1_Answer1" },
                                    new Answers(){ Answer="Q1_Answer2" },
                                    new Answers(){  Answer="Q1_Answer3" },
                                }
                            },
                            new QuoteQuestion(){  Question = "Question 2",
                                QuesAnswers = new List<Answers>()
                                {
                                    new Answers(){  Answer="Q2_Answer1" },
                                    new Answers(){  Answer="Q2_Answer2" },
                                    new Answers(){  Answer="Q2_Answer3" },
                                }
                            },
                            new QuoteQuestion(){  Question = "Question 3",
                                QuesAnswers = new List<Answers>()
                                {
                                    new Answers(){  Answer="Q3_Answer1" },
                                    new Answers(){  Answer="Q3_Answer2" },
                                    new Answers(){  Answer="Q3_Answer3" },
                                }
                            }
                        }
                    },
    
                    new QuettaReq(){  UserName ="B",
                        QuoteQuestions = new List<QuoteQuestion>()
                        {
                            new QuoteQuestion(){  Question = "Question 4",
                                QuesAnswers = new List<Answers>()
                                {
                                    new Answers(){ Answer="Q4_Answer1" },
                                    new Answers(){ Answer="Q4_Answer2" },
                                    new Answers(){ Answer="Q4_Answer3" },
                                }
                            },
                            new QuoteQuestion(){  Question = "Question 5",
                                QuesAnswers = new List<Answers>()
                                {
                                    new Answers(){  Answer="Q5_Answer1" },
                                    new Answers(){  Answer="Q5_Answer2" },
                                    new Answers(){  Answer="Q5_Answer3" },
                                }
                            },
                            new QuoteQuestion(){  Question = "Question 6",
                                QuesAnswers = new List<Answers>()
                                {
                                    new Answers(){  Answer="Q6_Answer1" },
                                    new Answers(){ Answer="Q6_Answer2" },
                                    new Answers(){  Answer="Q6_Answer3" },
                                }
                            }
                        }
                    }
                };
    
    
                dbContext.QuettaReqs.AddRange(QuettaReq);
                dbContext.SaveChanges();

    The database table as below: In the QuoteQuestions class, I didn't assign the foreign key, so it auto adds a QuettaReqId column.

    3. loading related entities

    we could use the following code:

                var query = (from cc in dbContext.QuettaReqs.Include(q => q.QuoteQuestions).ThenInclude(c => c.QuesAnswers)
                             //where cc.UserName == "A"
                             select cc).FirstOrDefault();

    the output as below:

    More details about Loading Related Data, please refer to this link:

    https://docs.microsoft.com/en-us/ef/core/querying/related-data 

    Edit: If you want to use the View Model in the select clause, you could refer to the following code:

                var query = (from cc in dbContext.QuettaReqs.Include(q => q.QuoteQuestions).ThenInclude(c => c.QuesAnswers)
                             //where cc.UserName == "A"
                             select  new QuettaReq_VM
                             {
                                 UserName = cc.UserName,
                                 QuoteQuestions = cc.QuoteQuestions.Select(c => new QuoteQuestion_VM() { Question = c.Question, QuesAnswers = c.QuesAnswers.Select(d => new Answers_VM() { Answer = d.Answer }).ToList() }).ToList()
                             }
                             ).ToList();

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 21, 2018 7:57 AM

All replies

  • User-271186128 posted

    Hi john_mm,

    im trying to query db (using EF core ) a table that has a child and the cild has a child , the results im getting are instead of "tree"  (x)1 father>3(x) sun>(x)1 grandson

    im getting  father  3 times and the proper grandson for each son 

    I'm not quite sure what do you want to achieve. Could you please create a simple sample with test data, then tell us the results that you want? it might be easier for us to help you solve the problem.

    here is an article about how to use GroupJoin, you could refer to it.

    Joining Operator: GroupJoin

    Best regards,
    Dillion

    Wednesday, September 19, 2018 5:15 AM
  • User-323149085 posted

    Hello Dillon & thanks for your replay .

    Sorry my question wasn't clear

    Im trying to  achieve as follow : Quetta (EG id=4) has multi question (EG id=2 , & id=3 (FK Quetta Id=4 ) & answer (Ed id=1 & id=3 FK question id= 2)

    in a table

    //Seeding scetch 
    
      IList<QuettaReq> QuettaReq = new List<QuettaReq>()
            {
                new QuettaReq()
                {
                    id = 1, ////pramters}
                }
            }
    
            IList<QuoteQuestion> QuoteQuestion = new List<QuoteQuestion>()
            {
                new QuoteQuestion()
                {
                    QuoteQuestionId   = 1, QuoteId = 1 //QuettaReq-Id },
                    new QuoteQuestion(){QuoteQuestionId = 2, QuoteId = 1 //QuettaReq-Id }
                }
                IList<Answers> Answers = new List<Answers>()
                {
                new Answers(){AnswersId = 1, QuoteQuestionId = 1},
    
            new Answers(){AnswersId = 2,QuoteQuestionId = 1
            }
        }
    
    

    And I wish to get :

       @foreach (var request in Model)
        {//Parm
     @foreach (var qustion in request.QAViewModel)
                        {//Parm
     foreach (var answers in qustion.Answers)
                                {//Parm
    
    
    }

    @ my present query as I wrote in my question the question is render as follow : 

    SELECT [t].[Id], [t].[ApplicationDate], [t].[Bedget], [t].[CatId], [t].[DeliveryDate], [t].[DeliveryDaysNumber], [t].[District], [t].[OfferDate], [t].[OfferDaysNumber], [t].[Qentity], [t].[ReqText], [t].[SiteTerms], [t].[SiteUserId], [t].[SubmissionDaysNumber], [t].[TecnicalDetails], [t].[UserId], [qestion].
    , [qestion].[Question], [qestion].[QuettaReqId], [qestion].
    , [quetta.Category].[CatName] FROM ( SELECT DISTINCT [q].[Id], [q].[ApplicationDate], [q].[Bedget], [q].[CatId], [q].[DeliveryDate], [q].[DeliveryDaysNumber], [q].[District], [q].[OfferDate], [q].[OfferDaysNumber], [q].[Qentity], [q].[ReqText], [q].[SiteTerms], [q].[SiteUserId], [q].[SubmissionDaysNumber], [q].[TecnicalDetails], [q].[UserId] FROM [Quetta] AS [q] WHERE (([q].[OfferDate] > GETDATE()) AND ([q].[CatId] = @__suplayerCat_0)) AND [q].[Id] NOT IN ( SELECT [d].[QuettaReqId] FROM [QuettaOffers] AS [d] WHERE [d].[SiteUserId] = @__userId_1 ) ) AS [t] INNER JOIN [Categories] AS [quetta.Category] ON [t].[CatId] = [quetta.Category].[CatId] LEFT JOIN
    AS [qestion] ON [t].[Id] = [qestion].
    ORDER BY [t].[Id] Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (190ms) [Parameters=[@_outer_QuoteQuestionId='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT [a].[AnswersId], [a].[Answer], [a].
    FROM [answers] AS [a] WHERE @_outer_QuoteQuestionId = [a].
    Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (171ms) [Parameters=[@_outer_QuoteQuestionId='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT [a].[AnswersId], [a].[Answer], [a].
    FROM [answers] AS [a] WHERE @_outer_QuoteQuestionId = [a].
    Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (164ms) [Parameters=[@_outer_QuoteQuestionId='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT [a].[AnswersId], [a].[Answer], [a].
    FROM [answers] AS [a] WHERE @_outer_QuoteQuestionId = [a].
    Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (138ms) [Parameters=[@_outer_QuoteQuestionId='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT [a].[AnswersId], [a].[Answer], [a].
    FROM [answers] AS [a] WHERE @_outer_QuoteQuestionId = [a].

    as you can c it is not grouping questions and answers to  quettaReq id. 

    I hope it is more understandable ,Thanks .

     

    Thursday, September 20, 2018 2:44 PM
  • User-271186128 posted

    hi john_mm,

    john_mm

    Im trying to  achieve as follow : Quetta (EG id=4) has multi question (EG id=2 , & id=3 (FK Quetta Id=4 ) & answer (Ed id=1 & id=3 FK question id= 2)

    According to your model, you have already configure one to many relationship for them. So, I suppose there is no need to use groupjoin to join the table, you could use the Include() and ThenInclude() method to get the related entities. Please refer to the following sample:

    1. Base on the following models (using your models, just add a 'virtual' in the QuoteQuestion class) to create database: 

        public class QuettaReq
        {
            public QuettaReq()
            {
            }
    
            [Key] public int Id { get; set; }
            public string UserName { get; set; }
            //Properties 
            public virtual List<QuoteQuestion> QuoteQuestions { get; set; }
        }
        public class QuoteQuestion
        {
            [Key]
            public int QuoteQuestionId { get; set; }
    
            public int QuoteId { get; set; }
            public QuettaReq QuettaReq { get; set; }
    
            public string Question { get; set; }
            public virtual IList<Answers> QuesAnswers { get; set; }
    
        }
        public class Answers
        {
            public int AnswersId { get; set; }
            public int QuoteQuestionId { get; set; }
            public string Answer { get; set; }
    
        }

    2. Initialized data: by default, the primary key is identity, there is no need to set value.

                IList<QuettaReq> QuettaReq = new List<QuettaReq>()
                {
                    new QuettaReq(){ UserName ="A",
                        QuoteQuestions = new List<QuoteQuestion>()
                        {
                            new QuoteQuestion(){  Question = "Question 1",
                                QuesAnswers = new List<Answers>()
                                {
                                    new Answers(){   Answer="Q1_Answer1" },
                                    new Answers(){ Answer="Q1_Answer2" },
                                    new Answers(){  Answer="Q1_Answer3" },
                                }
                            },
                            new QuoteQuestion(){  Question = "Question 2",
                                QuesAnswers = new List<Answers>()
                                {
                                    new Answers(){  Answer="Q2_Answer1" },
                                    new Answers(){  Answer="Q2_Answer2" },
                                    new Answers(){  Answer="Q2_Answer3" },
                                }
                            },
                            new QuoteQuestion(){  Question = "Question 3",
                                QuesAnswers = new List<Answers>()
                                {
                                    new Answers(){  Answer="Q3_Answer1" },
                                    new Answers(){  Answer="Q3_Answer2" },
                                    new Answers(){  Answer="Q3_Answer3" },
                                }
                            }
                        }
                    },
    
                    new QuettaReq(){  UserName ="B",
                        QuoteQuestions = new List<QuoteQuestion>()
                        {
                            new QuoteQuestion(){  Question = "Question 4",
                                QuesAnswers = new List<Answers>()
                                {
                                    new Answers(){ Answer="Q4_Answer1" },
                                    new Answers(){ Answer="Q4_Answer2" },
                                    new Answers(){ Answer="Q4_Answer3" },
                                }
                            },
                            new QuoteQuestion(){  Question = "Question 5",
                                QuesAnswers = new List<Answers>()
                                {
                                    new Answers(){  Answer="Q5_Answer1" },
                                    new Answers(){  Answer="Q5_Answer2" },
                                    new Answers(){  Answer="Q5_Answer3" },
                                }
                            },
                            new QuoteQuestion(){  Question = "Question 6",
                                QuesAnswers = new List<Answers>()
                                {
                                    new Answers(){  Answer="Q6_Answer1" },
                                    new Answers(){ Answer="Q6_Answer2" },
                                    new Answers(){  Answer="Q6_Answer3" },
                                }
                            }
                        }
                    }
                };
    
    
                dbContext.QuettaReqs.AddRange(QuettaReq);
                dbContext.SaveChanges();

    The database table as below: In the QuoteQuestions class, I didn't assign the foreign key, so it auto adds a QuettaReqId column.

    3. loading related entities

    we could use the following code:

                var query = (from cc in dbContext.QuettaReqs.Include(q => q.QuoteQuestions).ThenInclude(c => c.QuesAnswers)
                             //where cc.UserName == "A"
                             select cc).FirstOrDefault();

    the output as below:

    More details about Loading Related Data, please refer to this link:

    https://docs.microsoft.com/en-us/ef/core/querying/related-data 

    Edit: If you want to use the View Model in the select clause, you could refer to the following code:

                var query = (from cc in dbContext.QuettaReqs.Include(q => q.QuoteQuestions).ThenInclude(c => c.QuesAnswers)
                             //where cc.UserName == "A"
                             select  new QuettaReq_VM
                             {
                                 UserName = cc.UserName,
                                 QuoteQuestions = cc.QuoteQuestions.Select(c => new QuoteQuestion_VM() { Question = c.Question, QuesAnswers = c.QuesAnswers.Select(d => new Answers_VM() { Answer = d.Answer }).ToList() }).ToList()
                             }
                             ).ToList();

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 21, 2018 7:57 AM
  • User-323149085 posted

    Thank you Dillion , it was very helpful 

    Wednesday, September 26, 2018 9:36 AM