Processing MySQL Zero Date with EF Core

Question

Background: I have 200+ old VB6 applications that I convert to C # using EntityFramework Core as our ORM. Unfortunately, many applications use MySQL zero date (0000-00-00). So I need to satisfy this and be able to store the zero date in some way. Changing the main way of working in 200+ applications is currently not an option.

Setting: I can define an entity property that represents a field definition in MySQL for example:

 public DateTime ExpiryDate { get; set; }

...

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

This will save the zero date correctly if the value is not sent to the insert.

Problem: Since C # has a minimum date 0001-01-01, I cannot explicitly store the zero date. So my question is: is there a way to configure my entities to get this zero date to and from the database?

For now: I tried using a support field defined as a string so that I could manipulate anyone DateTime.MinValueto become '0000-00-00'. This allows me to keep the date null, but then causes the caste problem (as expected) when trying to get the data:

System.InvalidCastException: Cannot list an object of type 'System.DateTime' to enter 'System.String'.

Current packages I use:

• EFCore 1.1
• PomeloEntityFrameWorkCore 1.1.2
• MySQL 5.7.18

Answer 1

, , , .

, , . ( )

:

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

. , , . . , .

, (, , -):

[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
    }
 }

; EF? , EF ..?

, , CAST nvarchar SQL .

, ModelBuilder .