Optimize index on a 2,135,044,521 row table
You might be able to reduce PAGEIOLATCH_SH waits for this query if you're able to change the data types generated by the ORM. The Timestamp column in your table has a data type of DATETIME but the parameters @p__linq__1 and @p__linq__2 have data types of DATETIME2(7). That difference is why the query plan for the ORM queries is so much more complicated than the first query plan that you posted that had hardcoded search filters. You can get a hint of this in the XML as well:

&lt;ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@p__linq__1],NULL,(22))"&gt;


As is, with the ORM query you cannot get any partition elimination. You will get at least a few logical reads for every partition that's defined in the partition function, even if you're just searching for a day of data. Within each partition you get an index seek so it doesn't take long for SQL Server to move on to the next partition, but perhaps all of that IO is adding up.

I did a simple reproduction to be sure. There are 11 partitions defined within the partition function. For this query:

DECLARE @p__linq__0 bigint = 2000;
DECLARE @p__linq__1 datetime2(7) = '20180103';
DECLARE @p__linq__2 datetime2(7) = '20180104';

SELECT 1 AS [C1]
, [Extent1].[Timestamp] AS [Timestamp]
, [Extent1].[Value1] AS [Value1]
FROM [DemoUnitData] AS [Extent1]  
WHERE ([Extent1].[UnitID] = @p__linq__0)  
AND ([Extent1].[Timestamp] &gt;= @p__linq__1)  
AND ([Extent1].[Timestamp] &lt; @p__linq__2)
OPTION (MAXDOP 1) ;


Here's what IO looks like:


  Table 'DemoUnitData'. Scan count 11, logical reads 40


When I fix the data types:

DECLARE @p__linq__0 bigint = 2000;
DECLARE @p__linq__1 datetime = '20180103';
DECLARE @p__linq__2 datetime = '20180104';

SELECT 1 AS [C1]
, [Extent1].[Timestamp] AS [Timestamp]
, [Extent1].[Value1] AS [Value1]
FROM [DemoUnitData] AS [Extent1]  
WHERE ([Extent1].[UnitID] = @p__linq__0)  
AND ([Extent1].[Timestamp] &gt;= @p__linq__1)  
AND ([Extent1].[Timestamp] &lt; @p__linq__2)
OPTION (MAXDOP 1) ;


IO is reduced as a result of partition elimination:


  Table 'DemoUnitData'. Scan count 2, logical reads 8