πŸ›‘οΈ Coalesce Destination Option in Entity Framework ExtensionsUpdate only NULL values

The CoalesceDestination option in Entity Framework Extensions lets you update the destination value only when the current value in the database is null.

It’s equivalent to using ISNULL(DestinationTable.ColumnName, StagingTable.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 destination value is null
	options.CoalesceDestinationOnMergeUpdateExpression = 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 Destination With Coalesce Destination
1 (null) John Smith John Smith John Smith
2 Alice Brown Alice Brown Alice Brown Alice Brown
3 David Wilson David Miller David Miller David Wilson

πŸ› οΈ 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 CoalesceDestination option from Entity Framework Extensions when you want to fill in missing values in your database without replacing existing non-null values.

For example:

  • Importing data from an external system and only filling blanks in the database.
  • Updating customer profiles with optional data without overwriting already stored values.
  • Merging staging table data into the main table and preserving any non-null values already stored.

⭐ Why It’s Useful

Without CoalesceDestination, using in EF Core a bulk operations like BulkUpdate, BulkMerge, or BulkSynchronize from Entity Framework Extensions could overwrite non-null values with new values β€” even if the new data should only be applied when the destination is null.

Using this option ensures:

  • Existing data is preserved if already populated.
  • You avoid accidental overwriting of valid values.
  • Your updates remain intentional and clean, without extra 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, the requirement is that only null fields in the database should be updated β€” all non-null values must be preserved.

In summary:

  • When the destination Email IS NULL, the value is updated
  • When the destination Email IS NOT NULL, the value is kept

πŸ—οΈ Solution

The CoalesceDestination option offers three ways to handle this scenario:


🏷️ On[Action]UseCoalesceDestination

Use this option if you want all properties to apply the CoalesceDestination behavior by default.

// @nuget: Z.EntityFramework.Extensions.EFCore
using Z.EntityFramework.Extensions;

context.BulkMerge(customers, options => 
{
	// ON UPDATE: modify column values only if the destination value is null
	options.OnMergeUpdateUseCoalesceDestination = true;
});
Method Option Name Try it
BulkMerge OnMergeUpdateUseCoalesceDestination Online Example
BulkUpdate OnUpdateUseCoalesceDestination Online Example
BulkSynchronize OnSynchronizeUpdateUseCoalesceDestination Online Example

🏷️ CoalesceDestinationOn[Action]Expression

Use this option if you want to choose specific properties that should use the CoalesceDestination 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 destination value is null
	options.CoalesceDestinationOnMergeUpdateExpression = x => new { x.Name, x.Email };
});
Method Option Name Try it
BulkMerge CoalesceDestinationOnMergeUpdateExpression Online Example
BulkUpdate CoalesceDestinationOnUpdateExpression Online Example
BulkSynchronize CoalesceDestinationOnSynchronizeUpdateExpression Online Example

🏷️ CoalesceDestinationOn[Action]Names

Use this option if you prefer to explicitly list the property names that should use the CoalesceDestination 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 => 
{
	options.CoalesceDestinationOnMergeUpdateNames = new List<string>() 
	{
		nameof(Customer.Name),
		nameof(Customer.Email)
	};
});
Method Option Name Try it
BulkMerge CoalesceDestinationOnMergeUpdateNames Online Example
BulkUpdate CoalesceDestinationOnUpdateNames Online Example
BulkSynchronize CoalesceDestinationOnSynchronizeUpdateNames Online Example

🏁 Conclusion

Using CoalesceDestination option in Entity Framework Extensions with EF Core is a targeted safeguard that ensures only null fields in the database get updated, leaving non-null values untouched.

You can choose:

  • All properties β†’ On[Action]UseCoalesceDestination
  • Specific properties via expressions β†’ CoalesceDestinationOn[Action]Expression
  • Specific properties by name β†’ CoalesceDestinationOn[Action]Names

By using CoalesceDestination in your bulk operations, you can:

  • Preserve valuable data already stored
  • Fill in missing information without overwriting valid values
  • Maintain data consistency during imports and synchronizations

Column Options

Coalesce Options

Matched Options

Delete Matched Options


Last updated: 2025-08-17
Author: