Simon Simon - 6 months ago 48
MySQL Question

DBFirst - Using MySQL Connector and EF6, How to recognize timestamp columns as StoreGeneratedPattern = Computed?

I have a table like so:

CREATE TABLE `incidentdata` (
`IncidentDataID` int(11) NOT NULL AUTO_INCREMENT,
`GUID` binary(16) NOT NULL,
`LastModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Incident_ID` int(11) NOT NULL,
`Parameter_ID` int(11) NOT NULL,
`Value` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`IncidentDataID`),
UNIQUE KEY `GUID_UNIQUE` (`GUID`),
KEY `FK_INCDATA_INC` (`Incident_ID`),
KEY `FK_INCDATA_PAR` (`Parameter_ID`),
CONSTRAINT `FK_INCDATA_INC` FOREIGN KEY (`Incident_ID`) REFERENCES `incidents` (`IncidentID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_INCDATA_PAR` FOREIGN KEY (`Parameter_ID`) REFERENCES `eventparameters` (`ParameterID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$


When using EF 6 and MySQL Connector 6.9.8 and MySQL 5.5.21, updating the model via DB first always sets the
LastModified
column to be
StoreGeneratedPattern = None
.

When I insert a record in this table, the
LastModified
column is always 0 - because the default value in .NET of
System.DateTime
= 0. When I update the record, its
LastModified
value is then updated to be
CURRENT_TIMESTAMP
as desired.

I have found two options that I can use as a workaround:


  1. Manually change the StoreGeneratedPattern = 'Computed'. This is always overwritten when we change the schema (which is every day at the moment!)

  2. Set the column to allow
    NULL
    , and then explicitly set the value of
    LastModified
    to null when inserting in EF.



As you can see both methods require some thought to remember to do something - and alas it has already been forgotten.

I have also tried to use OnModelCreating() to force the
Computed
property of this column but it doesn't seem to work.

Is there any way EF/MySQL connector can correctly recognize the
LastModified
column to be
Computed
when updating the model from the DB?

Answer

I have since moved away from MySQL and EF - there are minor quirks in its implementation, especially when using asynchronous implementations like ToListAsync(), FirstOrDefaultAsync() etc.

If anyone goes down this path, just remember that while it 'appears' to be working, I don't think it is stable enough, and the odd exception like 'read-only table' for example start to show when using async methods in EF.

There isn't a way to achieve my question using EF alone (but neither in SQL Server too).

Comments