title: Entity Framework LINQ contains not case insensitive
body: <p>I'm having some 'tear my hair out'-problem with Entity Framework and I just can't find a solution.</p>
  
  <p>What I want to do is compare strings for a search function I'm running on the server. It's basically: <code>collection.Where(c =&gt; c.Name.Contains(searchTerm));</code> where searchTerm is a string passed by the user.</p>
  
  <p>Everywhere I look it's either:</p>
  
  <ol>
  <li><p>Change both string with toUpper(), or</p></li>
  <li><p>Set collation to a Case Insensitive one.</p></li>
  </ol>
  
  <p>However neither of these apply to my case. Here's a similar question which doesn't have an answer: <a href="https://stackoverflow.com/questions/14400856/entity-framework-case-insensitive-contains">Entity Framework - case insensitive Contains?</a></p>
  
  <p>Using the first alternative would result in getting every row in the database, and then perform toUpper(), to see if it's a match. This is unacceptable performance-wise.</p>
  
  <p>The second approach seems more likely to be a valid solution, but does for some reason not work. I have two databases. One local and one remote. The remote MSSQL database is set to collation: Finnish_Swedish_CI_AS, which means it's case insensitive? The local database is an auto-generated localDB with the property "Case Sensitive" set to False.</p>
  
  <p>No matter which of these two databases I use it's still always Case Sensitive for the users.</p>
  
  <p>Can someone please explain why this is happening so I can go on with my miserable life?</p>
  
  <p>Kind regards,
  Robin Dorbell</p>