title: How to efficiently rewrite LINQ expression 'DbSet.Where(...Any(...))' that could not be translated in EFCore 3.1?
body: <p>I have been solving the issues with the infamous <a href="https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.x/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client" rel="nofollow noreferrer">breaking changes</a> of an Entity Framework Core3 upgrade. I used to have a working-well query like this:</p>
  <pre class="lang-cs prettyprint-override"><code>// Assume someOtherList is a list populated here
  var existingItemsList = myDbSet
      .Where(s =&gt; someOtherList.Any(i =&gt; s.Name == i.Name &amp;&amp; s.Id == i.RefId))
      .Select(s =&gt; s.Id)
      .ToList();
  </code></pre>
  <p>But it now throws:</p>
  <pre><code>System.InvalidOperationException: The LINQ expression 'DbSet&lt;MyItem&gt;
      .Where(i =&gt; __someOtherList_0
          .Any(i =&gt; i.Name == i.Name &amp;&amp; i.Id == i.RefId))' could not be translated.
  Either rewrite the query in a form that can be translated, or switch to 
  client evaluation explicitly by inserting a call to either
  AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().
  See https://go.microsoft.com/fwlink/?linkid=2101038  for more information.
  </code></pre>
  <p>How should I re-write my LINQ query ensuring its efficiency (i.e. do not download all the data to client before the filter)?</p>
  <p>P.S: It turned out that I overlooked multiple uses of similar lines, the following statement the OP contained is not true: <s>To experiment neglecting the performance concern, I have tried inserting <code>AsEnumerable()</code> before and after the <code>Where()</code> statement; but the exact same error did not cease to exist.</s></p>