EF Core 5中实现高效实体AddOrUpdate（避免Find查询、指定修改属性）的方案咨询

Hey there! I’ve tackled this exact problem before, and there’s a solution that checks all your boxes: eliminating the costly Find call, letting the database handle the insert/update decision, controlling which properties get modified, retaining DbContext change tracking, and avoiding the old AddOrUpdate method’s pitfalls. Here’s how to make it work:

Core Idea #

Instead of querying the database upfront to check if the entity exists, we’ll attach the entity directly to the DbContext, manually mark only the properties we want to update, and leverage EF’s state management plus database primary key constraints to handle the upsert. This way, we skip the expensive Find call entirely.

Step-by-Step Implementation #

1. Build Your Entity Object #

Only populate the primary key (since it’s non-auto-generated, you must set this) and the properties you want to insert/update. Leave other properties unset—they’ll either keep their existing database values (for updates) or use default values (for inserts).

var targetEntity = new YourEntity
{
    Id = "your-existing-or-new-primary-key", // Mandatory for non-auto-generated PK
    UpdatedProperty1 = "new-value-1",
    UpdatedProperty2 = DateTime.UtcNow
    // Leave other properties as default—they won't overwrite database values
};

2. Attach the Entity & Mark Modified Properties #

Attach the entity to the DbContext in Unchanged state, then explicitly mark which properties should be updated. This tells EF exactly what to modify when SaveChanges runs.

var entry = Context.Entry(targetEntity);

if (entry.State == EntityState.Detached)
{
    Context.Attach(targetEntity);
    // Mark only the properties you want to update
    entry.Property(e => e.UpdatedProperty1).IsModified = true;
    entry.Property(e => e.UpdatedProperty2).IsModified = true;
}

3. Handle Insert vs Update via Database Feedback #

When you call SaveChanges():

• If the entity exists in the database: EF generates an UPDATE statement only for the marked properties—no overwriting of unmodified fields.
• If the entity doesn’t exist: Since we attached it as Unchanged, EF won’t do anything. We can check the number of affected rows to detect this and switch to an insert:

var affectedRows = Context.SaveChanges();

if (affectedRows == 0)
{
    // No rows updated = entity doesn't exist—switch to insert state
    entry.State = EntityState.Added;
    Context.SaveChanges();
}

4. Batch Multiple Upsert Calls #

Since we’re using DbContext’s change tracking, you can repeat steps 1-2 for as many entities as you want, then call SaveChanges() once at the end. EF will batch all the operations, which is perfect for merging multiple upsert requests.

Why This Works For You #

• No More Find Calls: We skip the expensive database query that was eating up CPU.
• Controlled Updates: Only properties marked as IsModified are touched—no accidental overwrites of uninitialized properties (unlike the old AddOrUpdate).
• Change Tracking Intact: All operations use EF’s built-in change tracking, so you get transaction support and batch processing out of the box.
• Database-Level Upsert Safety: The primary key constraint ensures atomicity—no duplicate inserts or race conditions.

Optional Optimization #

If you know whether most of your operations are inserts or updates, you can tweak the logic to be even faster:

• For update-heavy workloads: Stick with the above flow (attach as Unchanged first).
• For insert-heavy workloads: Start by setting the entity state to Added, catch the primary key conflict exception, then switch to Unchanged and mark modified properties before retrying.

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