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 |
Related Solutions
ZZZ Projects