如何在其他应用操作表时将增删改条目同步到EF DbContext？

嘿，我来给你分享几种纯代码实现的方案，不用触发器也能让你的控制台应用同步SQL Server表的增删改操作到自己的DbContext里~

SQL Server自带的变更跟踪功能可以帮你记录表中数据的增删改操作，咱们可以在控制台应用里定期查询这些变更记录，然后同步到DbContext中。

步骤： #

1. 先给目标表开启变更跟踪：

ALTER DATABASE YourDatabaseName
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

ALTER TABLE YourTableName
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

2. 在.NET控制台应用里，定期查询变更记录：

using var context = new YourDbContext();
var lastSyncVersion = context.Database.SqlQuery<long>("SELECT CHANGE_TRACKING_CURRENT_VERSION()").First();

// 查询自上次同步以来的变更
var changes = context.Database.SqlQuery<ChangeRecord>(@"
    SELECT 
        CT.SYS_CHANGE_OPERATION AS Operation,
        T.Id,
        T.Column1,
        T.Column2
    FROM CHANGETABLE(CHANGES YourTableName, @LastVersion) CT
    JOIN YourTableName T ON CT.Id = T.Id",
    new SqlParameter("@LastVersion", lastSyncVersion)).ToList();

// 同步到DbContext
foreach (var change in changes)
{
    var entity = await context.YourEntities.FindAsync(change.Id);
    switch (change.Operation)
    {
        case "I": // 插入
            if (entity == null)
            {
                context.YourEntities.Add(new YourEntity { Id = change.Id, Column1 = change.Column1, Column2 = change.Column2 });
            }
            break;
        case "U": // 更新
            if (entity != null)
            {
                entity.Column1 = change.Column1;
                entity.Column2 = change.Column2;
                context.Entry(entity).State = EntityState.Modified;
            }
            break;
        case "D": // 删除
            if (entity != null)
            {
                context.YourEntities.Remove(entity);
            }
            break;
    }
}

await context.SaveChangesAsync();
// 更新上次同步版本
lastSyncVersion = context.Database.SqlQuery<long>("SELECT CHANGE_TRACKING_CURRENT_VERSION()").First();

你可以把这段查询逻辑放到定时任务里（比如用Timer或者Hangfire），定期执行同步。

如果想要实时同步，不想用轮询，那Service Broker是个不错的选择。它可以在表发生增删改时主动推送消息给你的控制台应用，咱们在应用里监听消息然后更新DbContext。

步骤： #

1. 先创建Service Broker相关的对象（队列、服务、触发器）：

-- 开启Service Broker
ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

-- 创建消息类型
CREATE MESSAGE TYPE [EntityChangeMessage] VALIDATION = WELL_FORMED_XML;

-- 创建契约
CREATE CONTRACT [EntityChangeContract] ([EntityChangeMessage] SENT BY INITIATOR);

-- 创建队列
CREATE QUEUE EntityChangeQueue;

-- 创建服务
CREATE SERVICE EntityChangeService ON QUEUE EntityChangeQueue ([EntityChangeContract]);

-- 创建触发器，在表变更时发送消息
CREATE TRIGGER Trigger_YourTableName_Change ON YourTableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @Message XML;
    DECLARE @Operation NVARCHAR(1);

    IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
        SET @Operation = 'U';
    ELSE IF EXISTS(SELECT * FROM INSERTED)
        SET @Operation = 'I';
    ELSE IF EXISTS(SELECT * FROM DELETED)
        SET @Operation = 'D';

    SET @Message = (
        SELECT @Operation AS Operation, * FROM INSERTED UNION ALL
        SELECT @Operation AS Operation, * FROM DELETED
        FOR XML PATH('EntityChange'), ROOT('Changes')
    );

    DECLARE @ConversationHandle UNIQUEIDENTIFIER;
    BEGIN DIALOG CONVERSATION @ConversationHandle
        FROM SERVICE EntityChangeService
        TO SERVICE 'EntityChangeService'
        ON CONTRACT EntityChangeContract
        WITH ENCRYPTION = OFF;

    SEND ON CONVERSATION @ConversationHandle
        MESSAGE TYPE EntityChangeMessage (@Message);

    END CONVERSATION @ConversationHandle;
END;

2. 在控制台应用里监听队列消息：

using var connection = new SqlConnection("YourConnectionString");
connection.Open();

// 启动监听
var command = new SqlCommand(@"
    WAITFOR (RECEIVE TOP(1) * FROM EntityChangeQueue);", connection);

while (true)
{
    using var reader = command.ExecuteReader();
    if (reader.Read())
    {
        var messageBody = reader["message_body"] as byte[];
        var messageXml = Encoding.UTF8.GetString(messageBody);
        // 解析XML获取变更信息
        var xmlDoc = new XmlDocument();
        xmlDoc.LoadXml(messageXml);
        var operation = xmlDoc.SelectSingleNode("/Changes/EntityChange/Operation").InnerText;
        var entityId = int.Parse(xmlDoc.SelectSingleNode("/Changes/EntityChange/Id").InnerText);

        // 同步到DbContext
        using var context = new YourDbContext();
        var entity = await context.YourEntities.FindAsync(entityId);
        switch (operation)
        {
            case "I":
                // 重新查询最新数据
                var newEntity = await context.YourEntities.FindAsync(entityId);
                if (newEntity != null && context.Entry(newEntity).State == EntityState.Detached)
                {
                    context.YourEntities.Add(newEntity);
                }
                break;
            case "U":
                if (entity != null)
                {
                    // 重新加载实体获取最新数据
                    context.Entry(entity).Reload();
                }
                break;
            case "D":
                if (entity != null)
                {
                    context.YourEntities.Remove(entity);
                }
                break;
        }
        await context.SaveChangesAsync();
    }
}

这个方案是实时的，表一有变更就会触发消息推送，适合对实时性要求高的场景。

如果操作数据库的其他应用也是.NET技术栈，那咱们可以直接在那些应用里发布变更事件，然后你的控制台应用订阅这些事件来同步DbContext。比如用内存事件总线或者轻量消息队列。

举个简单的例子：

// 定义事件类
public class EntityChangedEvent
{
    public string Operation { get; set; }
    public int EntityId { get; set; }
    // 其他字段...
}

// 其他应用里的静态事件总线
public static class EventBus
{
    public static event Action<EntityChangedEvent> EntityChanged;

    public static void Publish(EntityChangedEvent ev)
    {
        EntityChanged?.Invoke(ev);
    }
}

// 其他应用操作数据库后发布事件
public async Task InsertEntity(YourEntity entity)
{
    using var context = new OtherDbContext();
    context.YourEntities.Add(entity);
    await context.SaveChangesAsync();
    EventBus.Publish(new EntityChangedEvent { Operation = "I", EntityId = entity.Id });
}

// 你的控制台应用订阅事件
EventBus.EntityChanged += async ev =>
{
    using var context = new YourDbContext();
    switch (ev.Operation)
    {
        case "I":
            var newEntity = await context.YourEntities.FindAsync(ev.EntityId);
            if (newEntity != null && context.Entry(newEntity).State == EntityState.Detached)
            {
                context.YourEntities.Add(newEntity);
            }
            break;
        case "U":
            var entityToUpdate = await context.YourEntities.FindAsync(ev.EntityId);
            if (entityToUpdate != null)
            {
                context.Entry(entityToUpdate).Reload();
            }
            break;
        case "D":
            var entityToDelete = await context.YourEntities.FindAsync(ev.EntityId);
            if (entityToDelete != null)
            {
                context.YourEntities.Remove(entityToDelete);
            }
            break;
    }
    await context.SaveChangesAsync();
};

这个方案最轻量化，但只适用于同技术栈的应用之间，而且如果是分布式场景，可能需要换成RabbitMQ、Kafka这类分布式消息队列来传递事件。

一些注意事项： #

• 处理并发冲突：比如多个操作同时修改同一条数据，建议给实体加Timestamp字段用乐观锁。
• DbContext生命周期：每次处理变更时最好创建新的DbContext实例，避免上下文状态混乱。
• 异常处理：同步过程中要捕获异常，避免一次失败导致整个监听逻辑崩溃。

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