0

I have many scenarios when I need to update data of an existing record by criteria or create a new record with this data.

For example, I have an entity.

public class UserStateEntity
{
    public Guid Id { get; set; }
    public string UserName { get; set; }
    public string Key { get; set; }
    public string Value { get; set; }
}

I need to store values by user and key. Now I use code like this:

public async Task AddCacheValueAsync(string userName, string key, string value)
{
    var existedData = await dbContext.UserCache.SingleOrDefaultAsync(d => d.UserName == userName && d.Key == key);
    if (existedData != null)
        existedData.Value = value;
    else
    {
        var userData = new UserStateEntity
        {
            UserName = userName,
            Key = key,
            Value = value
        };

        dbContext.UserCache.Add(userData);
    }

    await dbContext.SaveChangesAsync();
}

Is there a better way to find an existing record or create a record during updating cache value? I thought about something like this:

dbContext.UserCache.Upsert(d => d.UserName == userName && d.Key == key, d => {
    d.UserName = userName;
    d.Key = key;
    d.Value = value;
});

or like this:

dbContext.UserCache.Upsert(
    /* entity for compare or creation */ d => new UserStateEntity { d.UserName = userName, d.Key == key }, 
    /* updating fields */ d => d.Value = value);

I tried FlexLabs.Upsert library, but it always insert a new row instead of updating an existing one:

public async Task UpdateStateAsync()
{
    var userData = new UserStateEntity
    {
        UserName = "123",
        Key = "456",
        Value = "MainMenu"
    };
        
    await dbContext
        .UserData
        .Upsert(userData)
        .On(ud => new {ud.UserName, ud.Key})
        .WhenMatched(v => new UserDataEntity {Value = userData.Value})
        .RunAsync();

    await dbContext.SaveChangesAsync();
}

this code generates following SQL query for my MySql database:

INSERT INTO `bot.user_data` (`key`, `user_name`, `value`) 
VALUES ('456', '123', 'MainMenu') 
ON DUPLICATE KEY UPDATE `value` = 'MainMenu'
Vadim Martynov
  • 8,602
  • 5
  • 31
  • 43
  • Does [this library](https://github.com/artiomchi/FlexLabs.Upsert) help? – Richard Deeming Nov 24 '21 at 15:12
  • 1
    side-note `record` now has a different meaning (as a keyword) in .net from 5, so it's better not to use that word in another context. – JHBonarius Nov 24 '21 at 15:15
  • 1
    EF Core already "upserts" when it recognizes a record is new - when the key is 0 ro the equivalent, and the field marked as database-generated. When EF sees the key is "zero", it performs an INSERT. When it has a value, it performs an UPDATE. By using an explicit GUID you tolds EF Core that this is an already existing object – Panagiotis Kanavos Nov 24 '21 at 15:16
  • 1
    BTW GUIDs are horrible as keys. In SQL Server, MySQL and other databases the primary key is also a clustered key which determines how rows are stored on disk. A GUID is essentially a random number that can cause an insertion at the start, middle or end of a table. The server will have to move other rows around, resulting in fragmentation. SQL Server at least has the `NEWSEQUENTIALID()` function to generate sequential GUIDs, alleviating the problem. – Panagiotis Kanavos Nov 24 '21 at 15:20
  • @RichardDeeming wow this lib looks great, thanks! – Vadim Martynov Nov 24 '21 at 15:21
  • @PanagiotisKanavos thanks for your explanation. I have a problem because I don't know primary key before upsert and I don't want to have one more request to db for searching an existing value. – Vadim Martynov Nov 24 '21 at 15:26
  • Do not use Guid.New() as the default GUID implementation will lead to a huge amount of fragmentation in your clustered index. Worst case, implement an equivalent to A sequential GUID your DB supports. For instance, with SQL Server look at Ian Boyd's answer here: https://stackoverflow.com/questions/211498/is-there-a-net-equivalent-to-sql-servers-newsequentialid (2nd answer, not the accepted one, as that does not use a sequential value SQL Server will index efficiently) – Steve Py Nov 25 '21 at 01:28
  • @RichardDeeming oh sorry but I think that `FlexLabs.Upsert` uses behavior with `ON DUPLICATE KEY` but I need to update the row by other fields. – Vadim Martynov Nov 26 '21 at 15:03

0 Answers0