This post is about implementing dotnet ef database update command with MySql connector (MySql.Data.EntityFrameworkCore). Few days back I did a post on using MySql in ASP.NET Core. But one problem I found was when calling dotnet ef database update command, the lib was throwing not implemented exception. I had a discussion with @RuAnt, and I found the github repo - https://github.com/mysql/mysql-connector-net. As I got some time today, I thought of exploring the source and implementing the same, if it is not complex. :) So as a first step, I looked into the Sqlite and SqlServer implementations, and from the exception stack, I come to know about the method, which is not implemented.

It was ExistsSql property in the MySQLHistoryRepository.cs class. I looked the implementation in SqlServer and I found it is looking for existing tables, using SELECT OBJECTID. Then I searched how I can do same with MySql, something like this.

SELECT COUNT(*) FROM information_schema.tables WHERE 
    table_schema = 'SCHEMA' AND table_name='TABLE NAME' 

And I implemented same in C# something like this.

protected override string ExistsSql
{
    get
    {
        var builder = new StringBuilder();
        builder.Append("SELECT COUNT(*) FROM information_schema.tables WHERE ");
        if (TableSchema != null)
        {
            builder.Append($"table_schema = '{SqlGenerationHelper.EscapeLiteral(TableSchema)}' AND");
        }

        builder.Append($"table_name='{SqlGenerationHelper.EscapeLiteral(TableName)}'");
        return builder.ToString();
    }
}

Also I implemented one more method, InterpretExistsResult which return bool based on the value from ExistsSql (based on my assumptions), which is like this.

protected override bool InterpretExistsResult(object value) => (long)value != 0L;

I found the database update working most of the scenarios, except when your model class has DateTime type. When you’re using DateTime EF Code migrations are generating a default value, which is causing problem while executing the database update. To make this work, you need to open the migrations file and remove the default DateTime value.

And here is the database update command execution, after implementation.

Yes there are some warnings I am getting related to the MySql.Data package, but right now I am ignoring that.

Happy Programming :)