entity framework 6 mysql rowversion

Question

I am using entity framework 6 with my sql and seems like row version byte array is not supported by mysql. any help how this can be achieved.

[Column(TypeName = "timestamp")]
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime TimeStamp { get; set; }

Answer 1

First of all, if you are using automatic migration I think that the property attributes are not enough to create the right field type.
Here https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html there are the syntax that EF provider should generate to create a timestamp that is automatically generated/updated.

After creating the right field type you could do 2 tries:

-Mark the field as Timestamp

[Timestamp]
public DateTime TimeStamp { get; set; }

I don't think that EF needs that a Timestamp field is byte[]. Timestamp should only mean that is database generated field and that the optimistic concurrency use that field (i.e. update queries contains a where on the keys of the record to update and this field). But often EF does not work as I think...

-Mark the field as generated by database and as a field to use to check for optimistic concurrency exceptions

[ConcurrencyCheck]
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime TimeStamp { get; set; }

Answer 2

Sadly, if you use a DateTime value for optimistic locking it can fail due to loss of precision in the Oracle MySQL driver. There are bug reports on this that are years old that apparently they are refusing to fix.

Update: I have just submitted a PR to MySQL .NET Connector v6.9.10 that provides a solution for this issue that provides optimistic locking between EF and non-EF applications. See https://stackoverflow.com/a/50147396/365261 for more details.

As a workaround, you can create your own Non-DATETIME optimistic locking column. By setting this value via DB trigger (rather in c# code) we ensure that it works for external apps and any DB admin tasks.

• Add column rowversion BIGINT NOT NULL DEFAULT 0,
• Add a trigger to set this field to random or sequential value.
• Add E6 attributes on the new column.

Sequential rowversion

CREATE TRIGGER `trg_mytable_before_update` 
BEFORE UPDATE ON `mytable` 
FOR EACH ROW SET NEW.`rowversion` = OLD.`rowversion` + 1;

Random rowversion

CREATE TRIGGER `trg_mytable_before_update` 
BEFORE UPDATE ON `mytable` 
FOR EACH ROW SET NEW.`rowversion` = FLOOR(1 + RAND() * POW(2,54));

EF6 Attributes

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
[ConcurrencyCheck]
[Column("rowversion", TypeName = "bigint")]
public virtual long RowVersion { get; set; }