elsa-workflows / elsa-workflows/elsa-core

SqlException: Incorrect syntax near '$' when using WorkflowDefinitionFilter.DefinitionIds with EF Core 9 and SQL Server compatibility_level < 160

Open
#6,957 0 comments 0 reactions 0 assignees View on GitHub
triaged
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

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.