title: C# Entity Framework - Where Inside Include
body: <p>I have object called <strong>Project</strong> that has a list of hours (which is list made up of a <strong>Work</strong> objects) worked on that project. The <strong>Work</strong> object has a member called <strong>WorkDate</strong>, which is a day that you worked on that project. What i want to do is get a list of projects where the hours is between two dates. However, when i try to make this query in entity framework, i get the error below. Does anyone know how to write this query in entity frame work. I would hopefully like to achieve this with a single query and not load all the projects and sort them in my controller. </p>
  
  <p>For a better understand of what I'm trying to do, where is an SQL example. </p>
  
  <p>In SQL i would do this via a join. I have two tables <strong>Projects</strong> and <strong>Work</strong>. I would join Work and Projects on  <strong>Work.project_ID</strong> is equal to the <strong>Project.ID</strong> 
  Then select all project rows where the Work.WorkDate is bewteen two dates . </p>
  
  <p>ProjectController.cs</p>
  
  <pre><code>[HttpGet("[action]")]
  public async Task&lt;IActionResult&gt; GetProjectsByDate()
  {
      try
      {
              var min = new DateTime(2017, 3, 1); 
              var max = new DateTime(2017, 3, 15);
              var projects = await context.Projects
                  .Include(p =&gt; p.Hours)
                  .ThenInclude(w =&gt; w.Where(h =&gt; h.WorkDate &lt; max &amp;&amp; h.WorkDate &gt; min))
                  .AsNoTracking()
                  .ToListAsync(); 
               return Json(projects);
      }
  }
  </code></pre>
  
  <p>Project.cs</p>
  
  <pre><code>public class Project
  {
      [Key]
      public int ID { get; set; }
      public int? TeamID { get; set; }
      public int? ManagerID { get; set; }
      public int? CreatedBy { get; set; }
  
      public string Name { get; set; }
      public string Details { get; set; }
      public double? Estimate { get; set; }
      public bool IsActive { get; set; }
  
      public DateTime StartDate { get; set; }
      public DateTime EndDate { get; set; }
      public DateTime CreatedDate { get; set; }
  
      [ForeignKey("TeamID")]
      public virtual Team Team { get; set; }
      [ForeignKey("ManagerID")]
      public virtual User Manager { get; set; }
      [ForeignKey("CreatedBy")]
      public virtual User Creator { get; set; }
  
      public virtual ICollection&lt;Work&gt; Hours { get; set; }
  
  
      public Project()
      {
          Hours = new List&lt;Work&gt;();
      }
  }
  </code></pre>
  
  <p>Out put of projects </p>
  
  <pre><code>Message = "The property expression 'w =&gt; {from Work h in w where (([h].WorkDate &lt; __max_0) AndAlso ([h].WorkDate &gt; __min_1)) select [h]}' is not valid. The expression should represent a property access: 't =&gt; t.MyProperty'. For more information on including related d...
  </code></pre>