ASP. NET MVC uses EF6 code first mode to connect MySQL summary

Recently due to a server change to a Linux system. It takes a long time for the MSSQL for Linux to come out to use in the production environment. So consider using the MySQL database, ORM using EF. So first step on the pit and record, the need for TX can refer to.
When you consider using EF to connect to MySQL, you must have searched the Internet for a bunch of tutorials. The online tutorial is basically a demonstration using the console. Follow the steps to the right hand, it may work correctly, but after using layering in the project, the data layer is stripped out, and then the code first connects to the instant B. A variety of exotic problems followed. I'm not the same as the tutorial. So this article steps through how to use EF code first to connect to MySQL in a layered project.
PS: This article tests the environment for Windows 8.1+vs2013+mysql5.7.12

I. Setting up the environment and installing the corresponding components

Create an empty MVC project first and then simply create a class library project for EF Operations and entity storage (here to demonstrate that there are not too many projects created). The end result is as follows:

And then in. The data project uses NuGet to install EF. Then install the MySQL data-driven required DLLs:

MySQL.Data.Entities.dll  //nuget will be MySQL.Data.dll with the default

Add a class MyDbContext.cs that inherits from DbContext:

namespace EF2MySqlApp.Data
{
    public class MyDbContext:DbContext
    {
        public MyDbContext()
        {

        }

        public DbSet<BookInfo> BookInfoes { get; set; }
    }
}

Then build a folder to put the entity class BookInfo.cs:

namespace EF2MySqlApp.Data
{
    public class BookInfo
    {
        public int Id { get; set; }

        public string Number { get; set; }

        public string Name { get; set; }

        public string Author { get; set; }

        public decimal Price { get; set; }

        public bool Deleted { get; set; }

        public DateTime CreatedOn { get; set; }
    }
}

The structure is similar to this:

Two. Configure EF

Then it's the metabase connection string, now add connectionString bytes under app. Config, and don't write to configsections . Otherwise, adding migration will report the node configuration error.

<connectionStrings>
  <add name="MyDbContext" connectionString="Data Source=localhost;port=3306;Initial Catalog=myappdb;uid=root;password=123456;Charset=utf8" providerName="MySql.Data.MySqlClient" />
</connectionStrings>

Then open the Package Manager console (-_-does not know the custom search), the default item selection. Data project, and then enter this command:

Enable-migrations  //Enable migration

No error prompt followed by input:

Add-migration record1  //To this migration record a name This name can be casually up, try not to notice the names here migration no s end.

At this point you see a few more files under the project:
configuration. CS is the pre-migration profile. A file with a timestamp + the name you just entered as the migration log file. This article is not focused on this, so the role of the various functions in this do not introduce too much. be interested to search and learn by yourself.
In the constructor of the configuration class, there is a sentence in which the function is to enable automatic migration, which is not enabled by default:

Automaticmigrationsenabled=false;

Seed data can be added to the seed method, which will be executed after the migration succeeds, inserting the data into the database.

protected override void Seed(EF2MySqlApp.Data.MyDbContext context)
{
context.BookInfoes.AddOrUpdate(x => x.Id,
New bookinfo {name = "C ා great method", author = "summit", number = "1234", price = 1024}
);
context.SaveChanges();
}

Note that you need to save the last

This time if the direct update-database will report the SQL Server connection failure error: The following:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or could not be accessed. ....

The question is, how do you want to connect to MySQL by default SQL Server is used. This is the EF default connection is SQL Server so to tell EF we need to use MySQL:
1.

In the constructor of the configuration class, add: Setsqlgenerator ("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator ());

2.

Add dbconfigurationtype attribute to mydbcontext class:
[DbConfigurationType(typeof(MySql.Data.Entity.MySqlEFConfiguration))]
public class MyDbContext:DbContext
{
public MyDbContext()
{
}
public DbSet<BookInfo> BookInfoes { get; set; }
}
PS: this can also be configured in the config file. I prefer code control

Run update-database-v still error, but prompt string format is not correct, upside down see dbcontenniton Get string error, and then guess whether it is a connection string problem:

System.ArgumentException: Starting at index 0, the format of the initialization string does not conform to the specification. In System.Data.Common.DbConnectionOptions.GetKeyValuePair (String connectionString, Int32 currentposition, StringBuilder buffer, Boolean useodbcrules, string& KeyName, string& keyvalue)

The connection string test did not find the problem, it will not be able to find the connection string it? n long after discovering that the config file used in this console is the default to find the config file under the current startup item.

Run the report again after setting the data project as the startup item:

The underlying provider does not support the type ' nvarchar (max) '.

This error is resolved because the MySQL field is incompatible with the string type and is annotated [MaxLength] before the String type field (it is strange that I will not report this error with the vs2015 test.).
Execute update-database-v successfully again!
If you are using the MySQL command line: Enter these commands to see if the creation was successful:

show databases;
use myappdb;
select * from bookinfoes;

With other clients, that's even more than saying.

Three. Legacy issues

1. Do not set the data project for the startup item cannot find the string configured in app. Config under this item (console default project is already the project)
If you put the connection string in Web. config, you need to install Ef,mysql.data in the project ... But personal reasons do not want to appear in the presentation of EF and other things, do not go to find the configuration of App. Config.
It's been a long time. It's a hassle to set the startup item this way, so you can manually specify the connection string (or write it somewhere else to put it in):

public MyDbContext()
            : base("Data Source=127.0.0.1;port=3306;Initial Catalog=myappdb;uid=root;password=123456;Charset=utf8")
        {

        }

PS: If an error occurs during migration that does not refer to an object to an instance, most of the reason is that the connection string cannot be found or is invalid, and this problem is first removed from the connection string.

2. Inserting data contains garbled characters in Chinese.
This question on the Internet, change this configuration to change that configuration. Then the egg will be found after the change. I inserted the Chinese after the use of the MySQL console to view the data garbled, using navicat viewing is normal. So this question is still in the observation, if you have any good solution can leave a message:)

3. A few days ago, a friend asked a question, he used Miniprofiler monitor EF to MySQL monitor not to SQL statement. I didn't know he had solved it.
Again: If the error was reported using the Miniprofiler process:

The Entity Framework is already using an dbconfiguration instance before attempting to add the "Loaded" event handler.

Check to see if your project is using the initial data configuration of EF. You need to put miniprofileref6.initialize () when you use it; this sentence is placed before the initialization configuration. (I usually put it directly to the front:))

The pit is just beginning, there will certainly be a variety of problems in the use of the process. will continue to record, to the needy friends to wake up.
If there is an old driver in the crossing still hope to enlighten. -_-!

ASP. NET MVC uses EF6 code first mode to connect MySQL summary