Execution time high for Query in sp_executesql
The query optimizer is able to use parameter sniffing for the dynamic query as can be seen in the actual plan:

&lt;ParameterList&gt;
  &lt;ColumnReference Column="@p__linq__5" ParameterDataType="varchar(8000)" ParameterCompiledValue="'MI3300287'" ParameterRuntimeValue="'MI3300287'" /&gt;
  &lt;ColumnReference Column="@p__linq__4" ParameterDataType="datetime2(7)" ParameterCompiledValue="'2009-06-11 23:59:00.0000000'" ParameterRuntimeValue="'2009-06-11 23:59:00.0000000'" /&gt;
  &lt;ColumnReference Column="@p__linq__3" ParameterDataType="datetime2(7)" ParameterCompiledValue="'2008-06-11 00:00:00.0000000'" ParameterRuntimeValue="'2008-06-11 00:00:00.0000000'" /&gt;
  &lt;ColumnReference Column="@p__linq__2" ParameterDataType="varchar(8000)" ParameterCompiledValue="'MI3300287'" ParameterRuntimeValue="'MI3300287'" /&gt;
  &lt;ColumnReference Column="@p__linq__1" ParameterDataType="int" ParameterCompiledValue="(90495)" ParameterRuntimeValue="(90495)" /&gt;
  &lt;ColumnReference Column="@p__linq__0" ParameterDataType="int" ParameterCompiledValue="(90495)" ParameterRuntimeValue="(90495)" /&gt;
&lt;/ParameterList&gt;


Those sniffed values can influence the query plan that's chosen. However, the query plan still needs to be safe for all possible values of the input variables. Let's look at one difference between the plans, the access on the IncidentDetailsPage_Header table. In the slower, dynamic plan you get a scan and a hash match which in total takes about 2.4 seconds:



In the faster plan with the hardcoded values you get an index seek instead which takes about 0 ms:



Here's the T-SQL that corresponds to that part of the plan:

 AND (([Extent1].[agencyori] = @p__linq__5)
      OR (([Extent1].[agencyori] IS NULL)
          AND (@p__linq__5 IS NULL)))


Hardcoding the values results in a single use query plan just for those parameter values. With your parameter values, that T-SQL can be simplified to this:

([Extent1].[AgencyOri] = 'MI3300287')


That is eligible for an index seek. The other code is not. Avoid kitchen sink queries when you can.