locked
cant group results using ef core 3.1 left join RRS feed

  • Question

  • User-323149085 posted

    im qurery results in 2 table base on Id key using join but the results im getting insted of grouping by key are single foreach one sub results E.G. item A has 3 items B but im getting 3 times item A each one with 1 item B

    Code :`

    var viewModel =
                        from req in Context.Set<QuettaReq>()//results Id= 1 
                            .Where(sd => sd.SiteUserId == _userManager.GetUserId(User)
                                         && sd.QuettaClose == false)
                            .OrderBy(d => d.ApplicationDate)
                            .ThenBy(c => c.Category.CatName)
                        join question in Context.Set<QuoteQuestion>() //Results should be 3 items related to  req Id 1
                        on req.Id equals question.QuoteId into Jquet
                        from question in Jquet.DefaultIfEmpty()
                        select new {req, question,};
                    //Results are: req ID 1 three times each time with one question
                    //Insted of one time ID 1 with 3 questions 
                    var com = viewModel.Select(t => new ReqestWithQA
                        {
                           Id = t.req.Id,
                           ReqText=t.req.ReqText,
                           Question           = t.question.Question,
                           //Parm...
                           })
                        .AsNoTracking();`

    Tuesday, May 26, 2020 1:05 PM

All replies

  • User303363814 posted

    Wow! This looks like a very complex way to do a simple operation!

    Do you have a navigation property in QuettaReq?  Can you show the key parts of the definition of QuettaReq and QuoteQuestion?

    I would think that what you need to do is something like

    var result = Context.QuettaReqs
                    .Include(qr => qr.Questions)
                    .Where(qr =>qr.SiteUserId == _userManager.GetUserId(User) && !qr.QuettaClose)
                    .OrderBy(qr => qr.ApplicationDate)
                    .ThenBy(qr => qr.Categort.CatName);

    The .Include takes care or the entire joining and grouping stuff

    If you really, really need to do it the difficult way then add a group clause after your select (you didn't ask the results to be grouped, so why expect them to be grouped?)

    select new {req, question }
    group r by req;


    (I don't have any experience with ef core)

    Wednesday, May 27, 2020 1:18 AM
  • User-323149085 posted

    Thanks PaulTheSmith , but this is not working in EFCORE ,

    yes , i dont know why but the 3.1 ver is way to  complex ORM

    Wednesday, May 27, 2020 8:30 AM
  • User303363814 posted

    is not working
    Can you add a little more detail?  Show the code, show the compilation error, show the exception, show the unexpected result

    Thursday, May 28, 2020 4:22 AM
  • User-323149085 posted

    john_mm

    is not working

    Can you add a little more detail?  Show the code, show the compilation error, show the exception, show the unexpected result

    PaulTheSmith thank you for the extra mile of trying : 

     var result = Context.Quetta
                    .Include(qr => qr.QuoteQuestions)
                    .Where(qr =>qr.SiteUserId == _userManager.GetUserId(User) && !qr.QuettaClose)
                    .OrderBy(qr => qr.ApplicationDate)
                    .ThenBy(qr => qr.Category.CatName).ToList();
                return View(result);
    //Results are 2 Items(Quetta) with 0 (QuoteQuestions)
    

    results should be 2 items(Quetta) one item(Quetta) with 2 (QuoteQuestions) 

    Thanks

    Thursday, May 28, 2020 10:26 AM
  • User303363814 posted

    Do you have a navigation property in QuettaReq?  Can you show the key parts of the definition of QuettaReq and QuoteQuestion?

    (And maybe the data that makes you think the answer is wrong)

    Friday, May 29, 2020 12:12 PM