1

I make queries to MySQL database through EF Core to get entries. Some of these entries have zero dates 

0000-00-00 00:00:00

which raises the famous MySql.Data.Types.MySqlConversionException exception:

'Unable to convert MySQL date/time value to System.DateTime'

I tried to solve this by adding to my connection string the Allow Zero Date flag (in all the different ways possible) - didn't work.

Then I tried to write an interceptor that overrides ReaderExecuted method:

class DateTimeInterceptor : Microsoft.EntityFrameworkCore.Diagnostics.DbCommandInterceptor
        {
            public override DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData, DbDataReader result)
            {
                var columns = result.GetColumnSchema();
                for (int columnIndex = 0; columnIndex < columns.Count; columnIndex++)
                {
                    if (columns[columnIndex].DataType.Name == "DateTime")
                    {
                        var columnName = columns[columnIndex].BaseColumnName;
                    }
                }

                // TODO: check the datetime column values and if needed replace them
                return base.ReaderExecuted(command, eventData, result);
            }

Which kinda can work, if I manually read in correct way all the model data, copy it to another reader and pass as a result. That doesn't feel right though, I have 11 models and I don't want to implement manually the readers for all of them - too stupid.

So, what else can I do here? Basically what I need is to search and replace values of certain fields before accessing them.

And here is how these fields are defined in my model class:

[Column("created_at")]
public string CreatedAt { get; set; }

[Column("updated_at")]
public string UpdatedAt { get; set; }

There is this solution, but it doesn't look very neat either...

Movsar Bekaev
  • 888
  • 1
  • 12
  • 30
  • Can you clean 0 dates out of your data and replace with NULL? Will the application still work then? – danblack May 02 '20 at 08:35
  • 1
    from the design, it's always important to know if an entity will require to have a date when inserted or not. If it doesn't your date column should be `Nullable`. – Mosia Thabo May 02 '20 at 08:38
  • @MosiaThabo if you mean to use [Not Mapped] - yes, I could do that, but I'd like to have my app be able to check the input without me thinking for it )) – Movsar Bekaev May 02 '20 at 08:42
  • @danblack I could, it would, but I don't want to do that, the program should do that – Movsar Bekaev May 02 '20 at 08:43
  • You're going down a path of manually writing readers and you are right to think isn't the right way. 0000-00-00 isn't a a valid date (not recently anyway) so you need to stop letting MySQL treat it like one. But by all means continue to write readers or subclass of a single implementation to correct a data storage fault. – danblack May 02 '20 at 08:54
  • @danblack if it weren't valid date for mysql, it should not have set it when I changed some indexes on the table, that value has been set automatically, that's why I want to deal with it automatically, but thanks for your input anyways, maybe you are right. – Movsar Bekaev May 02 '20 at 08:57
  • No, [Not Mapped] is something totally different. What I'm looking for is `DateTime?` on your Models. And then on your database, you can set your collumn to take null values. Here's how: https://stackoverflow.com/questions/1691117/how-to-store-null-values-in-datetime-fields-in-mysql – Mosia Thabo May 02 '20 at 09:12

0 Answers0