Implementing dotnet ef database update command in MySql

Posted by Anuraj on Thursday, October 13, 2016

C# ASP.NET Core MySql EF Migrations

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.

Database update command

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.

Database update command

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

Happy Programming :)


Similar Posts


Did you like this article? Share it with your friends

Facebook Twitter Google+ LinkedIn Reddit StumbleUpon



MVP
Subscribe


Copyright © 2017 - Anuraj P. Blog content licensed under the Creative Commons CC BY 2.5 | Unless otherwise stated or granted, code samples licensed under the MIT license. This is a personal blog. The opinions expressed here represent my own and not those of my employer. Hosted with ❤ by GitHub