Entity Framework Extensions Coalesce Destination

Description

The CoalesceDestination option lets you update the destination value if the value is null in the database. This option is the equivalent of doing an ISNULL(DestinationTable.ColumnName, StagingTable.ColumnName) in SQL Server.

Example

context.BulkMerge(customers, options => 
{
	// ON UPDATE, modify the "Name" and "Email" column value only if the destination value is not null
	options.CoalesceDestinationOnMergeUpdateExpression = x => new { x.Name, x.Email };
});

Scenario

A company uses Entity Framework and imports customers with the BulkMerge method to insert new customers and update existing customers.

However, there is a peculiarity. Only null values for customer columns need to be updated; otherwise, we keep the current database values.

In summary:

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

Solution

The CoalesceDestination option has 3 solutions to this problem:

On[Action]UseCoalesceDestination

Use this option if you want all properties to use this feature by default.

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

CoalesceDestinationOn[Action]Expression

Use this option if you prefer to specify with an expression which properties you want to include.

context.BulkMerge(customers, options => 
{
	// ON UPDATE, modify the "Name" and "Email" column value only if the destination value is not null
	options.CoalesceDestinationOnMergeUpdateExpression = x => new { x.Name, x.Email };
});
Method Name Try it
BulkMerge CoalesceDestinationOnMergeUpdateExpression Fiddle
BulkUpdate CoalesceDestinationOnUpdateExpression Fiddle
BulkSynchronize CoalesceDestinationOnSynchronizeUpdateExpression Fiddle

CoalesceDestinationOn[Action]Names

Use this option if you prefer to specify a list of property names you want to include. The value must correspond to the property name or the navigation name.

context.BulkMerge(customers, options => 
{				
	// ON UPDATE, modify the "Name" and "Email" column value only if the destination value is not null
	options.CoalesceDestinationOnMergeUpdateNames = new List<string>() { nameof(Customer.Name), nameof(Customer.Email) };
});
Method Name Try it
BulkMerge CoalesceDestinationOnMergeUpdateNames Fiddle
BulkUpdate CoalesceDestinationOnUpdateNames Fiddle
BulkSynchronize CoalesceDestinationOnSynchronizeUpdateNames Fiddle
Last updated: 2025-06-23
Author:


Contents