search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.77k stars 3.19k forks source link

Parameter value mixup #32932

Open samuel-utbult-oborgen opened 9 months ago

samuel-utbult-oborgen commented 9 months ago

I wrote two manual SQL queries with parameters with the same names but with different values like this:

var result = await Enumerable
    .Range(1, 2)
    .Select(index => dbContext
        .Set<TestEntity>()
        .FromSqlRaw(
            "SELECT @date AS Date",
            new SqlParameter("date", new DateOnly(2024, 1, index))))
    .Aggregate(Queryable.Union)
    .ToListAsync();

foreach (var line in result)
{
    Console.WriteLine(line.Date);
}

I expected to get two results with 2024-01-01 and 2024-01-02 but got only the result 2024-01-01. However, this works as expected:

var result = await Enumerable
    .Range(1, 2)
    .Select(index => dbContext
        .Set<TestEntity>()
        .FromSqlRaw(
            "SELECT {0} AS Date",
            new DateOnly(2024, 1, index)))
    .Aggregate(Queryable.Union)
    .ToListAsync();

foreach (var line in result)
{
    Console.WriteLine(line.Date);
}

I have created an example project showcasing the bug: https://github.com/samuel-utbult-oborgen/ef-core-bug I suspect this could be because the variable "date" is created for the combined query and its value is set to 2024-01-01 while the value 2024-01-02 is ignored. The parameters should reasonably be isolated between the queries as they are specified for each query respectively.

EF Core version: 8.0.1 Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer) Target framework: (e.g. .NET 8.0) Operating system: macOS Sonoma 14.2.1

roji commented 9 months ago

Regardless of the actual issue above, be aware that this code executes a separate query for each value in the range, doing many sequential roundtrips, which is slow. Instead, consider just creating an array of your DateTimes and doing a single query with Contains over that array.

samuel-utbult-oborgen commented 9 months ago

Regardless of the actual issue above, be aware that this code executes a separate query for each value in the range, doing many sequential roundtrips, which is slow. Instead, consider just creating an array of your DateTimes and doing a single query with Contains over that array.

The provided example is the most simple one I could come up with that showcases the bug. The production code where this bug was found is considerably more complicated and there is an actual reason why such an SQL parameter is useful in that case.

ajcvickers commented 9 months ago

Debug query output for the first case, which ultimately returns one result. (Note that the database contains no data.)

dbug: 2/1/2024 10:48:54.969 CoreEventId.QueryCompilationStarting[10111] (Microsoft.EntityFrameworkCore.Query) 
      Compiling query expression:
      'DbSet<TestEntity>().FromSql(SELECT @date AS Date, __p_0)
          .Union(DbSet<TestEntity>().FromSql(SELECT @date AS Date, __p_1)
          )'
dbug: 2/1/2024 10:48:55.191 CoreEventId.QueryExecutionPlanned[10107] (Microsoft.EntityFrameworkCore.Query) 
      Generated query execution expression:
      'queryContext => new SingleQueryingEnumerable<TestEntity>(
          (RelationalQueryContext)queryContext,
          RelationalCommandCache.QueryExpression(
              Projection Mapping:
                  EmptyProjectionMember -> Dictionary<IProperty, int> { [Property: TestEntity.Date (DateOnly) Required, 0] }
              SELECT t0.Date
              FROM (
                  SELECT t.Date
                  FROM SELECT @date AS Date
                  UNION
                  SELECT t1.Date
                  FROM SELECT @date AS Date
              ) AS t0
              ),
          null,
          Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, TestEntity>,
          SomeDbContext,
          False,
          False,
          True
      )'
info: 2/1/2024 10:48:55.254 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (32ms) [Parameters=[date='01/01/2024' (DbType = Date)], CommandType='Text', CommandTimeout='30']
      SELECT [t].[Date]
      FROM (
          SELECT @date AS Date
      ) AS [t]
      UNION
      SELECT [t1].[Date]
      FROM (
          SELECT @date AS Date
      ) AS [t1]

Debug query output for the second case, which ultimately returns two results. (Note that the database contains no data here either.)

dbug: 2/1/2024 10:44:51.612 CoreEventId.QueryCompilationStarting[10111] (Microsoft.EntityFrameworkCore.Query) 
      Compiling query expression:
      'DbSet<TestEntity>().FromSql(SELECT {0} AS Date, __p_0)
          .Union(DbSet<TestEntity>().FromSql(SELECT {0} AS Date, __p_1)
          )'
dbug: 2/1/2024 10:44:51.821 CoreEventId.QueryExecutionPlanned[10107] (Microsoft.EntityFrameworkCore.Query) 
      Generated query execution expression:
      'queryContext => new SingleQueryingEnumerable<TestEntity>(
          (RelationalQueryContext)queryContext,
          RelationalCommandCache.QueryExpression(
              Projection Mapping:
                  EmptyProjectionMember -> Dictionary<IProperty, int> { [Property: TestEntity.Date (DateOnly) Required, 0] }
              SELECT t0.Date
              FROM (
                  SELECT t.Date
                  FROM SELECT {0} AS Date
                  UNION
                  SELECT t1.Date
                  FROM SELECT {0} AS Date
              ) AS t0
              ),
          null,
          Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, TestEntity>,
          SomeDbContext,
          False,
          False,
          True
      )'
info: 2/1/2024 10:44:51.887 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (30ms) [Parameters=[p0='01/01/2024' (DbType = Date), p1='01/02/2024' (DbType = Date)], CommandType='Text', CommandTimeout='30']
      SELECT [t].[Date]
      FROM (
          SELECT @p0 AS Date
      ) AS [t]
      UNION
      SELECT [t1].[Date]
      FROM (
          SELECT @p1 AS Date
      ) AS [t1]
samuel-utbult-oborgen commented 9 months ago

Debug query output for the first case, which ultimately returns one result. (Note that the database contains no data.)

dbug: 2/1/2024 10:48:54.969 CoreEventId.QueryCompilationStarting[10111] (Microsoft.EntityFrameworkCore.Query) 
      Compiling query expression:
      'DbSet<TestEntity>().FromSql(SELECT @date AS Date, __p_0)
          .Union(DbSet<TestEntity>().FromSql(SELECT @date AS Date, __p_1)
          )'
dbug: 2/1/2024 10:48:55.191 CoreEventId.QueryExecutionPlanned[10107] (Microsoft.EntityFrameworkCore.Query) 
      Generated query execution expression:
      'queryContext => new SingleQueryingEnumerable<TestEntity>(
          (RelationalQueryContext)queryContext,
          RelationalCommandCache.QueryExpression(
              Projection Mapping:
                  EmptyProjectionMember -> Dictionary<IProperty, int> { [Property: TestEntity.Date (DateOnly) Required, 0] }
              SELECT t0.Date
              FROM (
                  SELECT t.Date
                  FROM SELECT @date AS Date
                  UNION
                  SELECT t1.Date
                  FROM SELECT @date AS Date
              ) AS t0
              ),
          null,
          Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, TestEntity>,
          SomeDbContext,
          False,
          False,
          True
      )'
info: 2/1/2024 10:48:55.254 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (32ms) [Parameters=[date='01/01/2024' (DbType = Date)], CommandType='Text', CommandTimeout='30']
      SELECT [t].[Date]
      FROM (
          SELECT @date AS Date
      ) AS [t]
      UNION
      SELECT [t1].[Date]
      FROM (
          SELECT @date AS Date
      ) AS [t1]

Debug query output for the second case, which ultimately returns two results. (Note that the database contains no data here either.)

dbug: 2/1/2024 10:44:51.612 CoreEventId.QueryCompilationStarting[10111] (Microsoft.EntityFrameworkCore.Query) 
      Compiling query expression:
      'DbSet<TestEntity>().FromSql(SELECT {0} AS Date, __p_0)
          .Union(DbSet<TestEntity>().FromSql(SELECT {0} AS Date, __p_1)
          )'
dbug: 2/1/2024 10:44:51.821 CoreEventId.QueryExecutionPlanned[10107] (Microsoft.EntityFrameworkCore.Query) 
      Generated query execution expression:
      'queryContext => new SingleQueryingEnumerable<TestEntity>(
          (RelationalQueryContext)queryContext,
          RelationalCommandCache.QueryExpression(
              Projection Mapping:
                  EmptyProjectionMember -> Dictionary<IProperty, int> { [Property: TestEntity.Date (DateOnly) Required, 0] }
              SELECT t0.Date
              FROM (
                  SELECT t.Date
                  FROM SELECT {0} AS Date
                  UNION
                  SELECT t1.Date
                  FROM SELECT {0} AS Date
              ) AS t0
              ),
          null,
          Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, TestEntity>,
          SomeDbContext,
          False,
          False,
          True
      )'
info: 2/1/2024 10:44:51.887 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (30ms) [Parameters=[p0='01/01/2024' (DbType = Date), p1='01/02/2024' (DbType = Date)], CommandType='Text', CommandTimeout='30']
      SELECT [t].[Date]
      FROM (
          SELECT @p0 AS Date
      ) AS [t]
      UNION
      SELECT [t1].[Date]
      FROM (
          SELECT @p1 AS Date
      ) AS [t1]

A clear observation to make in the debug output is that one parameter date exists in the first query but two parameters p0 and p1 exists in the second query. This conforms to my speculation above. EF Core knows how to separate between {0} yet it does not know how to separate between @date. I hope this provides enough information for the developers of EF Core to know how this bug could be fixed.