0

I have a composite key (sku (nvarchar/string value) and quantity (int value)) and I want to do an upsert in Microsoft.EntityFrameworkCore.SqlServer 5.0.8 using C#.

The AddOrUpdate call is missing from the context - not that we're supposed to be using that anyway, since it's primarily meant for migrations.

How do I accomplish this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ian Forsyth
  • 31
  • 1
  • 6

1 Answers1

0

Having a composite key means (as a compare to a single surrogate-key-primary-key)......it is still a "match and find or not find" proposition.

First, the setup.

https://learn.microsoft.com/en-us/ef/core/modeling/keys?tabs=data-annotations#configuring-a-primary-key

You can also configure multiple properties to be the key of an entity

  • this is known as a composite key. Composite keys can only be configured using the Fluent API; conventions will never set up a composite key, and you can not use Data Annotations to configure one.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Car>()
        .HasKey(c => new { c.State, c.LicensePlate });
}

Then you will Add items to your dbContext. (like adding object(s) to a collection)

for each CarEntity currentCar : myCarCollection)
{
      /* this code may not work for the future copy-pasters out there..i'm building up a little, see "WHERE IN" later in this answer */
        this.myEntityDbContext.MyCars.Add(currentCar);
}

Then you will call

await this.myEntityDbContext.SaveChangesAsync(token);

(or the sync version .SaveChanges)

EF ~~may be able (but probably not) to find existing-matches (and update existing) or insert new ones. And it depends on the state of your myDbContext at the moment of impact. (Again, i'm trying to build this up a little bit with talking about it, instead of just giving a copy/paste answer...you gotta try to understand how EF is working a little...to get better at debugging it)

with the M$ example.

if the database already has a

c.State = "VA", c.LicensePlate = "2222-2222"

but does not have

c.State = "WV", c.LicensePlate = "3333-3333"

Now, and you add both of these to your myDbContext (the "currentCar" code above) and then call "SaveChangesAsync"....that MIGHT work. But it will probably fail. That's ok.

Here is one "trick". (it may not be the only one)

You need to get the existing items (where VA/2222-2222 already exists) INTO the myDbContext.

So before you do the MyCars.add....

call the EF equivalent of (rdbms WHERE IN ) ( see Entity Framework Core - IN clause equivalent )

and the values you try check against the EF version of "WHERE IN".....will be both combinations of the "Car".

I just read one of the comments. Yes, that is very helpful. For clarity, this link: Update Row if it Exists Else Insert Logic with Entity Framework

That link is saying "how do i check for exist/not-exists IN THE DBCONTEXT) .. but for a SINGLE-BOOK. The where-exists stuff above is about getting the dbcontext loaded for several (cars,books,whatever).

Now, you can do some checking on whether to .Add (a Car) to the myDbContext, or "change some properties" of an existing. ***

But as the other link points out, you're gonna check for the values (the VA and the NC and the 2222-2222 and the 3333-3333), you gonna check the dbContext (items)

Let me explain that answer's "trick".

context.Entry(blog).State = blog.BlogId == 0 ? 
                               EntityState.Added : 
                               EntityState.Modified;

That is an old-"fairly standard" trick of using 0 as the "do i already exist or not". with the "Car" from above, you don't have a single-surrogate-usually-primarykey thing like "BlogId". with the car, you have 2 parts. so you'll have to check the myDbContext using the n-parts-of-the-compound-key to determine this. it'll take a little experimentation to tweak your code.

You will have to be aware if your context is long-lived (like a winforms desktop app) or your context is short-lived (like a rest-api).

But that is a much deeper question. Here is a hint.

https://learn.microsoft.com/en-us/ef/core/querying/tracking

Then you gotta consider how hard your system is getting hit. This could be a race-condition in a high hit system.

granadaCoder
  • 26,328
  • 10
  • 113
  • 146
  • Thank you for your very detailed explanation. I have one question about "the trick." When you're evaluating the state and you test whether the blog id is 0, isn't that making the presumption that you know you're going to do an add vs. an update? In this case, I don't know whether I'm going to do an add or an update. It depends on whether the keys exist in a record in the database. So does this operation require a prior lookup of the key values in the database to determine whether you'll set the state to add vs. update or vice versa? – Ian Forsyth Aug 01 '21 at 08:36
  • The "trick" is .. when I know .. it is going to be a "new", I set the PK-SurrogateKey to Zero. (That it outside of what you are asking). You (your code rather). Upserts are tricky with any ORM. You have to flush out your exact needs a little. – granadaCoder Aug 01 '21 at 14:41
  • this adds to the discussion : https://stackoverflow.com/questions/5557829/update-row-if-it-exists-else-insert-logic-with-entity-framework/5558777#5558777. and what I write above is kinda what the other person says under "If you can't decide existance of the object by its Id you must execute lookup query:" – granadaCoder Aug 03 '21 at 20:22