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 equivalent of doing a 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 particularity. 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 keep

Solution

TheCoalesceDestination option have 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: 2023-02-28
Author:


Contents