ef core upsert

Posted on by runwithliz

I was looking into how to handle upserts with ef core and came across this:

The Update method normally marks the entity for update, not insert. However, if the entity has a auto-generated key, and no key value has been set, then the entity is instead automatically marked for insert.
https://docs.microsoft.com/en-us/ef/core/saving/disconnected-entities#saving-single-entities

To be honest, I’m a little torn on how this works. It may not be clear to someone reviewing the code that this method is behaving as an upsert. It could also be that data coming to this method without a value for the key is a bug and the inserts are just creating more to cleanup. I am excited though that there is something, even though terribly named, of an upsert to use.

Let’s see how it works and the sql it creates. I’ve put together a really small sample database. It has 3 tables, Product, ProductReview and User. Each has an auto-generated key.

I also threw together a very simple application. This has a controller with two routes, one will return the product with reviews and the user that submitted the reviews. The other route will take Product and related info and perform an upsert.


You can grab a copy here: https://github.com/runwithliz/Upsert
I also included my postman queries: https://github.com/runwithliz/Upsert/blob/master/upsert.postman_collection.json

Below is a copy of the service that handles the upserts. Notice I don’t have any logic there trying to establish if the record exists or not. I simply call the update method.

using Upsert.Entities;
using Upsert.Interfaces;
using Microsoft.EntityFrameworkCore;
using System.Linq;

namespace Upsert.Services
{
    public class ProductService : IProductService
    {
        private readonly UpsertContext _dbcontext;
        public ProductService(UpsertContext dbcontext)
        {
            _dbcontext = dbcontext;
        }
        
        public Product GetProduct(int productId)
        {
           return _dbcontext.Product.Include(prod => prod.ProductReviews)
             .ThenInclude(review => review.User)
             .SingleOrDefault(s => s.ProductId == productId);
          
        }

        public Product SaveProduct(Product product)
        {
            // I'm a secret upsert!
            _dbcontext.Update(product);
            _dbcontext.SaveChanges();

            return product;
        }
    }
}

Per the documentation, I should just be able to pass a payload in and the records should get created as long as I’m not passing in any value for the keys. Here is my first test:

{ 
   "description":"Fancy Product",
   "productReviews":[ 
      { 
         "comment":"It is very fancy!",
         "user":{ 
            "userName":"Nice User"
         }
      }
   ]
}

Here is the SQL the application generated. Looks pretty good to me.

-- Inserting the product
INSERT INTO Product
 VALUES (@p0);
 SELECT [ProductId]
 FROM [Product]
 WHERE @@ROWCOUNT = 1 AND [ProductId] = scope_identity();

-- Inserting the User
INSERT INTO [User] ([UserName])
VALUES (@p0);
SELECT [UserId]
FROM [User]
WHERE @@ROWCOUNT = 1 AND [UserId] = scope_identity();

-- Inserting the Product Review
INSERT INTO [ProductReview] ([Comment], [ProductId], [UserId])
VALUES (@p1, @p2, @p3);
SELECT [ProductReviewId]
FROM [ProductReview]
WHERE @@ROWCOUNT = 1 AND [ProductReviewId] = scope_identity();

Now lets try to insert another review from the same user.

{
    "productId": 2,
    "description": "Fancy Product",
    "productReviews": [
        {
            "productId": 2,
            "comment": "Works well!",
            "userId": 3,
            "user": {
                "userId": 3,
                "userName": "Nice User"
            }
        }
    ]
}

This time the app is not only adding the additional review, but it is also updating the product description and username for the user. Since this is a disconnected entity, EF Core doesn’t know that we didn’t make changes to these values and thus preforms the updates assuming our intention was to update these values.

-- Updating the product description
UPDATE [Product] SET [Description] = @p0
WHERE [ProductId] = @p1;
SELECT @@ROWCOUNT;

-- Inserting the new review
INSERT INTO [ProductReview] ([Comment], [ProductId], [UserId])
VALUES (@p0, @p1, @p2);
SELECT [ProductReviewId]
FROM [ProductReview]
WHERE @@ROWCOUNT = 1 AND [ProductReviewId] = scope_identity();

-- Updating the username
UPDATE [User] SET [UserName] = @p0
WHERE [UserId] = @p1;

Lets see what happens if I cleanup the previous payload to attempt to prevent these superfluous updates.

{
     "productId": 2,
     "productReviews": [
         {
             "productId": 2,
             "comment": "No Updates?",
             "userId": 3
         }
     ]
 }

No luck, it actually ended up passing in an empty string to the description on the product. Yuck!

Executing DbCommand [Parameters=[@p1='2', @p0='' (Size = 25) (DbType = AnsiString)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Product] SET [Description] = @p0
WHERE [ProductId] = @p1;

This is not a problem with the update method though, but a problem with usage and how I’ve setup this sample application. In practice the product review would likely have separate routes for inserting/updating and any route that did allow for this multi-table interaction would need additional checks before a consumer passed in a request that performs an update you were not expecting.

Unknown's avatar

Published by runwithliz

1 thought on “ef core upsert”

Leave a comment