Atomic updates in Entity Framework-CodePudding

This is my simple example of incrementing record's value in EF:

public class context : DbContext
{
    public DbSet<Result> Results { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;Trusted_Connection=True;");
    }
}

public class Result
{
    public int Id { get; set; }
    public List<History> Histories { get; set; }
}

public class History
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
    public int Value { get; set; }
}

public static int Get()
{
    using (var db = new context())
    {
        var entity = db.Results
            .Include(x => x.Histories)
            .First(x => x.Id == 1);

        return entity.Histories.Last().Value;
    }
}

public static void Increment(int newValue)
{
    using (var db = new context())
    {
        using (var transaction = db.Database.BeginTransaction())
        {
            var entity = db.Results
                .Include(x => x.Histories)
                .First(x => x.Id == 1);

            if (entity.Histories.Last().Value != newValue)
            {
                entity.Histories.Add(new History() { Value = newValue });
                db.SaveChanges();
                transaction.Commit();
            }
        }
    }
}

Next, I launch simple console app several times:

while(true)
{
    var val = Service.Get();
    Service.Increment(val   1);
}

What I expect is Increment to be atomic, which means, there should be no History records with the same value (because of if (entity.Histories.Last().Value != newValue)). Unfortunately, when I run this in sql:

SELECT [value], COUNT(value) AS amount 
FROM history
WHERE ResultId = 1
GROUP BY [value]
ORDER BY COUNT(value) DESC

I can see, that in fact, some values are duplicated

value   amount
5       5
7       7
12      4

Whats wrong there? How can I make Increment to be atomic?

CodePudding user response：

First a crash course on projection. EF is an ORM that allows you to query data in a relational database and map it to entities (classes) or project pieces of information from the relevant tables, working out the necessary SQL for you.

So for instance instead of this:

public static int Get()
{
    using (var db = new context())
    {
        var entity = db.Results
            .Include(x => x.Histories)
            .First(x => x.Id == 1);

        return entity.Histories.Last().Value;
    }
}

It is much faster to do this:

public static int Get()
{
    using (var db = new context())
    {
        var lastHistoryValue = db.Results
            .Where(x => x.Id == 1)
            .SelectMany(x => x.Histories)
            .OrderByDescending(x => x.Date)
            .Select(x => x.Value)
            .FirstOrDefault();
        return lastHistoryValue;
    }
}

The first example is loading a Result and all Histories for that result into memory then attempting to get the last history, just to return it's value. (Accounting for the bug that there is no order by to determine what is the last item.)

The second example builds a query that will return the latest Historical Value. This doesn't load any entities into memory. You will get back a value of "0" if no History record is found.

Now when it comes to incrementing, this is where we typically do want to load entities and their related entities. EF works with a Transaction by default, so there is no need to complicate things introducing explicit ones:

public static void Increment(int newValue)
{
    using (var db = new context())
    {
        var entity = db.Results
            .Include(x => x.Histories)
            .Single(x => x.Id == 1);

        var latestHistory = entity.Histories
            .OrderByDescending(x => x.Date)
            .FirstOrDefault();

        if (latestHistory == null || latestHistory.Value != newValue)
        {
            entity.Histories.Add(new History() { Value = newValue });
            db.SaveChanges();
        }
    }
}

This also doesn't mean your check is that accurate since there is nothing stopping a value from being repeated. For instance:

Increment(4);
Increment(5);
Increment(4);

... would be perfectly valid. When the first increment happens provided the value wasn't already 4, the history would be added with a value of 4. Then a history would be added for 5. Then a history could be added for 4 again since the latest history would be "5", so you could end up with duplicates.