如何基于EF Core生成带复杂谓词的SQL MERGE ON子句？

Great question! Building a custom Upsert extension that supports complex logical predicates (like your combination of AND/OR conditions) for MERGE statements is totally feasible, and we can leverage EF Core's built-in query translation capabilities to generate the ON clause SQL without reinventing the wheel.

Core Approach #

Instead of manually parsing the predicate expression, we can trick EF Core into translating it into a SQL WHERE clause, then repurpose that SQL as the ON clause for our MERGE statement. EF Core already has robust logic to translate LINQ expressions to database-specific SQL, so we can reuse that rather than building our own expression parser.

Step-by-Step Implementation #

1. Define the Upsert Builder Class #

First, create a fluent builder class to hold the necessary state (the entity to upsert, the predicate, columns to update, etc.):

public class UpsertBuilder<T> where T : class
{
    private readonly DbContext _dbContext;
    private readonly T _entity;
    private Expression<Func<T, T, bool>> _onPredicate;
    private Expression<Func<T, T>> _updateColumns;

    public UpsertBuilder(DbContext dbContext, T entity)
    {
        _dbContext = dbContext;
        _entity = entity;
    }

    public UpsertBuilder<T> On(Expression<Func<T, T, bool>> predicate)
    {
        _onPredicate = predicate;
        return this;
    }

    public UpsertBuilder<T> UpdateColumns(Expression<Func<T, T>> columns)
    {
        _updateColumns = columns;
        return this;
    }

    // We'll implement RunAsync next
    public async Task<int> RunAsync(CancellationToken cancellationToken = default)
    {
        // Logic to generate MERGE SQL and execute
    }
}

2. Add the Upsert Extension Method #

Add an extension method to DbContext to kick off the fluent chain:

public static class DbContextUpsertExtensions
{
    public static UpsertBuilder<T> Upsert<T>(this DbContext dbContext, T entity) where T : class
    {
        return new UpsertBuilder<T>(dbContext, entity);
    }
}

3. Translate Predicate to ON Clause SQL #

Inside the RunAsync method, we'll use EF Core to translate the predicate into a SQL fragment. The key trick is to replace the "compare" parameter in the predicate with the actual entity values, then generate a query with that predicate as a WHERE clause, and extract the SQL:

First, create a helper to replace expression parameters:

private class ParameterReplacer : ExpressionVisitor
{
    private readonly ParameterExpression _oldParam;
    private readonly Expression _newExpression;

    public ParameterReplacer(ParameterExpression oldParam, Expression newExpression)
    {
        _oldParam = oldParam;
        _newExpression = newExpression;
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        return node == _oldParam ? _newExpression : base.VisitParameter(node);
    }
}

Then, in RunAsync:

public async Task<int> RunAsync(CancellationToken cancellationToken = default)
{
    var originalParam = _onPredicate.Parameters[0];
    var compareParam = _onPredicate.Parameters[1];
    
    // Replace the "compare" parameter with the actual entity instance (as a constant)
    var entityConstant = Expression.Constant(_entity);
    var resolvedPredicateBody = new ParameterReplacer(compareParam, entityConstant).Visit(_onPredicate.Body);
    
    // Create a lambda that filters the original set using our resolved predicate
    var filterLambda = Expression.Lambda<Func<T, bool>>(resolvedPredicateBody, originalParam);
    
    // Generate a query with this filter, then get the SQL
    var query = _dbContext.Set<T>().Where(filterLambda);
    var querySql = query.ToQueryString();
    
    // Extract the WHERE clause content to use as our MERGE ON clause
    var onClauseSql = querySql.Substring(querySql.IndexOf("WHERE ") + 6).TrimEnd(';');

4. Build the MERGE Statement #

Next, we need to generate the rest of the MERGE SQL (UPDATE and INSERT parts). For simplicity, let's assume we want to update the columns specified in UpdateColumns, or insert all columns if no match is found:

// Get entity metadata from EF Core
var entityType = _dbContext.Model.FindEntityType(typeof(T));
var tableName = entityType.GetTableName();
var schema = entityType.GetSchema();
var fullTableName = string.IsNullOrEmpty(schema) ? tableName : $"{schema}.{tableName}";

// Generate UPDATE set clauses (simplified example)
var updateColumns = _updateColumns.Body is MemberInitExpression initExpr
    ? initExpr.Bindings.Select(b => ((MemberAssignment)b).Member.Name)
    : throw new InvalidOperationException("UpdateColumns must be a member init expression");

var updateSetClauses = string.Join(", ", updateColumns.Select(col => $"{col} = @p{updateColumns.ToList().IndexOf(col)}"));

// Generate INSERT columns and values (simplified example)
var allColumns = entityType.GetProperties().Select(p => p.Name);
var insertColumns = string.Join(", ", allColumns);
var insertValues = string.Join(", ", allColumns.Select((col, idx) => $"@p{idx + updateColumns.Count()}"));

// Combine into full MERGE SQL
var mergeSql = $@"
MERGE INTO {fullTableName} AS Target
USING (SELECT {insertValues}) AS Source ({insertColumns})
ON ({onClauseSql})
WHEN MATCHED THEN
    UPDATE SET {updateSetClauses}
WHEN NOT MATCHED THEN
    INSERT ({insertColumns})
    VALUES ({insertValues});";

// Extract parameters from the original query and combine with insert parameters
var parameters = query.Parameters.Concat(_dbContext.Entry(_entity).Properties.Select(p => new SqlParameter($"@p{query.Parameters.Count() + allColumns.ToList().IndexOf(p.Metadata.Name)}", p.CurrentValue)));

// Execute the MERGE statement
return await _dbContext.Database.ExecuteSqlRawAsync(mergeSql, parameters.ToArray(), cancellationToken);
}

Notes on EF Core's WHERE Clause Generation #

If you want to dig into how EF Core generates WHERE clauses under the hood, here's where to look in the source code:

• The core query translation logic lives in the Microsoft.EntityFrameworkCore.Query namespace.
• For relational databases, RelationalQueryCompilationContext coordinates the SQL generation.
• The actual SQL writing is handled by RelationalQuerySqlGenerator (and database-specific implementations like SqlServerQuerySqlGenerator). The VisitWhereClause method in these classes is responsible for generating the WHERE clause SQL from the expression tree.

This approach lets you reuse EF Core's battle-tested expression-to-SQL logic, so you don't have to handle edge cases like different database dialects, parameterization, or complex expression parsing yourself.

内容的提问来源于stack exchange，提问作者RHarris