EF Core upsert operation in C# application

Question

I want to develop an application where if data exists based on multiple database columns, then EF Core should perform an update operation or else an insert operation.

Table Structure: TableName - Engagement Id, OrgId, PersonId, OrgName, ClubName, .. and many other fields.

I want to write a code where in if OrgId and PersonId [OrgId and PersonId are not the primary keys of the table] Exist in Table Engagement then EF Core should update the record or else It should Insert a new record in the table.

I couldn't find any AddorUpdate Method in EFCore.

What is stopping you doing that? Stackoverflow is a question and answer site, so you ideally need to be asking a question so that someone can provide an answer. Simply providing a design brief (although it is a bit of a stretch to call your single sentence a design brief) and hoping someone will write your code for you is unlikely to illicit a positive response. What have you tried so far? What problems are you having? What is your data structure and which fields identify an existing record? — GarethD, Nov 18 '22 at 10:07
There is an extension method [`AddOrUpdate`](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.migrations.idbsetextensions.addorupdate?view=entity-framework-5.0.0), which may be of use. It is hard to say with the limited information provided. — GarethD, Nov 18 '22 at 10:12
@GarethD : The method is part of migration. Not what this question is about. OP want to do it for data. Migration is used to update database when class properties are added or deleted in c#. — jdweng, Nov 18 '22 at 10:16
You need to get return value from ExecuteNonQuery (https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.core.entityclient.entitycommand.executenonquery?view=entity-framework-6.2.0) The return value indicates number of rows changed in database. When doing an Insert you will get zero if primary key already exists and then you need to do update. When doing an Update if primary key does not exist you will get zero and then need to do an Insert. — jdweng, Nov 18 '22 at 10:19
@jdweng I have no idea how you can tell what this question is about based on the little information provided, but just because that extension method is in the Migrations namespace doesn't mean it is about changing properties. The `AddOrUpdate` method will do exactly what it says on the tin, if you add (or update) an entity with this method, when `SaveChanges()` is called it will update the entity if it exists, or insert if it doesn't - which is an upsert operation in EF Core, so seems to be exactly what the question is about. — GarethD, Nov 18 '22 at 10:33
The documentation says *"Adds or updates entities by key when SaveChanges is called. Equivalent to an **"upsert" operation** from database terminology."*. (emphasis mine) It does go on to say that this method is *useful* in migrations (hence why it exists), but it is certainly not exclusive to migrations — GarethD, Nov 18 '22 at 10:36
@Gareth it's in the System.Data.Entity.Migrations namespace, it's not for EF Core, it's not meant for application code. — CodeCaster, Nov 18 '22 at 12:19
Sorry, forgot that `AddOrUpdate` was removed for EF Core, you can simply use `Update()` this works the same way as `AddOrUpdate()` did as noted [in this answer](https://stackoverflow.com/a/62449352/1048425), although with the additional information now in the question this is not relevant anyway since the columns for identifying an existing record are not the primary key and this only works with autogenerated IDs. This article should be useful - [Disconnected entities](https://learn.microsoft.com/en-us/ef/core/saving/disconnected-entities) — GarethD, Nov 18 '22 at 12:47

Fitri Halim · Answer 1 · 2022-11-19T01:22:03.987

You can use a nuget package from Flexlab https://github.com/artiomchi/FlexLabs.Upsert to accomplish this.

var EngagementEntity = new Engagement 
{
    EngagementId = "engid",
    OrgId = "orgid",
    PersonName = "personName",
    PersonId = "personId",
    OrgName = "Orgname",
};


await DataContext.Upsert(EngagementEntity)
    .On(c => new Engagement  //Specify which column to check for matches
     {
        c.OrgId , 
        c.PersonId 
     })
    .WhenMatched(c => new Engagement // Specify which columns to update 
    {
        OrgName = "Orgname",
        PersonName = "personName",
    })
    .RunAsync();

The best thing about this is it is implemented on database site and it is very fast.

EF Core upsert operation in C# application

1 Answers1