Optimizing entity framework linq queries

Nested linq expression

My clientModel has a list of MotionDetections that contains a date. I want to get a list from the database where the min and max of this date are used.
The first code looks like this:

[code lang="csharp"]
// Get the existing values from this device between the clientModel dates
var motionDetections = _dataContext.MotionDetections
	.Where(a_item => a_item.DeviceID == clientModel.Device.ID &&
						a_item.DateTimeDetection >= clientModel.MotionDetections.Min(b_item => b_item.Date) &&
						a_item.DateTimeDetection <= clientModel.MotionDetections.Max(b_item => b_item.Date))
	.ToList();
[/code]

When executing it gives warnings like “The LINQ expression ‘Min()’ could not be translated and will be evaluated locally.”
The full log shows some more information as well

[code]
<pre>2017-07-30 15:41:19.491 +02:00 [Information] Executing action method "HouseDB.Controllers.Exporter.ExporterController.InsertMotionDetectionValues (HouseDB)" with arguments (["HouseDB.Controllers.Exporter.DomoticzMotionDetectionClientModel"]) - ModelState is Valid
2017-07-30 15:41:34.010 +02:00 [Debug] Compiling query model:
'from MotionDetection a_item in DbSet<MotionDetection>
where a_item.DeviceID == __clientModel_Device_ID_0 && a_item.DateTimeDetection >=
    (from DomoticzMotionDetection b_item in __clientModel_MotionDetections_1
    select b_item.Date)
    .Min() && a_item.DateTimeDetection <=
    (from DomoticzMotionDetection b_item in __clientModel_MotionDetections_2
    select b_item.Date)
    .Max()
select a_item'
2017-07-30 15:41:34.028 +02:00 [Debug] Optimized query model:
'from MotionDetection a_item in DbSet<MotionDetection>
where a_item.DeviceID == __clientModel_Device_ID_0 && a_item.DateTimeDetection >=
    (from DomoticzMotionDetection b_item in __clientModel_MotionDetections_1
    select b_item.Date)
    .Min() && a_item.DateTimeDetection <= (from DomoticzMotionDetection b_item in __clientModel_MotionDetections_2 select b_item.Date) .Max() select a_item' 2017-07-30 15:41:34.075 +02:00 [Warning] The LINQ expression 'Min()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider. 2017-07-30 15:41:34.081 +02:00 [Warning] The LINQ expression 'Max()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider. 2017-07-30 15:41:34.086 +02:00 [Warning] The LINQ expression '((([a_item].DeviceID == __clientModel_Device_ID_0) AndAlso ([a_item].DateTimeDetection >= {from DomoticzMotionDetection b_item in __clientModel_MotionDetections_1 select [b_item].Date => Min()})) AndAlso ([a_item].DateTimeDetection <= {from DomoticzMotionDetection b_item in __clientModel_MotionDetections_2 select [b_item].Date => Max()}))' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
2017-07-30 15:41:34.092 +02:00 [Warning] The LINQ expression 'Min()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
2017-07-30 15:41:34.095 +02:00 [Warning] The LINQ expression 'Max()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
2017-07-30 15:41:34.103 +02:00 [Debug] TRACKED: True
(QueryContext queryContext) => IEnumerable<MotionDetection> _Where(
    source: IEnumerable<MotionDetection> _ShapedQuery(
        queryContext: queryContext,
        shaperCommandContext: SelectExpression:
            SELECT `a_item`.`ID`, `a_item`.`DateDeleted`, `a_item`.`DateTimeDetection`, `a_item`.`DeviceID`, `a_item`.`Status`
            FROM `MotionDetection` AS `a_item`
        ,
        shaper: UnbufferedEntityShaper<MotionDetection>
    )
    ,
    predicate: (MotionDetection a_item) => a_item.DeviceID == long GetParameterValue(
        queryContext: queryContext,
        parameterName: "__clientModel_Device_ID_0"
    )
     && a_item.DateTimeDetection >= DateTime Min(
        source: IEnumerable<DateTime> _Select(
            source: List<DomoticzMotionDetection> GetParameterValue(
                queryContext: queryContext,
                parameterName: "__clientModel_MotionDetections_1"
            )
            ,
            selector: (DomoticzMotionDetection b_item) => b_item.Date
        )
    )
     && a_item.DateTimeDetection <= DateTime Max(
        source: IEnumerable<DateTime> _Select(
            source: List<DomoticzMotionDetection> GetParameterValue(
                queryContext: queryContext,
                parameterName: "__clientModel_MotionDetections_2"
            )
            ,
            selector: (DomoticzMotionDetection b_item) => b_item.Date
        )
    )
)
[/code]

Optimized code

I didn’t check the performance of the previous code, but it is clear that the filter cannot be executed on the SQL server (not even the deviceID filter).
That will mean that more results will be returned by the database then needed and filtered out later, this is an overhead that will cause decreasing performance.

The new code looks like:

[code lang="csharp"]
var minDate = clientModel.MotionDetections.Min(b_item => b_item.Date);
var maxDate = clientModel.MotionDetections.Max(b_item => b_item.Date);

// Get the existing values from this device between the clientModel dates
var motionDetections = _dataContext.MotionDetections
	.Where(a_item => a_item.DeviceID == clientModel.Device.ID &&
						a_item.DateTimeDetection >= minDate &&
						a_item.DateTimeDetection <= maxDate)
	.ToList();
[/code]

The full log from the new code

[code]
2017-07-30 15:30:02.038 +02:00 [Information] Executing action method "HouseDB.Controllers.Exporter.ExporterController.InsertMotionDetectionValues (HouseDB)" with arguments (["HouseDB.Controllers.Exporter.DomoticzMotionDetectionClientModel"]) - ModelState is Valid
2017-07-30 15:30:05.416 +02:00 [Debug] Compiling query model:
'from MotionDetection a_item in DbSet<MotionDetection>
where a_item.DeviceID == __clientModel_Device_ID_0 && a_item.DateTimeDetection >= __minDate_1 && a_item.DateTimeDetection <= __maxDate_2
select a_item'
2017-07-30 15:30:05.418 +02:00 [Debug] Optimized query model:
'from MotionDetection a_item in DbSet<MotionDetection>
where a_item.DeviceID == __clientModel_Device_ID_0 && a_item.DateTimeDetection >= __minDate_1 && a_item.DateTimeDetection <= __maxDate_2 select a_item' 2017-07-30 15:30:05.434 +02:00 [Debug] TRACKED: True (QueryContext queryContext) => IEnumerable<MotionDetection> _ShapedQuery(
    queryContext: queryContext,
    shaperCommandContext: SelectExpression:
        SELECT `a_item`.`ID`, `a_item`.`DateDeleted`, `a_item`.`DateTimeDetection`, `a_item`.`DeviceID`, `a_item`.`Status`
        FROM `MotionDetection` AS `a_item`
        WHERE ((`a_item`.`DeviceID` = @__clientModel_Device_ID_0) AND (`a_item`.`DateTimeDetection` >= @__minDate_1)) AND (`a_item`.`DateTimeDetection` <= @__maxDate_2)
    ,
    shaper: UnbufferedEntityShaper<MotionDetection>
)
[/code]