dotnet / dotnet/efcore

string.StartsWith(char) with wild char translates to incorrect LIKE pattern on SQL Server

Open
#38,923 1 comment 0 reactions 1 assignee Claimed by @AndriySvyryd View on GitHub
area-query customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

### Bug description

### Repro:

Build a DB context with UseSqlServer options having an entity with a string column.

Query using a `string.StartsWith(char)` filter for values starting with underscore or other LIKE wild char, e.g.:
`db.MyEntities.Where(i => i.StringColumn.StartsWith('_'))`...

Expect: SQL containing: `WHERE [i].[StringColumn] LIKE N'\_%' ESCAPE N'\'`

Actual: SQL contains: `WHERE [i].[StringColumn] LIKE N'187%' ESCAPE N'\'`

### Impact:

The query runs, but includes/excludes the wrong rows.

### Workaround:

Use the `string.StartsWith(string)` overload, e.g. `.StartsWith("_")`, rather than the char overload.

Note, the C# code analyzers encourage the use of the char overload.

### Analysis:

This appears to be caused by the escape and wild char being added rather than concatenated,
e.g. 187 = `'\'` (92) + `'_'` (95), then the resulting integer being concatenated with "%".

See: src/EFCore.SqlServer/Query/Internal/SqlServerSqlTranslatingExpressionVisitor.cs

In latest main branch, same code in 2 places (for constant and parameter), switch with char s:

L581: `StartsEndsWithContains.StartsWith => LikeEscapeChar + s + "%",`
L765: `StartsEndsWithContains.StartsWith => LikeEscapeChar + s + "%",`

Note, `EndsWith` and `Contains` appear to work correctly.

### Possible fix:

Change similar to how `Contains` works a couple of lines down, i.e. `$"{LikeEscapeChar}{s}%"`.

### Your code

```csharp
// C# example mostly generated by Claude Code
//

using System.ComponentModel.DataAnnotations;
using System.Linq.Expressions;
using EFUnderscoreIssue;
using Microsoft.EntityFrameworkCore;

var options = new DbContextOptionsBuilder()
.UseSqlServer("Server=.;Database=XXX;Trusted_Connection=True;TrustServerCertificate=True;")
.Options;

using var db = new AppDbContext(options);

void Show(string label, Expression> whereClause)
{
IQueryable query = db.MyEntities.Where(whereClause);
string sql = query.ToQueryString();
// Console.WriteLine(sql);

// Pull out the DECLARE line (parameterised patterns) and the WHERE clause.
var declare = sql.Split('\n').FirstOrDefault(l => l.StartsWith("DECLARE"))?.Trim() ?? "";
var where = sql.Split('\n').FirstOrDefault(l => l.TrimStart().StartsWith("WHERE"))?.Trim() ?? "";

Console.WriteLine($"{label,-32} {where}");
if (declare.Length > 0)
{
Console.WriteLine($"{"",-32} {declare}");
}
}

char underscore = '_';
string underscoreText = "_";

Console.WriteLine("=== constant argument ===");
Show("StartsWith('_') char", i => i.StringColumn.StartsWith('_'));
Show("StartsWith(\"_\") string", i => i.StringColumn.StartsWith("_"));
Show("StartsWith('%') char", i => i.StringColumn.StartsWith('%'));
Show("StartsWith('[') char", i => i.StringColumn.StartsWith('['));
Show("StartsWith('A') char", i => i.StringColumn.StartsWith('A'));
Show("EndsWith('_') char", i => i.StringColumn.EndsWith('_'));
Show("Contains('_') char", i => i.StringColumn.Contains('_'));

Console.WriteLine();
Console.WriteLine("=== captured variable (parameterised) ===");
Show("StartsWith(char) '_'", i => i.StringColumn.StartsWith(underscore));
Show("StartsWith(string) \"_\"", i => i.StringColumn.StartsWith(underscoreText));
Show("EndsWith(char) '_'", i => i.StringColumn.EndsWith(underscore));

namespace EFUnderscoreIssue
{
public class MyEntity
{
[Key]
public int Id { get; set; }
public string StringColumn { get; set; } = string.Empty;
}

public class AppDbContext(DbContextOptions options) : DbContext(options)
{
public DbSet MyEntities { get; set; }
}
}
```

### Stack traces

```text

```

### Verbose output

```text
=== constant argument ===
StartsWith('_') char WHERE [m].[StringColumn] LIKE N'187%' ESCAPE N'\'
StartsWith("_") string WHERE [m].[StringColumn] LIKE N'\_%' ESCAPE N'\'
StartsWith('%') char WHERE [m].[StringColumn] LIKE N'129%' ESCAPE N'\'
StartsWith('[') char WHERE [m].[StringColumn] LIKE N'183%' ESCAPE N'\'
StartsWith('A') char WHERE [m].[StringColumn] LIKE N'A%'
EndsWith('_') char WHERE [m].[StringColumn] LIKE N'%\_' ESCAPE N'\'
Contains('_') char WHERE [m].[StringColumn] LIKE N'%\_%' ESCAPE N'\'

=== captured variable (parameterised) ===
StartsWith(char) '_' WHERE [m].[StringColumn] LIKE @underscore_startswith ESCAPE N'\'
DECLARE @underscore_startswith nvarchar(4000) = N'187%';
StartsWith(string) "_" WHERE [m].[StringColumn] LIKE @underscoreText_startswith ESCAPE N'\'
DECLARE @underscoreText_startswith nvarchar(4000) = N'\_%';
EndsWith(char) '_' WHERE [m].[StringColumn] LIKE @underscore_endswith ESCAPE N'\'
DECLARE @underscore_endswith nvarchar(4000) = N'%\_';
```

### EF Core version

10.0.11

### Database provider

Microsoft.EntityFrameworkCore.SqlServer

### Target framework

.NET 10

### Operating system

Windows 11

### IDE

Visual Studio 2026 18.6.2

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.