CODEWITHSUNDEEP
linqentity-framework-core
K.Z
K.Z

Reputation: 5075

LINQ group by null issue

I am working on LINQ query where I need all the questions where each question may or may not have sub-question.

I am getting group by null/ exception issue as some parent question doesn't have child question. I am doing left join followed; group by parent question

(from question in Context.Questions.Where(question => question.ConsultationId == ConsultationId)
                   join questionHierarchy in Context.QuestionHierarchy on question.Id equals questionHierarchy.ParentQuestionId into qs
                   from childQuestion in qs.DefaultIfEmpty()
                   group childQuestion by question into g
                   select new
                   {
                       g.Key,
                       g
                   }).ToList();

Upvotes: 1

Views: 905

Answers (2)

Harald Coppoolse
Harald Coppoolse

Reputation: 30464

If you want something like "questions with their sub-questions" cnsider using GroupJoin? instead of an inner join followed by a GroupBy

In small steps:

IQueryable<Question> questions = dbContext.Questions.Where(...)
IQueryable<QuestinoHierarch> subQuestions = Context.QuestionHierarchy;

var questionsWithTheirSubQuestions = questions.GroupJoin(  // GroupJoin the questions
    subQuestions,                                          // with the subQuestions
    question => question.Id,           // from every question take the Id
    subQuestion => subQuestion.ParentQuestionId, 
                                       // from every subQuestion take the ParentQuestionId,
    (question, subQuestions) => new    // take the question with all matching subQuestions
    {                                  // to make one new object:
        // select only the question items you plan to use
        Title = question.Title,
        Priority = question.Priority,
        ...

        // select only the subQuestions that you want (or all)
        // for example only the answered sub questions:
        SubQuestions = subQuestions
             .Where(subQuestion.Type = QuestionType.Answered)
             .Select(subQuestion => new
             {
                  // again: select only the subQuestion properties you plan to use
                  Name = subQuestion.Name,
                  Date = subQuestion.Date,
                  ...
             })
             .ToList(),
    });

TODO: if desired, make one big statement.

Upvotes: 0

K.Z
K.Z

Reputation: 5075

found the answer

(from question in Context.Questions.Where(question => question.ConsultationId == ConsultationId)
                   join questionHierarchy in Context.QuestionHierarchy on question.Id equals questionHierarchy.ParentQuestionId into qs
                   from childQuestion in qs.DefaultIfEmpty()
                   group childQuestion by question into groupQuestions
                   select new
                   {
                       groupQuestions.Key,
                       childQuestions = groupQuestions.DefaultIfEmpty() == null? null : groupQuestions
                   }).ToList();

Upvotes: 1

Related Questions