facebook

Entity Framework Extensions Coalesce

Description

The Coalesce option let 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 => 
{
	// USE the code as the key expression
	options.ColumnPrimaryKeyExpression = x => x.Code;
	
	// ON UPDATE, modifiy the "Name" and "Email" column only if the source value is not null 
	options.CoalesceOnMergeUpdateExpression = 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 data to import don't contains all values (some are null). The destination value should be keep if the source value is null.

  • When the source Email IS NULL, the destination value should be keep
  • When the source Email IS NOT NULL, the destination value should be 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 => 
{
	// 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 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 => 
{
	// USE the code as the key expression
	options.ColumnPrimaryKeyExpression = x => x.Code;
	
	// ON UPDATE, modifiy the "Name" and "Email" column 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 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 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