πŸ›‘οΈ 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 with null.
  • 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

Column Options

Coalesce Options

Matched Options

Delete Matched Options


Last updated: 2025-08-17
Author: