Efcore: Could "single entity by ID" queries using System.Linq.Expressions also be optimized?

[Casimodo72]

Edited: @tuespetre saved my day by pointing out that the key to this issue is to use
```c#
var predicate = Expression.Lambda>(
Expression.Equal(sourceIdPropExpr, Expression.Property(Expression.Constant(new { sourceId }), "sourceId")), sourceParamExpr);

instead of
```c#
var predicate = Expression.Lambda<Func<TSource, bool>>(
    Expression.Equal(sourceIdPropExpr, Expression.Constant(sourceId)), sourceParamExpr);

He provides an explanation further down.

Original issue

Using a predicate built with System.Linq.Expressions in order to get an entity by ID seems to be not optimized, while the following ways are optimized:
1) the Find method on DbContext/DbSet
2) hard-coded "First(x => x.Id == id)" expressions
3) parameterized FromSql "select * from Contracts where Id = {id}"

Could queries using System.Linq.Expressions also be optimized that way?

This was significantly impacting data migrations until I identified the bottleneck and switched to "Find" and "FromSql" with parameterized SQL.

E.g. the following query takes ~28 seconds to read 1010 entities - one by one.
(Tracking is disabled with ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking)
(The optimized strategies need only ~1.7 seconds)

• TSource in an entity type parameter
• sourceId is a Guid
  ```c#
  var sourceParamExpr = Expression.Parameter(typeof(TSource), "x");
  var sourceIdPropExpr = Expression.Property(sourceParamExpr, typeof(TSource).GetProperty("Id"));
  var predicate = Expression.Lambda>(
  Expression.Equal(sourceIdPropExpr, Expression.Constant(sourceId)), sourceParamExpr);

var sourceEntity = dbcontext.Contracts.First(predicate);

The generated SQL:
```sql
SELECT TOP(1) [source].[Id], (more fields)
FROM [Contracts] AS [source]
WHERE [source].[Id] = '0fea4256-8698-4010-b622-2b81537149c5'

Times:
Read count: 1010
Read times total: 28,049
Distinct read times: 0,4 0,036 0,023 0,024 0,021 0,019 0,022 0,018 0,02 0,017 0,029 0,031 0,028 0,03 0,025 0,063 0,033 0,026 0,035 0,057 0,027 0,032 0,037 0,042 0,034 0,043 0,05 0,044 0,04 0,045 0,048 0,055 0,051 0,039 0,052 0,038 0,049 0,053 0,041 0,047 0,058 0,161 0,068

While the optimized strategies produce SQL like the following:

exec sp_executesql N'SELECT TOP(1) [e].[Id], (more fields)
FROM [Contracts] AS [e]
WHERE [e].[Id] = @__get_Item_0',N'@__get_Item_0 uniqueidentifier',@__get_Item_0='FF1CDCB8-553E-4AB9-893D-47FA39D65A30'

Times:
Read count: 1010
Read times total: 1,689
Distinct read times: 0,312 0,013 0,002 0,001 0,006 0,003 0,008 0,004

Using:
EF Core version: 2.2.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.9.4

[Casimodo72]

Additional info: SQL Server profiler shows a 20-30 ms gap between the select query and "exec sp_reset_connection" for the non-optimized scenario. There's almost no gap for the optimized scenario.
It seems it is the entity framework which fiddles longer with the SQL result in the non-optimized scenario or it does fiddle with the .net query before it produces the SQL (dunno if sp_reset_connection is called before or after a query).
(All SQL queries finish within < 1ms).

20-30 ms gap:

almost no gap:

[Casimodo72]

Also my migration now consumes way less memory. Before, the .net core host went up to 2.x GB, while now it stays at 130 MB.
I now assume that ef core is compiling every query (really every query, even if just the ID changes) in the non-optimized scenario. That would explain why there's a gap and also why it consumes that much more memory. Is my assumption correct?

[tuespetre]

If I remember correctly, simple constants of translatable type are just not parameterized at all.

1. The Find method compares the entity property to an expression that reads the key value from a value buffer object, so it is not a simple constant and is thus parameterized. This can be seen in the EntityFinder class
2. In an expression like query.First(x => x.Id == id), id is not a constant expression (unless id is the name of a const!) Instead, id is a MemberExpression accessing the id property of a closure object, and that will be parameterized by the query compiler.
3. FromSql needs no explanation 🍰

I think the reason constants are not parameterized is because they are typically going to be constant literals, set in stone, no need to really change. Like, if a constant expression exists in a query tree, it is either a true-blue constant expression or someone dynamically added it like you just did. If you want to ensure that it gets parameterized the way you are using it, you might try something like Expression.Property(Expression.Constant(new { sourceId }), "sourceId") instead of just Expression.Constant(sourceId).

[Casimodo72]

@tuespetre: Perfect!

Read count: 1010
Read times total: 0,683
Distinct read times: 0,088 0,006 0,001 0,002 0 0,003 0,004
(It became faster than 1.7 seconds because my laptop is attached to the great stream of power now)

Tiny change, big improvement. I hope this issue helps me and other under-educated folks in the realm on LINQ expressions to avoid having to walk around the blocks while waiting for data migrations to finish.

(by the way: thanks for the wkhtmltopdf wrapper as well :-))