title: Using custom method inside Linq Select with Entity Framework
body: <p>I am trying to use a custom Function inside a Linq Select that is used with EF.
  I want to project each item of tblMitarbeiter onto one tblMitarbeiterPersonalkostenstelleHistories that is valid ad the given date.
  This should be done with an extension method so that I do not repeat myself ;)
  I can only get it to work when used directly on the DbSet, but not inside a Select.</p>
  
  <p>How can I teach EF to recognize my Method (3.) as if I would be writing it out (1.)?</p>
  
  <pre><code>void Main()
  {
      var date = DateTime.Now;
  
      // 1. works, returns IEnumerable&lt;tblMitarbeiterPersonalkostenstelleHistories&gt;
      tblMitarbeiters
      .Select(m =&gt; m.tblMitarbeiterPersonalkostenstelleHistories.Where(p =&gt; p.ZuordnungGültigAb &lt;= date).OrderByDescending(p =&gt; p.ZuordnungGültigAb).FirstOrDefault())
      .Dump();
  
      // 2. works, returns one tblMitarbeiterPersonalkostenstelleHistories
      tblMitarbeiterPersonalkostenstelleHistories
      .GetValidItemForDate(p =&gt; p.ZuordnungGültigAb, date)
      .Dump();
  
      // 3. throws NotSupportedException
      tblMitarbeiters
      .Select(m =&gt; m.tblMitarbeiterPersonalkostenstelleHistories.GetValidItemForDate(p =&gt; p.ZuordnungGültigAb, date))
      .Dump();
  
      // 4. throws NotSupportedException
      tblMitarbeiters
      .Select(m =&gt; m.tblMitarbeiterPersonalkostenstelleHistories.AsQueryable().GetValidItemForDate(p =&gt; p.ZuordnungGültigAb, date))
      .Dump();
  }
  
  
  public static class QueryableExtensions
  {
      public static T GetValidItemForDate&lt;T&gt;(this IQueryable&lt;T&gt; source, Expression&lt;Func&lt;T, DateTime&gt;&gt; selector, DateTime date)
      {
          var dateAccessor = Expression.Lambda&lt;Func&lt;T, DateTime&gt;&gt;(Expression.Constant(date), selector.Parameters);
          var lessThanOrEqual = Expression.LessThanOrEqual(selector.Body, dateAccessor.Body);
          var lambda = Expression.Lambda&lt;Func&lt;T, bool&gt;&gt;(lessThanOrEqual, selector.Parameters);
          return source.Where(lambda).OrderByDescending(selector).FirstOrDefault();
      }
  
      public static T GetValidItemForDate&lt;T&gt;(this IEnumerable&lt;T&gt; source, Func&lt;T, DateTime&gt; selector, DateTime date) =&gt;
          source.Where(i =&gt; selector(i) &lt;= date).OrderByDescending(selector).FirstOrDefault();
  }
  </code></pre>