Handling MySQL Zero Date with EF Core

Question

Background: I have 200+ legacy VB6 apps which I am converting to C#, using EntityFramework Core as our ORM. Unfortunately a lot of the apps utilize MySQL's zero date (0000-00-00). So I need to cater for this and be able to store a zero date in some way. Changing the fundamental way this works in the 200+ apps is not currently an option.

Setup: I can define an entity property which represents the field definition in MySQL eg:

 public DateTime ExpiryDate { get; set; }

...

 entity.Property(e => e.ExpiryDate)
                       .IsRequired()
                       .HasColumnType("datetime")
                       .HasDefaultValueSql("'0000-00-00 00:00:00'");

This will correctly store a zero date if no value is sent on an insert.

Problem: Because C# has a minimum date of 0001-01-01 it is not possible for me to explicitly store a zero date. So my question is... Is there a way to set up my entities to get this zero date into and out of the database??

So far: I have tried using a backing field, defined as a string so that I can manipulate any DateTime.MinValue to become '0000-00-00'. This allows me to store the zero date but then causes a casting issue (as you would expect) when trying to retrieve the data:

System.InvalidCastException : Unable to cast object of type 'System.DateTime' to type 'System.String'.

Current packages I am using are:

EFCore 1.1
PomeloEntityFrameWorkCore 1.1.2
MySQL 5.7.18

Question: why is the field required (and nullable?) when you want a zero date? — Stefan, Aug 30 '17 at 11:58
Sorry for the confusion, I was using the nullable datetime in conjunction with my backing field tests. Now edited. — Bandito, Aug 30 '17 at 12:05
But still the question remains: the field is required, but you use a default null date as default. The point is; do you use this null date to ensure all new entries are expired by default? Or is it just a default value to mark the field as "unfilled". If it's the latter, you could allow `NULL` (i.e. making it not required) and do the same kind of checks to check the validity of the field. — Stefan, Aug 30 '17 at 12:10
It is the default value to mark the field as 'unfilled' using null is not an option as the legacy systems are specifically looking for the zero date and/or implicitly setting it to zero. — Bandito, Aug 30 '17 at 12:58
Ah, that makes sense. In order to proceed we need to know some additional info. 1) Does the database already exists? 2) Are multiple applications using the same database? — Stefan, Aug 30 '17 at 14:11
Yes, all 200+ apps use this pre-existing database, hence my hands are a bit tied when it comes to changing how this works. As I convert each application it will use EF Core for data access but I need to maintain some of the constraints/rules that are expected by the remaining applications. — Bandito, Aug 30 '17 at 14:25
Well, in a sense this makes it easier since your set of options is quite clear; you really *need* to "fix" this locally. Just flushing my thoughts here but there are several "hacks" you can investigate: 1) use a `[NotMapped]` property in your EF entity which would be filled by a raw sql statement. or 2) EF provide some custom functions EF/SQL translations, although I can't seem to find the correct terminology. ... or ... — Stefan, Aug 30 '17 at 14:34
@Stefan. I Guess that's what I am asking. How can this be wired up to work?? — Bandito, Aug 31 '17 at 07:00
To start, I don't have a MySql database at hand which allows `0000-00-00`, so it's hard for me to test if the solution works. But perhaps you can try to get the data out the database though a raw sql statement. A better option would be a sort of type conversion in EF, but I have to google on that a bit first. — Stefan, Aug 31 '17 at 07:19

score 0 · Answer 1 · answered Aug 31 '17 at 09:19

People might argue that this is better suited as a comment, but basically, it's to long for that.

And:

You'll have to help me out a bit since I don't have a working system at hand, so I am doing this from the top of my head. (and I am in a bit of a rush)

First, start out with a not mapped property:

[NotMapped]
public DateTime ExpiryDate { get; set; }

This property is not mapped. It might lead to some errors concerning the database does not match the model, but we can overcome that. This property will not be automatically filled when querying the data. So, we need a way to deal with this our self.

For example, (which is a bad example because we need the context in the entity somewhere):

[NotMapped]
public DateTime? ExpiryDate 
{
    get
    {
         //of course you'll need some caching here
         var s = context.Database.SqlQuery<string>("query to select datetime as string");
         //additional logic to determine validity:
         if (s == "0000-00-00")
             return null;
         //else:
         //do the conversion
    }
 }

The basic question here; how far do you want to go to support this within EF framework? Do you only need to read it, or write as well, using the change tracker of EF etc.?

There are other posibilities, for example, to perform a perform a CAST to nvarchar within the SQL itself to obtain the data and further process it.

Maybe the ModelBuilder exposes some additional options.

Thanks for all your suggestions. As I will need to retrieve and store these dates using EF and the change tracker I have concluded that it will be cleaner to use database triggers to convert the C# MinDate to a MySQL zero date upon insert/update/add. It seems that EF Core will allow custom type mapping in the future, that should resolve this issue: https://github.com/aspnet/EntityFrameworkCore/issues/242 — Bandito, Sep 05 '17 at 10:07

Handling MySQL Zero Date with EF Core

1 Answers1

Linked