Entity Framework Extensions Matched and Formula

Description

The MatchedAndFormula option lets you perform or skip the update action, depending on the hardcoded SQL specified.

Example

context.BulkMerge(customers, options => 
{
	// ON UPDATE, modify customers where the version is equal or lower than the one coming from the importation
	// StagingTable = source
	// DestinationTable = database/destination
	options.MergeMatchedAndFormula = "StagingTable.Version > DestinationTable.Version";
});

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 only be updated if the Version value is higher in the source than in the database. Otherwise, we consider the database value having the latest customer information.

In summary:

  • If the source Version value is higher, the customer can be updated
  • If the source Version value is equal or lower, the customer cannot be updated

Solution

TheMatchedAndFormula have 1 solution to this problem:

[Action]MatchedAndFormula

Use this option to hardcode an SQL that returns a boolean. If the predicate is true, the update action will be performed.

context.BulkMerge(customers, options => 
{
	// ON UPDATE, modify customers where the version is equal or lower than the one coming from the importation
	// StagingTable = source
	// DestinationTable = database/destination
	options.MergeMatchedAndFormula = "StagingTable.Version > DestinationTable.Version";
});

Table Alias:

  • DestinationTable: Alias for the table in the database
  • StagingTable: Alias for the table containing value from the source
Method Name Try it
BulkMerge MergeMatchedAndFormula Fiddle
BulkUpdate UpdateMatchedAndFormula Fiddle
BulkSynchronize SynchronizeMatchedAndFormula Fiddle
Last updated: 2023-02-28
Author:


Contents