EF Core 3.1- Parameter does not follow the model definition used for the entity property

[shvmgpt116]

I have a sample application where I am applying code first migration.
Here is the entity definition.

public partial class MyTable
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
      modelBuilder.Entity<MyTable>(entity =>
      {
        entity.ToTable("MY_TABLE");

        entity.Property(e => e.Id)
                  .HasColumnName("ID")
                  .ValueGeneratedNever();

        entity.Property(e => e.FirstName)
                  .HasColumnName("FIRST_NAME")
                  .HasMaxLength(20)
                  .IsUnicode(false);

        entity.Property(e => e.LastName)
                  .HasColumnName("LAST_NAME")
                  .HasMaxLength(20)
                  .IsUnicode(false);
      });

      OnModelCreatingPartial(modelBuilder);
    }

Here is the table that gets created after migration.

CREATE TABLE [MY_TABLE] (
    [ID] int NOT NULL,
    [FIRST_NAME] varchar(20) NULL,
    [LAST_NAME] varchar(20) NULL,
    CONSTRAINT [PK_MY_TABLE] PRIMARY KEY ([ID])
);

After that I am executing following linq-

var item = _context.MyTable.Where(x => x.FirstName.StartsWith(firstName)).ToList();

Here is the query that gets executed.

Microsoft.EntityFrameworkCore.Database.Command: Debug: Executing DbCommand [Parameters=[@__firstName_0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT [m].[ID], [m].[FIRST_NAME], [m].[LAST_NAME]
FROM [MY_TABLE] AS [m]
WHERE (@__firstName_0 = N'') OR ([m].[FIRST_NAME] IS NOT NULL AND (LEFT([m].[FIRST_NAME], LEN(@__firstName_0)) = @__firstName_0))

Here I see the parameter that is created has incorrect size (4000) since FIRST_NAME can have max length only 20.
Also the parameter is being compared to a Unicode character even though IsUnicode(false) is set for the corresponding entity property.

If I execute following query which does not use 'StartWith()', then I don't see such issues,

var item = _context.MyTable.Where(x => x.FirstName == firstName).ToList();

Here is the generated sql-

Microsoft.EntityFrameworkCore.Database.Command: Debug: Executing DbCommand [Parameters=[@__firstName_0='?' (Size = 20) (DbType = AnsiString)], CommandType='Text', CommandTimeout='30']
SELECT [m].[ID], [m].[FIRST_NAME], [m].[LAST_NAME]
FROM [MY_TABLE] AS [m]
WHERE [m].[FIRST_NAME] = @__firstName_0

I found following github link which discusses about similar issue.
#4686
I am wandering why is there this mismatch for the parameter for two different but similar linq query.
There could potential performance issue if the non-unicode string type gets compared against unicode parameter or vice-versa.

Include provider and version information

EF Core version: 3.1.22
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 3.1
Operating system: Windows 10
IDE: Visual Studio 2019 16.11.2

[ajcvickers]

@shvmgpt116 This should be fixed in a more recent version of EF Core. Please update to EF Core 6.0.1, and report back if the issue still happens.

[shvmgpt116]

@ajcvickers I tried with EF Core 6.0.1, but the issue is reproducible there as well.
Here is the query that gets generated.

Microsoft.EntityFrameworkCore.Database.Command: Debug: Executing DbCommand [Parameters=[@__firstName_0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT [m].[ID], [m].[FIRST_NAME], [m].[LAST_NAME]
FROM [MY_TABLE] AS [m]
WHERE (@__firstName_0 = N'') OR (LEFT([m].[FIRST_NAME], LEN(@__firstName_0)) = @__firstName_0)

[ajcvickers]

@smitpatel Looks like parameter isn't getting inferred type mapping when StartsWith is used. Query from x => x.FirstName.StartsWith(firstName):

info: 1/4/2022 11:49:50.567 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (20ms) [Parameters=[@__firstName_0='Arthur' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SELECT [m].[ID], [m].[FIRST_NAME], [m].[LAST_NAME]
      FROM [MY_TABLE] AS [m]
      WHERE (@__firstName_0 = N'') OR ([m].[FIRST_NAME] IS NOT NULL AND (LEFT([m].[FIRST_NAME], LEN(@__firstName_0)) = @__firstName_0))

Query from x => x.FirstName == firstName:

info: 1/4/2022 11:49:03.034 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (19ms) [Parameters=[@__firstName_0='Arthur' (Size = 20) (DbType = AnsiString)], CommandType='Text', CommandTimeout='30']
      SELECT [m].[ID], [m].[FIRST_NAME], [m].[LAST_NAME]
      FROM [MY_TABLE] AS [m]
      WHERE [m].[FIRST_NAME] = @__firstName_0

Code:

public partial class MyTable
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Your.ConnectionString)
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    public DbSet<MyTable> MyTable { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyTable>(entity =>
        {
            entity.ToTable("MY_TABLE");

            entity.Property(e => e.Id)
                .HasColumnName("ID")
                .ValueGeneratedNever();

            entity.Property(e => e.FirstName)
                .HasColumnName("FIRST_NAME")
                .HasMaxLength(20)
                .IsUnicode(false);

            entity.Property(e => e.LastName)
                .HasColumnName("LAST_NAME")
                .HasMaxLength(20)
                .IsUnicode(false);
        });
    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var firstName = "Arthur";
            var item = context.MyTable.Where(x => x.FirstName.StartsWith(firstName)).ToList();
        }
    }
}

[smitpatel]

This is happening because of earlier part of (@__firstName_0 = N'') which assigns default facets to parameter. We don't add another parameter even though facets changed. This has been fixed in 7.0. We send 2 different parameters to server with appropriate type mapping assigned to each.

[shvmgpt116]

@ajcvickers If the application makes sure that the parameter cannot be empty string. For Ex:-

if (!string.IsNullOrEmpty(firstName))
    var item = context.MyTable.Where(x => x.FirstName.StartsWith(firstName)).ToList();

Then, is there any way to avoid generation of condition (@__firstName_0 = N'') in the sql?

[ajcvickers]

@shvmgpt116 As a workaround, you should be able to use DbFunctions.Like

[ajcvickers]

Same root cause as #19503.

[shvmgpt116]

This is happening because of earlier part of (@__firstName_0 = N'') which assigns default facets to parameter. We don't add another parameter even though facets changed. This has been fixed in 7.0. We send 2 different parameters to server with appropriate type mapping assigned to each.

@ajcvickers @smitpatel Please could you confirm if you are planning to fix the issue in EFCore 6.0 , 5.0 and 3.1.

[ajcvickers]

@shvmgpt116 We don't currently plan to patch this issue; the fix is not low-risk. See the planning process for how we determine which issues to patch.