title: EF Core 3.0 using Skip() and Take() in queries with nested collection projections
body: <p>I'm using EF Core 3.0.1 in my project and I need to perform nested projections in sql instead of client-side. </p>
  
  <pre><code>public class Order
  {
     public int Id {get;set;}
     public ICollection&lt;Detail&gt; Details {get;set;}
  }
  
  public class Detail
  {
     public string Comment {get;set;}
  }
  
  // Order and Detail has exact same dto's: OrderDto and DetailDto
  
  public PagedCollectionResponse&lt;OrderDto&gt; Execute()
  {
     // consider I need to use .Count() somewhere inside this method after applying nested projection.
     var orders = context.Orders
        .Select(x =&gt; new OrderDto 
        {
            Id = x.Id,
            Details = x.Details.Select(d =&gt; new DetailDto 
            {
                Comment = d.Comment
            })
            // initially, I had no ToList here, but this hasn't fixed the issue
            .ToList();
        });
  
     var ordersCount = orders.Count(); // works fine, executing only one query in db
  
     var ordersCount2 = orders.Skip(5).Take(1).Count(); --&gt; System.InvalidCastException: Unable to cast object of type
                                           'Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlFunctionExpression' to 
                                            type'System.Linq.Expressions.ConstantExpression'
  }
  </code></pre>
  
  <p>However, if I remove collection projection, then both statements assigning counter and counter2 execute normally.</p>
  
  <pre><code>...
  .Select(x =&gt; new OrderDto 
  {
            Id = x.Id
  };
  
  var ordersCount = orders.Count(); // OK
  var ordersCount2 = orders.Skip(5).Take(1).Count(); // OK
  </code></pre>
  
  <p>According to the <a href="https://benjii.me/2018/01/expression-projection-magic-entity-framework-core/" rel="nofollow noreferrer">post on EF Core projections</a> I've found, it was a known issue in EF Core 2.0. Was it fixed?</p>
  
  <blockquote>
    <p>VERSION WARNING (EF Core 2.0.0): At the time of writing nested collection projections only work in memory, defeating the purpose of using them in the first place. This will be fixed soon, so it’s still worth understanding. They DO still work for nested single element projections which we’ll learn about in the next section, so keep reading!</p>
  </blockquote>
  
  <p>So, is there any known fix on how to make this work?</p>