title: Entity Framework request is slow with date parameter vs SQL query
body: <p>I'm in trouble with a simple entity request. I have tried a lot of things to improve its performance, and I can't find any solution. All the request have been tested with the same parameter date, and all the requests return exactly the same data. I also precise that the lazy loading is not in cause, the <code>ToList()</code> in the request is to test the request performance.</p>
  <p>Here the original request in entity:</p>
  <pre><code>var T = base.Context.Readings
                      .Where(r =&gt; r.ReadOn &gt;= pFrom &amp;&amp; r.ReadOn &lt;= pTo)
                      .Select(x =&gt; x.BatchDetail.Species)
                      .Distinct().ToList();
  </code></pre>
  <p>To resume I want to check the readings with dates, then take the species object in their <code>BatchDetails</code>.</p>
  <p>The SQL query for this request is:</p>
  <pre><code>SELECT 
      [Distinct1].[Species_ID] AS[ID],
      [Distinct1].[Code] AS[Code],
      [Distinct1].[Name] AS[Name]
  FROM
      (SELECT DISTINCT
           [Extent2].[Species_ID] AS[Species_ID],
           [Extent3].[Code] AS[Code],
           [Extent3].[Name] AS[Name],
       FROM 
           [dbo].[Readings] AS[Extent1]
       INNER JOIN
           [dbo].[BatchDetails] AS [Extent2] ON [Extent1].[BatchDetail_ID] = [Extent2].[idBatchDetail]
       INNER JOIN
           [dbo].[Species] AS [Extent3] ON [Extent2].[Species_ID] = [Extent3].[idSpecies]
       WHERE
           ([Extent1].[ReadOn] &gt;= @p0) AND([Extent1].[ReadOn] &lt;= @p1)
  ) AS [Distinct1]
  </code></pre>
  <p>If I execute the Entity Framework request, I get a respones within 5 seconds.</p>
  <p>If I execute the SQL request on SQL Server by replacing @p0 and @p1 by '2021-01-01 hh:mm:ss' (the date), it is only 0.4 seconds.</p>
  <p>I also tried to do this:</p>
  <pre><code>    var sqlP1 = new SqlParameter();
      sqlP1.ParameterName = &quot;@p0&quot;;
      sqlP1.Value = pFrom;
      sqlP1.DbType = DbType.DateTime;
      sqlP1.Direction = ParameterDirection.Input;
  
      var sqlP2 = new SqlParameter();
      sqlP2.ParameterName = &quot;@p1&quot;;
      sqlP2.Value = pTo;
      sqlP2.DbType = DbType.DateTime;
      sqlP2.Direction = ParameterDirection.Input;
  
      var Z = base.Context.Species.SqlQuery(@&quot;SELECT 
              [Distinct1].[Species_ID] AS[ID],
              [Distinct1].[Code] AS[Code],
              [Distinct1].[Name] AS[Name]
              FROM(SELECT DISTINCT
                  [Extent2].[Species_ID] AS[Species_ID],
                  [Extent3].[Code] AS[Code],
                  [Extent3].[Name] AS[Name],
                  FROM[dbo].[Readings] AS[Extent1]
                  INNER JOIN[dbo].[BatchDetails] AS[Extent2] ON[Extent1].[BatchDetail_ID] = [Extent2].[idBatchDetail]
                  INNER JOIN[dbo].[Species] AS[Extent3] ON[Extent2].[Species_ID] = [Extent3].[idSpecies]
                  WHERE([Extent1].[ReadOn] &gt;= @p0) AND([Extent1].[ReadOn] &lt;= @p1)
              )  AS[Distinct1]
              &quot;, sqlP1 , sqlP2 ).ToList();
  </code></pre>
  <p>This also takes 5 seconds (like the first execution).</p>
  <p>And this:</p>
  <pre><code>    var Z = base.Context.Species.SqlQuery(@&quot;SELECT 
              [Distinct1].[Species_ID] AS[ID],
              [Distinct1].[Code] AS[Code],
              [Distinct1].[Name] AS[Name]
              FROM(SELECT DISTINCT
                  [Extent2].[Species_ID] AS[Species_ID],
                  [Extent3].[Code] AS[Code],
                  [Extent3].[Name] AS[Name],
                  FROM[dbo].[Readings] AS[Extent1]
                  INNER JOIN[dbo].[BatchDetails] AS[Extent2] ON[Extent1].[BatchDetail_ID] = [Extent2].[idBatchDetail]
                  INNER JOIN[dbo].[Species] AS[Extent3] ON[Extent2].[Species_ID] = [Extent3].[idSpecies]
                  WHERE([Extent1].[ReadOn] &gt;= '2022-01-01 00:00:00') AND([Extent1].[ReadOn] &lt;= '2022-07-01 00:00:00')
              )  AS[Distinct1]
        ).ToList();
  </code></pre>
  <p>this one is 0.4 sec (like the request in SQL Server).</p>
  <p>I saw that entity could have some troubles with format conversion (I saw it for <code>varchar</code> and <code>nvarchar</code>), but I don't understand why, if I pass the date in param in the request, it takes 10x more time than passing them in string in the request.</p>
  <p>Do you have an explanation? Or do you have any idea how I can resolve this?</p>
  <p>Thanks in advance.</p>
  <p>Edit: I forgot to mention that I also tried <code>WithNoTracking</code> but there is no change in the performances.</p>