title: How to create complex dynamic queries in EF Core 3.1 after breaking changes?
body: <p>I have this function that returns an <code>IQueryable</code>:</p>
  
  <pre><code>private IQueryable&lt;string&gt; GetActiveCellPhoneNumbersUpToDate(long serviceToken, DateTime date, bool? isPrepaid = null)
  {
      var to = date.AddDays(1).Date;
      var query = ViewRepository
          .All
          .Where(i =&gt; i.ServiceToken == serviceToken)
          .Where(i =&gt; i.Date &lt; to);
      if (isPrepaid.HasValue)
      {
          query = query.Where(i =&gt; i.IsPrepaid == isPrepaid);
      }
      query = query.OrderByDescending(i =&gt; i.Date);
      var result = query
          .GroupBy(i =&gt; i.CellPhoneNumber)
          .Where(i =&gt; i.First().ActionId == (int)SubscriptionAction.Subscription)
          .SelectMany(i =&gt; i.ToList())
          .Select(i =&gt; i.CellPhoneNumber)
          .Distinct();
      return result;
  }
  </code></pre>
  
  <p>and this function would be called another function for only counting:</p>
  
  <pre><code>var prepaidsCount = GetActiveCellPhoneNumbersUpToDate(serviceToken, DateTime.Date, true);
  var postPaidsCount = GetActiveCellPhoneNumbersUpToDate(serviceToken, DateTime.Date, false);
  </code></pre>
  
  <p>And when I execute it I see <a href="https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client" rel="nofollow noreferrer">a breaking change of EF 3.0</a>, that says:</p>
  
  <blockquote>
    <p>Processing of the LINQ expression 'i => i
        .ToList()' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See
    <a href="https://go.microsoft.com/fwlink/?linkid=2101433" rel="nofollow noreferrer">https://go.microsoft.com/fwlink/?linkid=2101433</a> for more detailed
    information.</p>
  </blockquote>
  
  <p>As stated in the breaking change notes, I need to user <code>AsEnumerable</code> or <code>ToList</code> before complex <code>Where</code> clauses to execute that portion of LINQ and bring data into RAM, then continue my query.</p>
  
  <p>But for large amounts of data with the need of dynamic queries, this is absolutely insane and unimaginably inefficient.</p>
  
  <p>What is the substitute for this? How can we create dynamic complex queries to be translated at runtime and only return a singular scalar value?</p>
  
  <p><strong>Update:</strong> Real-world requirements are not hello-world examples. They need complex filtering and sorting and grouping and other functions, mixed together, to extract data from a relational structure. In the past we would use Stored Procedures for those purposes. Passing a couple of parameters to database, and writing ugly, hard-to-test, far-from-maintainable, weekly-typed, refactor-resistant SQL code to fetch data.</p>
  
  <p>Now the only option that comes to my mind is downgrading back to those ugly Stored Procedures. Is this nightmare a reality in EF 3.1?</p>
  
  <p><strong>Update 2:</strong> This is my scenario. I have a table in which I store cell phone number subscription/cancellations in specific services. The simplified version of that table would be:</p>
  
  <pre><code>create table Subscriptions
  (
      Id,
      CellPhoneNumber,
      ServiceId,
      Date,
      ActionId
  )
  </code></pre>
  
  <p>And these can be records:</p>
  
  <pre><code>John,+1-541-754-3010,15,2019-10-13 12:10:06.153,1
  John,+1-541-754-3010,15,2019-10-18 12:10:06.153,2
  </code></pre>
  
  <p>Here we can see that John has subscribed to service 15, and has remained in it for 5 days, and then he has cancelled. If we want to report how many subscriber did we have at <em>2019-10-14</em>, John would be counted. Because in that time, his last action was to enroll. But if we want to report how many subscribers did we have at <code>2910-11-03</code> then John's last action was to leave the service and he should not be counted.</p>