title: Filter Child Object of a Collection
body: <p>First of all, I'm new to the EF-code first and .NET in general. </p>
  
  <p>Im working on a mobile web app thats being built with MVC4, EF, JQuery, AutoMapper &amp; other technologies and libraries.</p>
  
  <p>This might be confusing, but I will do my best to explain it right.</p>
  
  <p>I have the following pocos:</p>
  
  <pre><code>  public class Test
  {
      [Key]
      public int TestId { get; set; }
      .
      .
      Other properties
      .
      .
      public virtual ICollection&lt;Question&gt; Questions { get; set; }
      public virtual ICollection&lt;UserStatus&gt; UserStatuses { get; set; }
  }
  
  public class UserStatus
  {
      [Key]
      public int UserStatusId { get; set; }
      public int TestId { get; set; }
      public string Customer { get; set; }
      .
      .
      Other properties
      .
      .
  }
  
  public class Question
  {
      [Key]
      public int QuestionId { get; set; }
      public int TestId { get; set; }
      .
      .
      Other properties
      .
      .
  }
  </code></pre>
  
  <p>and in my context class i have: </p>
  
  <pre><code>protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
      Database.SetInitializer&lt;CmeContext&gt;(null);
  
      modelBuilder.Entity&lt;Test&gt;()
          .HasKey(a =&gt; new { a.TestId })
          .HasMany(s =&gt; s.Questions);
      modelBuilder.Entity&lt;Test&gt;()
          .HasKey(a =&gt; new { a.TestId })
          .HasMany(s =&gt; s.UserStatuses);          
  }
  </code></pre>
  
  <p>The above Test object has a one-to-many relationship to Question and UserStatus objects through TestId. Each Test has it's own set of questions which are 4 questions per test, and 20 userStatuses 5 per question). 
  As the name implies the UserStatus table has user statuses; it let me know which question(s) from what test was answered by a user. </p>
  
  <p>The code in the controller:</p>
  
  <pre><code>var filtered = (from test in _ctxCmeContext.Test.Include("UserStatus").Include("Question")
                  where test.Program == "SomeProgram" &amp;&amp; test.IssueDate.Value.Year == year &amp;&amp; test.IssueDate.Value.Month == monthNumber 
                  orderby test.IssueDate descending 
                  select new { test,
                                                   Questions = test.Questions, 
                           UserStatuses =     test.UserStatuses.Where(x =&gt; x.Customer == currentUserId) });
  </code></pre>
  
  <p>As you see I have an anonymous class so I can get the UserStatuses collection filtered by currentUserId to get the statuses for the current user only. Everything works fine at this point.</p>
  
  <p>Here's what I need to do:</p>
  
  <p>Each question needs to know its own status, so it's a one-to-one relationship between the Question and UserStatus table</p>
  
  <p>In my context class I added the following:</p>
  
  <pre><code>      modelBuilder.Entity&lt;Question&gt;()
       .HasRequired(x =&gt; x.UStatus)
       .WithOptional();
  </code></pre>
  
  <p>and in the Question Object I had:</p>
  
  <pre><code>                        public virtual UserStatus UStatus { get; set; }
  </code></pre>
  
  <p>and I also modified my linq statement as follows:</p>
  
  <pre><code>          var filtered = (from test in         _ctxCmeContext.Test.Include("UserStatus").Include("Question").Include("UStatus")
                  where test.Program == "SomeProgram" &amp;&amp;   test.IssueDate.Value.Year == year &amp;&amp; test.IssueDate.Value.Month == monthNumber 
                  orderby test.IssueDate descending 
                  select new { test, 
                               QuestionStatus =    test.Questions.Select(u=&gt;u.UStatus), 
                               Questions = test.Questions, 
                               UserStatuses = test.UserStatuses.Where(x =&gt; x.Customer == currentUserId) }); 
  </code></pre>
  
  <p>The result:</p>
  
  <p>the UStatus property had data for each question but it wasn't for the current user nor for the right test; it seemed to me that it just grabbed the first record it ecountered that had the same QuestionId from the Question table. No good.</p>
  
  <p>so I changed the relationship to one-to-many:</p>
  
  <p>In my context class I replaced:</p>
  
  <pre><code>    modelBuilder.Entity&lt;Question&gt;()
         .HasRequired(x =&gt; x.UStatus)
         .WithOptional();
  </code></pre>
  
  <p>with:</p>
  
  <pre><code>        modelBuilder.Entity&lt;Question&gt;()
             .HasKey(a =&gt; new { a.QuestionId })
             .HasMany(s =&gt; s.UStatus);
  </code></pre>
  
  <p>changed the UStatus property in the Question Object to:</p>
  
  <pre><code>   public virtual ICollection&lt;UserStatus&gt; UStatus { get; set; }
  </code></pre>
  
  <p>and removed the following from the linq statement</p>
  
  <pre><code>   QuestionStatus = test.Questions.Select(u=&gt;u.UStatus), 
  </code></pre>
  
  <p>This time I was able to get some correct data but only for the first 4 questions, and the rest were null</p>
  
  <p>I don't know what to do anymore. Help me please.</p>