title: EntityFramework - virtual ICollection query
body: <p>I have entities like this:</p>
  
  <pre><code>public class Student
  {
      public int StudentId { get; set; }
      public string StudentName { get; set; }
  
      //Foreign key for Standard
      public int StandardId { get; set; }
      public virtual Standard Standard { get; set; }
  }
  
  public class Standard
  {
      public int StandardId { get; set; }
      public string StandardName { get; set; }
  
      public virtual ICollection&lt;Student&gt; Students { get; set; }
  }
  </code></pre>
  
  <p>In my query, when I write:</p>
  
  <pre><code>dbcontext.Standards.Where(standard =&gt; standard.StandardId &gt; 6)
  </code></pre>
  
  <p>I can see that the underlying query that has been run is something like:</p>
  
  <pre><code>SELECT "Extent1"."StandardId", "Extent1"."StandardName"
  FROM "dbo"."Standard" as "Extent1"
  WHERE "Extent1"."StandardId" &gt; @EntityKeyValue1;
  </code></pre>
  
  <p>I understand that because it is IQueryable, even the Expression in Where function has been translated and is actually run in the database and not in the program.</p>
  
  <p>The Students are not loaded since I have marked the collection as virtual. No I am trying to load them in another query:</p>
  
  <pre><code>var XStandard = dbcontext.Standards.Where(standard =&gt; standard.StandardId == 10);
  var students = XStandard.Students.Where(student =&gt; student.StudentId &gt; 5);
  </code></pre>
  
  <p>I see that the query executed is:</p>
  
  <pre><code>SELECT "Extent1"."StudentId", "Extent1"."StudentName", "Extent1"."StandardId"
  FROM "dbo"."Student" as "Extent1"
  WHERE "Extent1"."StandardId = @EntityKeyValue1;
  </code></pre>
  
  <p>I do not see any <code>WHERE</code> clause in that query which checks is the student id is greater than 5. It is actually fetching all the students and filtering out in memory. I understand that this is because it is IEnumerable and not IQueryable and since IEnumerable is LINQ-to-objects, the filtering is done in memory.</p>
  
  <p>When I run the query against dbcontext like this:</p>
  
  <pre><code>var students = dbcontext.Students.Where(student =&gt; student.StudentId &gt; 5 &amp;&amp; student.StandardId == 10);
  </code></pre>
  
  <p>Now the SQL query is like:</p>
  
  <pre><code>SELECT "Extent1"."StudentId", "Extent1"."StudentName", "Extent1"."StandardId"
  FROM "dbo"."Student" as "Extent1"
  WHERE "Extent1"."StandardId" = @EntityKeyValue1 AND "Extent1"."StudentId" &gt; @EntityKeyValue2;
  </code></pre>
  
  <p>If the query on nested collections fetches all the records rather than a few, whats the point of having nested collections in a one-many relation?
  Is there a way how I can make sure this is run at the database end? Am I missing something? Please help me understand how EF works internally. Thanks in advance.</p>