Entity Framework Extensions Delete Matched and Formula

Description

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

Example

context.BulkDelete(customers, options => 
{
	// REQUIRED because by default, only the "CustomerID" is part of the "StagingTable"
	options.ColumnStagingTableFormulaExpression = x => x.Version;
	
	// ON DELETE, remove customer where the version is equal or lower than the one coming from the importation
	options.DeleteMatchedAndFormula = "StagingTable.Version > DestinationTable.Version";
});

Scenario

A company uses Entity Framework and needs to delete customers with the BulkDelete method.

However, there is a particularity. The delete should only happen if the version in the database is equal or lower as the one coming from the importation.

In summary:

  • If the destination Version value is equal or lower, the customer can be deleted
  • If the destination Version value is higher, the customer cannot be deleted

Solution

TheDeleteMatchedAndFormula option have 1 solutions to this problem:

DeleteMatchedAndFormula

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

context.BulkDelete(customers, options => 
{
	// REQUIRED because by default, only the "CustomerID" is part of the "StagingTable"
	options.ColumnStagingTableFormulaExpression = x => x.Version;
	
	// ON DELETE, remove customer where the version is equal or lower than the one coming from the importation
	options.DeleteMatchedAndFormula = "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
BulkDelete DeleteMatchedAndFormula Fiddle
Last updated: 2023-02-28
Author:


Contents