Entity Framework Extensions Coalesce

Description

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

Example

context.BulkMerge(customers, options => 
{
	// ON UPDATE, modify the "Name" and "Email" column value only if the source value is not null 
	options.CoalesceOnMergeUpdateExpression = 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. The customer data to import doesn't contain all values (some are null). For some 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 has 3 solutions to this problem:

On[Action]UseCoalesce

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 source value is not null 
	options.OnMergeUpdateUseCoalesce = true;
});
Method Name Try it
BulkMerge OnMergeUpdateUseCoalesce Fiddle
BulkUpdate OnUpdateUseCoalesce Fiddle
BulkSynchronize OnSynchronizeUpdateUseCoalesce Fiddle

CoalesceOn[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 source value is not null 
	options.CoalesceOnMergeUpdateExpression = x => new { x.Name, x.Email };
});
Method Name Try it
BulkMerge CoalesceOnMergeUpdateExpression Fiddle
BulkUpdate CoalesceOnUpdateExpression Fiddle
BulkSynchronize CoalesceOnSynchronizeUpdateExpression Fiddle

CoalesceOn[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 source value is not null
	options.CoalesceOnMergeUpdateNames = new List<string>() { nameof(Customer.Name), nameof(Customer.Email) };
});
Method Name Try it
BulkMerge CoalesceOnMergeUpdateNames Fiddle
BulkUpdate CoalesceOnUpdateNames Fiddle
BulkSynchronize CoalesceOnSynchronizeUpdateNames Fiddle
Last updated: 2025-06-23
Author:


Contents