facebook

Entity Framework Extensions Coalesce Destination

Description

The CoalesceDestination option let you to update the destination value only 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 => 
{
	// USE the code as the key expression
	options.ColumnPrimaryKeyExpression = x => x.Code;
	
	// ON UPDATE, modifiy the "Name" and "Email" column only if the destination value is null 
	options.CoalesceDestinationOnMergeUpdateExpression = x => new { x.Name, x.Email };
});

Scenario

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

However, there is a particularity. The customer should keep the current Email value in the database if already specified.

  • When the destination Email IS NULL, the destination value should be updated
  • When the destination Email IS NOT NULL, the destination value should be 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 => 
{
	// USE the code as the key expression
	options.ColumnPrimaryKeyExpression = x => x.Code;
	
	// ON UPDATE, modifiy the "Name" and "Email" column only if the destination value is null 
	options.CoalesceDestinationOnMergeUpdateExpression = x => new { x.Name, x.Email };
});
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 => 
{
	// USE the code as the key expression
	options.ColumnPrimaryKeyExpression = x => x.Code;
	
	// ON UPDATE, modifiy the "Name" and "Email" column only if the destination value is 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 properties names you want to include. The value must correspond to the property name or the navigation name.

context.BulkMerge(customers, options => 
{
	// USE the code as the key expression
	options.ColumnPrimaryKeyExpression = x => x.Code;
	
	// ON UPDATE, modifiy the "Name" and "Email" column only if the destination value is 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

Contents

Related