title: Strategy for using EF Core query, SQL and local
body: <p><strong>Background:</strong></p>
  <p>I have two tables, stored in SQL Server, that contain a list of items (about 50,000 items) and localized (translated to specific languages) text associated with each item. So, this is a parent/child (1 &quot;item&quot; has 1 or more &quot;localized data&quot;) relationship between these tables.</p>
  <p>Now, a user is going to start typing in a text box on my ASP.NET Core website, and I want to provide culture-aware (translated to the user's language if possible) drop-down &quot;suggestion&quot; text, derived from those tables, based on the characters the user is typing.</p>
  <p>So, as the user is typing in the text box, some JavaScript is going to be asking my server to fetch a list of text strings based on the characters the user has entered. This means that my server needs to fetch potentially matching records from the database and then do some more processing to find the records with actual matches in the user's language, and then transform those matches to a list of text strings I can return to the browser. <strong>In order to provide a good user experience, this round-trip to my server wants to be a fast as possible.</strong></p>
  <p>As I see it, this is probably a two-step process:</p>
  <ol>
  <li>Fetch data from SQL server using a Linq to Entities query</li>
  <li>Transform the records I got from SQL Server to the final list of strings using logic that can't be executed directly on SQL Server (i.e., C# code that is incompatible with the Linq to Entities query).</li>
  </ol>
  <p><strong>The actual question</strong></p>
  <p>When fetching data from SQL Server, I have a couple of obvious options:</p>
  <ol>
  <li><p>I can try to be smart and include logic in my Linq to Entities query that fetches only records that are potential matches to the user's text. Because of the complexity of the required matching algorithm, I can't perform the complete matching criteria in this query, but I can significantly reduce the size of the record set returned by the query.</p>
  </li>
  <li><p>I can use a simple, unchanging query to fetch ALL of the records from the SQL Server.</p>
  </li>
  </ol>
  <p><strong>Option 1</strong> forces a round-trip to SQL server for the query to execute. But the next step, where I operate on the returned data in-memory using complete C# semantics, has a reduced data set to process.</p>
  <p><strong>Option 2</strong> may be faster since the query results will hopefully be cached. The next step, operating on the in-memory data, may require more work (since it will need to look at the entire data set), but the total elapsed time to return results may be faster.</p>
  <p>So, I have two questions:</p>
  <ol>
  <li><p>Is my analysis (above) correct? Did I get anything horribly wrong?</p>
  </li>
  <li><p>Short of writing both versions and testing them, is it obvious to anyone which method is the clear winner?</p>
  </li>
  </ol>