elsa-workflows / elsa-workflows/elsa-core
SqlException: Incorrect syntax near '$' when using WorkflowDefinitionFilter.DefinitionIds with EF Core 9 and SQL Server compatibility_level < 160
- Dominant language
- C#
- Stars
- 7.9k
- Forks
- 1.5k
- Avg merge
- 15h 22m
- Merged PRs (30d)
- 114
Description
## Description
When using `WorkflowDefinitionFilter` with `DefinitionIds` in Elsa with EF Core 9, a SQL exception occurs on SQL Server databases with `compatibility_level < 160`:
```
Microsoft.Data.SqlClient.SqlException: 'Incorrect syntax near '$'.'
```
This happens because EF Core 9 generates queries with `OPENJSON` and `$` syntax when using `Contains()` with collections, which requires SQL Server compatibility level 160 or higher (SQL Server 2022+).
## Steps to Reproduce
1. **Set up SQL Server with lower compatibility level**:
```sql
ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 120;
```
2. **Execute the following code**:
```csharp
var definitionIdByEvent = "DefinitionIdTest";
WorkflowDefinitionFilter filter = new WorkflowDefinitionFilter();
List ids = new List();
ids.Add(definitionIdByEvent);
var client = await _runtime.CreateClientAsync();
filter.DefinitionIds = ids;
var candidates = await _defs.FindManyAsync(filter);
```
3. The error occurs when the filter applies this query:
```csharp
if (DefinitionIds != null)
queryable = queryable.Where(x => DefinitionIds.Contains(x.DefinitionId));
```
**Reproduction Rate**: Every time when SQL Server compatibility_level < 160
## Expected Behavior
The query should execute successfully regardless of SQL Server compatibility level (as long as it's a supported version).
## Actual Behavior
Query fails with: `Microsoft.Data.SqlClient.SqlException: 'Incorrect syntax near '$'.'`
## Environment
- **Elsa Package Version**: [Your version]
- **EF Core Version**: 9.x
- **SQL Server Version**: [Your version] with compatibility_level < 160
- **Operating System**: [Your OS]
## Root Cause
EF Core 9 uses `OPENJSON` with `$` syntax for `Contains()` operations with `List`, which requires compatibility level 160+.
## Proposed Solution
Convert the list to `HashSet` before using in the query, which makes EF Core generate a different SQL pattern compatible with older compatibility levels:
```csharp
if (DefinitionIds != null)
queryable = queryable.Where(x => DefinitionIds.ToHashSet().Contains(x.DefinitionId));
```
Or convert at assignment:
```csharp
filter.DefinitionIds = ids.ToHashSet();
```
**I'm willing to contribute a PR to fix this issue if needed.**
## Additional Context
- This affects any SQL Server database with compatibility_level < 160 (pre-SQL Server 2022)
- The issue is specific to EF Core 9's query generation behavior
- Affects all filter operations using `Contains()` with collections
Contributor guide
Assessment
This issue has not been assessed yet.