Query: allow providers to easily override null compensation and provide simpler translations instead
Nobody has claimed this yet.
Assessment
- Difficulty
- 5/5
- Estimated time
- Over a week
- Newbie friendliness
- 20/100
- Issue type
- Feature
- Clarity
- Needs clarification
- Activity status
- Stale
- Domain
- backend-api-design, databases
Research direction
Start by tracing EF Core's query translation and null-compensation logic, then compare how provider-specific translations could be selected. The issue provides no file, test, or entry point, so first identify the relevant query translation components and existing provider overrides. Done means providers can choose simpler or alternative null-equality translations without breaking LINQ semantics.
Written by the indexing model from the issue text.
Description
We have pretty heavy logic in query that allows translating two-value logic expressions into SQL, which does three-value logic natively. Many popular relational databases besides SQL Server implement alternative patterns to perform equality comparisons with in-memory semantics, e.g. x == y in a LINQ expression could be translated to
| Translation | Supported by |
|---|---|
x is y |
SQLite |
x <=> y |
MySQL and MariaDB |
decode(x, y, 0, 1) = 0 |
DB2 and Oracle |
EXISTS (SELECT x INTERSECT SELECT y) |
PostgreSQL, SQLite, SQL Server (as search condition) |
EXISTS (VALUES(x) INTERSECT VALUES(y)) |
DB2, PostgreSQL, SQL Server |
x IS NOT DISTINCT FROM y |
ANSI SQL:2003, PostgreSQL (apparently not sargable) |
This article presents an excellent survey of all the alternatives: http://modern-sql.com/feature/is-distinct-from.
For cases in which we know if either x or y are non-nullable, we can probably produce a more concise translation, but in more complex cases, the options above could lead to better SQL, even for SQL Server.
In any case, it should be possible for a provider to pick the best translation.
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- Avg merge
- 2d 5h
- Merged PRs (30d)
- 134
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from dotnet/efcore
-
Difficulty 4/5 3-5 days Newbie friendliness 55/100
-
customer-reported
Difficulty 5/5 Over a week Newbie friendliness 38/100
-
area-cosmos area-vector-search
Difficulty 5/5 Over a week Newbie friendliness 25/100
-
area-cosmos
Difficulty 5/5 Over a week Newbie friendliness 25/100
-
area-tools needs-design
Difficulty 4/5 3-5 days Newbie friendliness 25/100
Similar issues
-
Difficulty 2/5 1-3 hours Newbie friendliness 86/100
-
:watch: Not Triaged 11.0 fundamentals/subsvc
Difficulty 2/5 1-3 hours Newbie friendliness 92/100
dotnet/AspNetCore.Docs#37699 ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
SubtitleEdit/subtitleedit#15108 · 1 comment ·
-
area/docs-content Bug pulumi/docs
Difficulty 1/5 1-3 hours Newbie friendliness 94/100
-
agentic-workflows untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 76/100