I am trying to build a survey engine from an existing database design which is like this.
Survey - Not so Relavent
========================
SurveyID
SurveyName
SurveyQuestionCategories
=========================
SurveyQuestionCategoryID
SurveyQuestionCategory
SurveyID
RecordOrder
SurveyQuestions
================
SurveyQuestionID
SurveyQuestion
SurveyID
SurveyQuestionCategoryID
RecordOrder
Basically there will always be a list of questions for a survey but survey may or may not have question categories. What I want to do is have one linq query that will pull all the questions for the survey grouped by categories (if they exist) and order them by category record order first (if they exist) and then question record order. I have got something like this so far. (Just assume that variable CurrentSurvey holds the entity representing the currently displayed survey)
Dim Al开发者_Go百科lQuestions = From sq In CurrentSurvey.SurveyQuestions
Order By sq.RecordOrder
Group sq By Key = sq.SurveyQuestionCategory Into Group
Select QuestionCategory = Key, Questions = Group
This works fine as the grouping handles the grouping by NULL SurveyQuestionCategory. The issue now is when I change the query to this.
Dim AllQuestions = From sq In CurrentSurvey.SurveyQuestions
Order By sq.RecordOrder
Group sq By Key = sq.SurveyQuestionCategory Into Group
Order By Key.RecordOrder
Select QuestionCategory = Key, Questions = Group
The line Order By Key.RecordOrder throws a null reference exception and I can understand why. Can someone advise on how to resolve this by just modifying this one query? I am fairly new to LINQ and the necessity to write this project in VB .Net is not helping. :-)
Just so you know, I'm open to taking another approach to the problem. I have already tried left outer join too but I'm confused about the ordering with that approach. Here is my alternate query.
Dim AllSurveyQuestions = (From sq In CurrentSurvey.SurveyQuestions Group Join qc In CurrentSurvey.SurveyQuestionCategories
On sq.SurveysNEW Equals qc.SurveysNEW Into sqc = Group
From qc In sqc.DefaultIfEmpty()
Select sq Order By sq.RecordOrder).Distinct()
Thanks for the help in advance.
Here's an idea, not tested though:
Dim AllQuestions = From sq In CurrentSurvey.SurveyQuestions
Order By sq.RecordOrder
Group sq By Key = sq.SurveyQuestionCategory Into Group
Select QuestionCategory = Key, Questions = Group,
CategoryOrder = If(Key Is Nothing, -1, Key.RecordOrder)
Order By CategoryOrder
I assumed RecordOrder is an integer, by putting -1 in case there is no category, such questions would appear first. Now if that's a LinqToSql query, maybe the If condition won't work.
精彩评论