search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.79k stars 3.19k forks source link

Achieving database null semantics with string concatenation #33616

Open slepmog opened 6 months ago

slepmog commented 6 months ago

Some time ago (issue #3836) it was decided that the null semantic of string concatenation is wrong and should be replaced with C# semantic. From then on, when EF detects a string concatenation in your LINQ, it translates each concatenated argument into SQL by wrapping it into COALESCE:

... = t.Field1 + t.Field2 + t.Field3, ...

becomes

... COALESCE([t].[Field1], N'') + COALESCE([t].[Field2], N'') + COALESCE([t].[Field3], N''), ...

This behaviour may be surprisingly annoying and disruptive, resulting in having to write very convoluted code with superfluous null checks that is then translated into an SQL monstrosity that runs slow because it has to evaluate the arguments twice (once for the null comparison, then for the concatenation if the comparison holds). All where the raw SQL version would neatly eliminate the unwanted nulls automatically.

I am aware that .UseRelationalNulls() exists, and I use it, but it only fixes the nullable comparison semantic (i.e. stops generating WHERE (a = b) OR (a IS NULL AND b IS NULL) and starts generating WHERE a = b like it should be). It does not fix the null concatenation semantic.

EF Core version: 8.0.4 Database provider: Microsoft.EntityFrameworkCore.SqlServer 8.0.4 Target framework: .NET 8.0 Operating system: Windows 10 IDE: Visual Studio 2022 17.9.6

maumar commented 5 months ago

Changing UseRelationalNulls behavior for string concat would be breaking and may not be a change that all users want. We should consider another switch