CODEWITHSUNDEEP
c#linq
David
David

Reputation: 235

Why is linq reversing order in group by

I have a linq query which seems to be reversing one column of several in some rows of an earlier query:

        var dataSet = from fb in ds.Feedback_Answers
                         where fb.Feedback_Questions.Feedback_Questionnaires.QuestionnaireID == criteriaType
                            && fb.UpdatedDate >= dateFeedbackFrom && fb.UpdatedDate <= dateFeedbackTo
                         select new
                         {
                             fb.Feedback_Questions.Feedback_Questionnaires.QuestionnaireID,
                             fb.QuestionID,
                             fb.Feedback_Questions.Text,
                             fb.Answer,
                             fb.UpdatedBy
                         };

Gets the first dataset and is confirmed working. This is then grouped like this:

var groupedSet = from row in dataSet
                         group row by row.UpdatedBy
                             into grp
                             select new
                             {
                                 Survey = grp.Key,
                                 QuestionID = grp.Select(i => i.QuestionID),
                                 Question = grp.Select(q => q.Text),
                                 Answer =  grp.Select(a => a.Answer)
                             };

While grouping, the resulting returnset (of type: string, list int, list string, list int) sometimes, but not always, turns the question order back to front, without inverting answer or questionID, which throws it off. i.e. if the set is questionID 1,2,3 and question A,B,C it sometimes returns 1,2,3 and C,B,A

Can anyone advise why it may be doing this? Why only on the one column? Thanks!

edit: Got it thanks all! In case it helps anyone in future, here is the solution used:

        var groupedSet = from row in dataSet
                         group row by row.UpdatedBy
                             into grp 
                             select new
                             {
                                 Survey = grp.Key,
                                 QuestionID = grp.OrderBy(x=>x.QuestionID).Select(i => i.QuestionID),
                                 Question = grp.OrderBy(x=>x.QuestionID).Select(q => q.Text),
                                 Answer =  grp.OrderBy(x=>x.QuestionID).Select(a => a.Answer)
                             };

Upvotes: 1

Views: 266

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726809

Reversal of a grouped order is a coincidence: IQueryable<T>'s GroupBy returns groups in no particular order. Unlike in-memory GroupBy, which specifies the order of its groups, queries performed in RDBMS depend on implementation:

The query behavior that occurs as a result of executing an expression tree that represents calling GroupBy<TSource,TKey,TElement>(IQueryable<TSource>, Expression<Func<TSource,TKey>>, Expression<Func<TSource,TElement>>) depends on the implementation of the type of the source parameter.`

If you would like to have your rows in a specific order, you need to add OrderBy to your query to force it.

How I do it and maintain the relative list order, rather than apply an order to the resulting set?

One approach is to apply grouping to your data after bringing it into memory. Apply ToList() to dataSet at the end to bring data into memory. After that, the order of subsequent GrouBy query will be consistent with dataSet. A drawback is that the grouping is no longer done in RDBMS.

Upvotes: 3

Related Questions