π‘οΈ Coalesce Option in Entity Framework ExtensionsIgnore NULL values during updates
The Coalesce
option in Entity Framework Extensions lets you keep the destination value when the source value is null
.
Itβs equivalent to using ISNULL(StagingTable.ColumnName, DestinationTable.ColumnName)
in SQL Server.
// @nuget: Z.EntityFramework.Extensions.EFCore using Z.EntityFramework.Extensions; context.BulkMerge(customers, options => { // ON UPDATE: change "Name" and "Email" only if the source value is not null options.CoalesceOnMergeUpdateExpression = x => new { x.Name, x.Email }; });
This option applies to the following methods in Entity Framework Extensions:
π‘ Example Effect
ID | Destination.Name | Source.Name | Without Coalesce | With Coalesce |
---|---|---|---|---|
1 | John Smith | (null) | (null) | John Smith |
2 | Alice Brown | Alice Brown | Alice Brown | Alice Brown |
3 | David Wilson | David Miller | David Miller | David Miller |
π οΈ Prerequisites
Before continuing, we recommend reading these articles first to understand how EF Extensions options work and the differences between column option types:
- Configure Options β Learn the basics of setting and customizing options in EF Extensions.
- Configure Column Options β See how to configure column-specific behavior and when to use
Expression
(strongly typed, compile-time safe) vs.Names
(string-based, dynamic at runtime).
π When to Use
Use the Coalesce
option from Entity Framework Extensions when importing or synchronizing data that may have missing values.
For example:
- Importing data from an external system that doesnβt always send all fields.
- Updating customer records while preserving existing values if the new data is incomplete.
- Merging staging table data into the main table without overwriting valid values with
null
.
β Why Itβs Useful
Without Coalesce
, using in EF Core a bulk operations like BulkUpdate, BulkMerge, or BulkSynchronize from Entity Framework Extensions could overwrite existing data with null values from the source.
Using this option ensures:
- Important data is preserved when the source doesnβt provide a value.
- Fewer accidental data losses when doing large merges.
- Cleaner data handling logic, without writing custom SQL.
π’ Scenario
A company uses EF Core and imports customers with the BulkMerge method from Entity Framework Extensions to insert new customers and update existing ones.
However, thereβs a peculiarity: the customer data to import doesnβt always contain all values β some fields can be null
. For certain columns, we want to keep the destination value if the source value is null
.
In summary:
- When the source
Email IS NULL
, the destination value is kept - When the source
Email IS NOT NULL
, the destination value is updated
ποΈ Solution
The Coalesce
option offers three ways to handle this scenario, depending on how broadly or precisely you want to apply the behavior:
- On[Action]UseCoalesce β Apply
Coalesce
to all properties during the operation. Quick to set up and ideal when you want maximum protection against overwriting withnull
. - CoalesceOn[Action]Expression β Apply
Coalesce
only to specific properties, defined via a strongly typed lambda expression. Great for compile-time safety and easy refactoring. - CoalesceOn[Action]Names β Apply
Coalesce
only to specific properties by name (string list). Perfect when the list of properties is dynamic or comes from configuration.
π·οΈ On[Action]UseCoalesce
Use this option if you want all properties to apply the Coalesce
behavior by default.
// @nuget: Z.EntityFramework.Extensions.EFCore using Z.EntityFramework.Extensions; context.BulkMerge(customers, options => { // ON UPDATE: modify column values only if the source value is not null options.OnMergeUpdateUseCoalesce = true; });
Method | Option Name | Try it |
---|---|---|
BulkMerge | OnMergeUpdateUseCoalesce | Online Example |
BulkUpdate | OnUpdateUseCoalesce | Online Example |
BulkSynchronize | OnSynchronizeUpdateUseCoalesce | Online Example |
π·οΈ CoalesceOn[Action]Expression
Use this option if you want to choose specific properties that should use the Coalesce
behavior, defined via a lambda expression.
// @nuget: Z.EntityFramework.Extensions.EFCore using Z.EntityFramework.Extensions; context.BulkMerge(customers, options => { // ON UPDATE: change "Name" and "Email" only if the source value is not null options.CoalesceOnMergeUpdateExpression = x => new { x.Name, x.Email }; });
Method | Option Name | Try it |
---|---|---|
BulkMerge | CoalesceOnMergeUpdateExpression | Online Example |
BulkUpdate | CoalesceOnUpdateExpression | Online Example |
BulkSynchronize | CoalesceOnSynchronizeUpdateExpression | Online Example |
π·οΈ CoalesceOn[Action]Names
Use this option if you prefer to explicitly list the property names that should use the Coalesce
behavior.
The value must match either the property name or a navigation property name.
// @nuget: Z.EntityFramework.Extensions.EFCore using Z.EntityFramework.Extensions; context.BulkMerge(customers, options => { // ON UPDATE: change "Name" and "Email" only if the source value is not null options.CoalesceOnMergeUpdateNames = new List<string>() { nameof(Customer.Name), nameof(Customer.Email) }; });
Method | Option Name | Try it |
---|---|---|
BulkMerge | CoalesceOnMergeUpdateNames | Online Example |
BulkUpdate | CoalesceOnUpdateNames | Online Example |
BulkSynchronize | CoalesceOnSynchronizeUpdateNames | Online Example |
π Conclusion
Using Coalesce
option in Entity Framework Extensions with EF Core is a safeguard that ensures null values from the source do not overwrite existing values in the database.
You can choose:
- All properties β
On[Action]UseCoalesce
- Specific properties via expressions β
CoalesceOn[Action]Expression
- Specific properties by name β
CoalesceOn[Action]Names
By using Coalesce
in your bulk operations, you can:
- Protect valuable data when the source is incomplete
- Simplify your update logic without writing custom SQL
- Keep imports and synchronizations safer and more predictable
π Related Articles
Column Options
Coalesce Options
Matched Options
Delete Matched Options
ZZZ Projects