title: EF Core 5 + Sql Server: slow query
body: <p>EF Core created a paginated query that searches data in a navigation property.
  The generated raw query is:</p>
  <pre><code>DECLARE @__p_1 varchar(40) = '%Ordine Rilasciato%';
  DECLARE @__p_2 int = 0;
  DECLARE @__p_3 int = 20;
  
  SELECT *
  FROM [RDA] AS [r]
  LEFT JOIN [StatoRDA] AS [s] ON (([r].[IdSocieta] = [s].[IdSocieta]) AND ([r].[CodiceStato] = [s].[CodiceStato])) AND ([r].[CodiceStatoOrigine] = [s].[CodiceStatoOrigine])
  WHERE LOWER([s].[DescrizioneStato]) LIKE @__p_1
  ORDER BY LOWER([s].[DescrizioneStato])
  OFFSET @__p_2 ROWS FETCH NEXT @__p_3 ROWS ONLY
  </code></pre>
  <p>this query takes up to 40 seconds to elaborate, but if I omit the declaration like this:</p>
  <pre><code>SELECT *
  FROM [RDA] AS [r]
  LEFT JOIN [StatoRDA] AS [s] ON (([r].[IdSocieta] = [s].[IdSocieta]) AND ([r].[CodiceStato] = [s].[CodiceStato])) AND ([r].[CodiceStatoOrigine] = [s].[CodiceStatoOrigine])
  WHERE LOWER([s].[DescrizioneStato]) LIKE '%Ordine Rilasciato%'
  ORDER BY LOWER([s].[DescrizioneStato])
  OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
  </code></pre>
  <p>the elaboration is immediate.</p>
  <p>Since the first query is generated by EF I have no control on it.</p>
  <p>Query code:</p>
  <pre><code>var qry = myContext.Rda
      .Where(x =&gt; EF.Functions.Like(x.StatoRda.DescrizioneStato.ToLower(), &quot;%&quot; + item.Valore + &quot;%&quot;))
      .OrderBy(x =&gt; x.StatoRda.DescrizioneStato.ToLower()
      .Skip((pageIndex - 1) * pageSize)
      .Take(pageSize)
  </code></pre>
  <p>Both filter and order are made on a related table</p>