title: Complex ASP.NET MVC5 EF6 Linq query
body: <p>I am converting an old classic asp <strong>(YUCK!!)</strong> application to a ASP.NET MVC5 Web Application.
  One report in particular is an aggregation, that previously was created by building a dynamic sql query and executing it in a loop.</p>
  
  <p>I would like to convert the entire thing to a linq query, but am struggling with doing the pivot.</p>
  
  <p>Essentially, in my app, there are numerous Entities (businesses) who fill out a set of questions from a category and question set, with the number of questions being dynamic for each category.</p>
  
  <p><strong>Question Structure:</strong>
  QuestionSet has Categories has Questions (each their own table).
  Entities answer questions with a response and evaluation (in an answers table).</p>
  
  <p>The old report I am trying to rebuild <strong><em>shows a list of entities and their answers to each question for a specific category</em></strong> and is shown here:
  <a href="http://workitonline.biz/temp/aggregation-practice_classic.png" rel="nofollow noreferrer">image http://workitonline.biz/temp/aggregation-practice_classic.png</a>.</p>
  
  <p>The old code is like this <strong>(I have omitted a lot for breivty's sake):</strong></p>
  
  <pre><code>'Get all the questions with filters
  Dim rsQuestions : Set rsQuestions = objAuditAggregation.GetQuestionsFiltered(p_categoryId, p_questionGroupId, p_priority)
  
  ' Dynamically construct the query based on how many
  ' question are in the category
  Dim questionValues : questionValues = ""
  Dim questionTables : quesitonTables = ""
  
  Dim totalQuestions : totalQuestions = 0
  
  While(Not rsQuestions.EOF)  
      i = rsQuestions.Fields.Item("id").Value
  
      questionValues = questionValues &amp; 
      questionTables = questionTables &amp; 
  
      totalQuestions = totalQuestions + 1
      rsQuestions.MoveNext                            
  Wend
  
  'Get all the entities and their answers (filters omitted)
  Set rsEntities_cmd = Server.CreateObject ("ADODB.Command")
  rsEntities_cmd.ActiveConnection = MM_raworkit2004_STRING
  rsEntities_cmd.CommandText = 
      "SELECT DISTINCT E.EntityId, EntityName, 
      ISNULL(EA.include, 1) AS include", a" &amp; i &amp; ".yourEvaluation AS yourEvaluation" &amp; i &amp; ", a" &amp; i &amp; ".yourResponse AS yourResponse" &amp; i 
      FROM tbl0101Entity E 
      INNER JOIN tbl0102User U ON U.EntityId = E.EntityId 
      LEFT JOIN tbl12EntityAggregates EA ON E.EntityId = EA.entityId 
      LEFT JOIN (SELECT entityId, yourEvaluation, yourResponse 
             FROM tbl12Audit 
             WHERE questionId = " &amp; i &amp; " AND entityLevelAudit = 1) AS a" &amp; i &amp; " ON E.EntityId = a" &amp; i &amp; ".entityId"
  
  Set rsEntities = rsEntities_cmd.Execute
  </code></pre>
  
  <p><strong>So, to the .NET Code.</strong> I have the following view models to display the data:   </p>
  
  <pre><code>public class AggregationPracticeConductViewModel : BaseModel
  {
      public ICollection&lt;EntitiesAndAnswersViewModel&gt; EntitiesAndAnswers { get; set; }
      public AggregationPracticeConductFiltersViewModel Filters { get; set; }
      public bool Display { get; set; }
      public bool Updated { get; set; }
      public bool IsAdmin { get; set; }
  }
  
  public class EntitiesAndAnswersViewModel : BaseModel
  {
      public Entity Entity { get; set; }
      public bool Include { get; set; }
      public ICollection&lt;AnswersViewModel&gt; Answers { get; set; }
  }
  public class AnswersViewModel
  {
      public int QuestionId { get; set; }
      public int? Evaluation { get; set; }
      public int? Response { get; set; }
  }
  </code></pre>
  
  <p>And I have the following controller code to fill the view models.</p>
  
  <pre><code>private ICollection&lt;EntitiesAndAnswersViewModel&gt; getEntitiesAndQuestions(AggregationPracticeConductViewModel apdvm, AggregationPracticeConductFiltersViewModel filters)
      {
          var query = from e in _repository.GetAll&lt;Entity&gt;()
                      from u in e.Users                        
                      where (e.AuditQuestionGroupId != null ? e.AuditQuestionGroupId : 0) == this.LoggedInEntity.AuditQuestionGroupId
                      select new { entity = e, user = u };
  
          var query2 = from a in query
                       join ea in _repository.GetAll&lt;EntityAggregate&gt;() on a.entity.EntityId equals ea.EntityId into eas
                       from ea in eas.DefaultIfEmpty()
                       group a.entity by new { entity = a.entity, ea = ea } into newGroup
                       orderby newGroup.Key.entity.Name
                       select new EntitiesAndAnswersViewModel()
                       {
                           Entity = newGroup.Key.entity,                            
                           Include = newGroup.Key.ea == null ? true : newGroup.Key.ea.include   // If there are no entity aggregate record yet, return as include = true                                                   
                       };                                     
  
           // TODO: Now join the entities with their results. This is what I have so far
          var results = from e in query2
                        join a in _repository.GetAll&lt;ReviewAnswer&gt;().DefaultIfEmpty() on e.Entity.EntityId equals a.entityId
                        where a.Question.Category.QuestionGroup.Id == (e.Entity.AuditQuestionGroupId != null ? e.Entity.AuditQuestionGroupId : 0)
                        &amp;&amp; a.Question.Category.id == filters.SelectedCategoryId &amp;&amp; a.entityLevelAudit == true
                        group e by new { e, a } into group1                          
                        select new EntitiesAndAnswersViewModel()
                        {
                            Include = group1.Key.e.Include,
                            Entity = group1.Key.e.Entity,
                            Answers =  // list of AnswersViewModels e.g. new AnswersViewModel { QuestionId = answer.question, Evaluation = answer.Evaluation, Response = answer.Response}
                        };
          return results.ToList();
      }
  </code></pre>
  
  <p>Obviously the code is not working as in the last query I don't know how I could group by the entity and the set of questions, and then return a list of answers inside the Select statement. But up until the TODO:, the code is getting exactly what I want.</p>
  
  <p>It's just this last query that I am no even sure if it's possible to do in linq.
  Any help or guidance would be greatly appreciated.</p>