1

Trying to implement Optimistic Concurrency with Entity Framework 6 (Designer First approach) and Oracle database (ver 12).

SQL Server has RowVersion attribute which can be used to track the changes, couldn't find any similar attribute in Oracle.

Was able to use a Trigger to simulate the functionality of RowVersion, just wondering whether there are any other alternatives instead of creating a Trigger for the sole purpose of versioning.

Thanks

skjagini
  • 3,142
  • 5
  • 34
  • 63
  • 1
    Possible duplicate of [SQL Server : RowVersion equivalent in Oracle](http://stackoverflow.com/questions/20487657/sql-server-rowversion-equivalent-in-oracle) – Fran Jan 27 '17 at 20:27
  • Can you elaborate on what you are trying to achieve? Oracle has a very sophisticated and largely automatic concurrency mechanism. – BobC Jan 27 '17 at 22:30

2 Answers2

2

Sure, there is a way of doing it without trigger. Doing it without a trigger will work with any database supported by Entity Framework providers...

Unfortunately, I am not sure about Designer First approach but with Code First you can do it like this(it's probably similar with Designer First, maybe using partial class). Looking forward, if possible, you should use Code First, because it's only way supported by new Entity Framework Core.

Model:

public abstract class ConcurrencyTracker
{
    [ConcurrencyCheck]
    public long LastChange { get; set; }

    [NotMapped]
    public DateTime LastChangeTime
    {
        set
        {
            long timestamp = value.Ticks - new DateTime(1970, 1, 1).Ticks;
            timestamp = timestamp / TimeSpan.TicksPerSecond;
            LastChange = timestamp;
        }
    }
}

public class Product : ConcurrencyTracker
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Context:

public override int SaveChanges()
{
    var currentTime = DateTime.Now;

    var changed = ChangeTracker.Entries<ConcurrencyTracker>().Where(c => c.State != EntityState.Unchanged);
    if (changed != null)
    {
        foreach (var item in changed)
        {
            item.Entity.LastChangeTime = currentTime;
        }
    }
    return base.SaveChanges();
}

EF will include LastChange column in all updates of Product model so there is optimistic concurrency.

Marko M.
  • 789
  • 3
  • 9
  • Can you explain in English, as opposed to code, what you at trying to do? – BobC Jan 29 '17 at 16:29
  • 1
    Only thing "special" here is that we are updating LastChange column instead of letting database to update it. That's all. Feel free to try it... – Marko M. Jan 29 '17 at 16:50
  • 1
    ConcurrencyTracker is custom abstract class. I created it and gave it that name. You can rename it anyway you like it. So, it is available. Try pasting all that code in a new test Console application and make it work, then use it in your existing project. – Marko M. Jan 30 '17 at 21:18
  • nice. How really works? is there a complete example (githup) for that? – Marco Mangia Feb 16 '18 at 11:43
  • I think the line "timestamp = timestamp / TimeSpan.TicksPerSecond;" is not necessary. It rounds the concurrency token to the precision of a second. So if you have two concurrent writes within one second, the concurrency will not be detected. – VeganHunter Mar 13 '18 at 23:45
  • don't understand how this solution ensures optimistic concurrency? Doing it this way in EF causes a conflict. Lastchange column is set on client and at the same this value is included in Where-Clause (concurrencyMode=Fixed is assumed); Value of is not written to database. so wher is optimistic locking here? – Karl Nov 10 '18 at 10:05
  • @Karl: EF Core (starting with EF Core 3.0) uses the original value (when the entity was attached or loaded from the database with change tracking enabled) for the where clause, wherein the code it's new value is set to the current date. This is made so that it works in this case, generating a pseudo query like `UPDATE ... SET LastChange = '' WHERE Id= AND LastChange = ''`. EF Core 1.0 and 2.0 the above code shouldn't work – Tseng Apr 22 '21 at 08:44
0

You can use the ORA_ROWSCN pseudo column in Oracle rather than creating and maintaining a timestamp. You will need to create the tables with ROWDEPENDENCIES enabled

BobC
  • 4,208
  • 1
  • 12
  • 15
  • 2
    From what I have come across ORA_ROWSCN is not available at row level to use it through Entity Framework. – skjagini Jan 30 '17 at 18:16