In ASP.Net Core 2.x with Entity Framework Core, Concurrency Control not working with SQLite

[ajcvickers]

From VS Feedback:

I studied this article on Concurrency Control (+ other related articles) and implemented the same for SQLite with the following differences:

Created a DATETIME column (internally TEXT for SQLite) which is updated by a Trigger (after INSERT and UPDATE). I did this because there is no equivalent of a Timestamp / Rowversion in SQLite
I created the SQLite Db and Tables first and then created the model and DbContext from that (existing) Db as I couldn't do dotnet-ef migrations
What I am getting is the "Client Always Wins" scenario or the "Last Update wins" whereas I want the "Store wins" to be implemented. I have also asked the same question in StackOverflow with much more details but so far no answer for that.

Hence asking here. Kindly note that I remember seeing the "Store-Wins" work as expected some 4 / 5 VS2017 updates before after which I didn't check but recently noticed that it's not working. Also, I am having this problem with .Net Core 2.0.x and the latest 2.1.300 as well and I am using VS2017 15.7.3 on Windows 10 Pro x64.

Thanks in advance

rgds,
Vatsan

[bricelam]

Concurrency check is working as expected. Here's a sample:

class Status
{
    public int Id { get; set; }
    public string Text { get; set; }

    [Timestamp]
    public byte[] Timestamp { get; set; }
}

class Context : DbContext
{
    public DbSet<Status> Statuses { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=Timestamp.db");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
        => modelBuilder.Entity<Status>()
            .HasData(new Status { Id = 1, Text = "This is a test" });
}

class Program
{
    static void Main()
    {
        var db1 = new Context();
        var db2 = new Context();

        db1.Database.EnsureDeleted();
        db1.Database.EnsureCreated();

        db1.Database.ExecuteSqlCommand(
        @"
            CREATE TRIGGER SetStatusTimestamp
            AFTER UPDATE ON Statuses
            BEGIN
                UPDATE Statuses
                SET Timestamp = randomblob(8)
                WHERE rowid = NEW.rowid;
            END
        ");

        var status1 = db1.Statuses.First(s => s.Id == 1);
        var status2 = db2.Statuses.First(s => s.Id == 1);

        status1.Text = "First update";
        db1.SaveChanges();

        status2.Text = "Second update";
        db2.SaveChanges(); // Throws DbUpdateConcurrencyException
    }
}

[ajcvickers]

EF Team Triage: This issue is lacking enough information for us to be able to effectively triage it. In particular, it is missing the following information requested in the new issue template. Can you please provide this information?

Steps to reproduce

Ideally include a complete code listing that we can run to reproduce the issue.
Alternatively, you can provide a project/solution that we can run.

BTW we're not just doing this to be mean 😄... we get a lot traffic on this project and it takes time to attempt to reproduce an issue based on fragments of information. In addition, our attempt is often unsuccessful as the exact conditions required to hit the issue are often not explicitly included in the code provided. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we ask that folks give us a self-contained way to reproduce an issue.

For a guide on submitting good bug reports, read Painless Bug Tracking.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.

[tww19861004]

very good

[ggirard07]

Is there a way to use parameters in @bricelam solution, for the table name and column to set for example?

My aim is to patch any entity with a concurrency token based on the Model available in the db context.

Right now I can't find a solution which does not end-up with SQLite Error 1: 'near "@p0": syntax error'.. ExecuteSqlCommand properly replaces arguments in my statement, but I am not sure this syntax is actually supported by SQLite...

[bricelam]

@ggirard07 You need to create one trigger per table.