Entity Framework Extensions Coalesce

Description

The Coalesce option lets you keep the destination value if the source value is null. This option is equivalent of doing a 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 particularity. 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 keep
  • When the source Email IS NOT NULL, the destination value is updated

Solution

TheCoalesce option have 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

Contents

Related