GroupJoin with list returns multiple results instead of attaching the list per result

Question

I have I query that im using GroupJoin for a list of items(father item) & GroupJoin to a list of items(first child item) to the second list(second child item) that has fk to the first child item. insted of getting a list for each item(father item) im getting one m item per item (so insted of getting single result with a list of item im geten each("father") item multi time with a single ("first child item") result.

I hope the code will explain better.

Entity model:

public class QuettaReq
{
    public QuettaReq()
    {
    }

    [Key] public int             Id   { get; set; }
    public       ApplicationUser User { get; set; }

    public Category Category { get; set; }

    public int CatId { get; set; }
    //Parameters 

    public virtual List<QuettaOffer>   QuettaOffer    { get; set; }
    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; }
}

View model:

public class ReqestWithQA
{
    [Key] public int Id { get; set; }

    //public ApplicationUser User { get; set; }
    public string SiteUserId { get; set; }

    //properties
    public virtual IList<QAViewModel> QAViewModel { get; set; }
    public         int                QuettaReqId { 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; }
}

Query:

IQueryable<ReqestWithQA> viewModel = _context.Quetta
        .Include(q => q.Category)
        .Where(d => d.OfferDate > DateTime.Now && d.CatId == suplayerCat)
        .Where(rq => !postOn.Contains(rq.Id))
        .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, //there are 6 question
                            Answers  = q.Answers,
                            QuoteId  = q.QuoteId

                        })
                        .FirstOrDefault()
                }
            });

return View(viewModel.Distinct().ToList()); //viewModel results are 6 Should be 1 with 6 Q

I think the problem is the array but I have no idea what to use instead:

QAViewModel = new[] {
    joinQestionQuetta
        .Select(q => new QAViewModel

Answer 1

I'm not quite sure what are you trying to achieve with new [] { ….. }.FirstOrDefault(), and also if you are hitting some EF Core query translation bug, but in general try not using manual Join / GroupJoin, but navigation properties where possible.

With your entity and view models structure, looks like the query should be something like this:

var query = _context.Quetta
    .Include(qr => qr.Category) // Not needed, will be ignored
    .Where(qr => qr.OfferDate > DateTime.Now && qr.CatId == suplayerCat)
    .Where(qr => !postOn.Contains(qr.Id))
    .Select(qr => new ReqestWithQA
    {
        ReqText = qr.ReqText,
        Qentity = qr.Qentity,
        CatId = qr.CatId,
        CatName = qr.Category.CatName,
        District = qr.District,
        ApplicationDate = qr.ApplicationDate,
        DeliveryDate = qr.DeliveryDate,
        OfferDate = qr.OfferDate,
        TecnicalDetails = qr.TecnicalDetails,
        Bedget = qr.Bedget,
        Id = qr.Id,
        QAViewModel = qr.QuoteQuestions
            .Select(qq => new QAViewModel
            {
                QuoteQuestionId = qq.QuoteQuestionId,
                QuoteId = qq.QuoteId,
                Question = qq.Question,
                Answers = qq.Answers,
            })
            .ToList()
    });

var result = query.ToList();

The collection navigation properties act as GroupJoin, reference navigation properties - as Join (either inner or left outer depending on whether the relationship is required or optional).