"Pro-Test" ASP. NET MVC5 + EF6 code First mode connection MySQL summary

The original address of this article is: https://www.cnblogs.com/summit7ca/p/5423637.html

The original test environment for Windows 8.1+vs2013+mysql5.7.12

I tested it under the win10+vs2017+mysql5.7.

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 with MySQL.Data.dll on default

I first install the latest version of EntityFramework (version number: 6.2)

Then install: MySql.Data.Entity.EF6 (after the installed name is MySql.Data.Entity.EF6, this item will automatically install Mysql.data)

Add a class MyDbContext.cs that inherits from DbContext:

namespace ef2mysqlapp.data{public    class Mydbcontext:dbcontext    {public        mydbcontext ()        {         } Public         dbset<bookinfo> bookinfoes {get; set;}}    }

Here, I amended to

 Public class Mydbcontext:dbcontext    {        public mydbcontext (): Base ("Name=mydbcontext")        {        }          protected override void Onmodelcreating (Dbmodelbuilder MB)        {            base. onmodelcreating (MB);        }          Public 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 is the configuration database connection string, now add connectionstring bytes under app. Config, do not write to configsections top, otherwise add migration will report node configuration error.

I am adding this content to the Web. config for the startup project.

<connectionStrings>  <add name= "Mydbcontext" connectionstring= "Data source=localhost;port=3306;i Nitial 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

in my test, this department will report an error " object reference not set to an instance of an object ":

don't worry about it, next .

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 configuration file. A file with a timestamp + the name you just entered for 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 # Dafa good", 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:

In the constructor of the configuration class, add:

setsqlgenerator ("MySql.Data.MySqlClient",new MySql.Data.Entity.MySqlMigrationSqlGenerator ());

Add Dbconfigurationtype properties to the 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 config file, personal preferred to use 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)

Here, because I will tie the connection string to write to the startup project's Web. config, the test did not error.

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:

' nvarchar (max) '.

This error is resolved because the MySQL field is incompatible with the string type and is annotated [MaxLength (100)] before the String type field (it is strange that I will not report this error with the vs2015 test.).

Here, I did not error during the test. This may be the reason VS2017 has improved. Therefore, no annotations are added to the String Type field [MaxLength (100)].

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.

Other error issues that may occur:

Error A. This error please check the connection string is correct, MySQL and MSSQL connection string is different. Verify that the connection is valid and open correctly.

System.Data.Entity.Core.ProviderIncompatibleException: The provider did not return a providermanifesttoken string. ---> MySql.Data.MySqlClient.MySqlException:Unable to connect to any of the specified MYSQL hosts.

Error B. Appear mysqlexception such problems are not installed Connector/net driver can be downloaded to the official website https://dev.mysql.com/downloads/connector/net/

This step is not required in VS2017.

unresolved member "Mysql.data.mysqlclient.mysqlexception,mysql.data, Version=6.9. 9.0, Culture=neutral, publickeytoken=c5687fc88969c44d "type.

"Pro-Test" ASP. NET MVC5 + EF6 code First mode connection MySQL summary