使用EF Core处理MySQL零日期 [英] Handling MySQL Zero Date with EF Core

问题描述

背景:我有200多个旧版VB6应用程序，它们正在使用EntityFramework Core作为我们的ORM转换为C#.不幸的是，许多应用程序都使用MySQL的零日期(0000-00-00).因此，我需要迎合这一点，并能够以某种方式存储零日期.更改在200多个应用程序中的基本工作方式目前还不可行.

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.

设置:我可以定义一个代表MySQL中字段定义的实体属性例如:

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.

问题:由于C#的最小日期为 0001-01-01 ，因此我无法明确存储零日期.所以我的问题是...有没有办法设置我的实体以使这个零日期进出数据库?

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??

到目前为止:我尝试使用定义为字符串的后备字段，以便可以操纵任何 DateTime.MinValue 成为'0000-00-00'.这使我可以存储零日期，但是在尝试检索数据时会导致强制转换问题(如您期望的那样):

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:无法转换类型的对象"System.DateTime"键入"System.String".

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

推荐答案

人们可能会认为这更适合作为注释，但从根本上说，这已经很久了.

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

并且:

由于我手边没有工作系统，因此您将不得不帮助我，所以我要从头开始.(我有点着急)

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.

例如，(这是一个不好的例子，因为我们需要实体中某个地方的上下文):

[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等的变更跟踪器进行读取或编写?

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.

也许 ModelBuilder 提供了一些其他选项.

Maybe the ModelBuilder exposes some additional options.

这篇关于使用EF Core处理MySQL零日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！