EF6 (Codefirst) +mysql development de-crater guide

nonsense

Words that year, in a spring midday, met the charming lilac girl, and then bowed in her pomegranate skirt, so far, the lilac Girl is ORM thought.

The so-called Orm thought, my understanding is that according to certain rules, the object in the program and the relational data in the database are mapped to each other. Before that year, I used ADO to write data persistence layer, splicing T-SQL statements, this is a rather tedious process, and for different databases, but also to adjust T-SQL statements. Remember, the first site on-line, the database changed to MySQL, in order to fit MySQL, I changed the SQL statement for two days. Now often think of, can not help shaking his head punctuate.

How do ORM ideas block the differences between big databases? The mapping between program objects and relational data is based on certain rules, and as long as the rules are set, their conversion is easy. So, what is the rule, and who will specify it? Rule-making can be easily understood as translation, which translates programming languages into database languages (T-SQL), regardless of which databases are compatible with the most basic T-SQL syntax, but each has some differences. In this way, different programming languages can develop the corresponding rules for different databases. Usually the database vendor or team or community will maintain these rules and, of course, can diy them.

The framework for implementing ORM ideas is also a dazzling array, such as:

Entityframework:.net programmers are most familiar with, supported by Microsoft.

Hibernate: Very popular, all versions are available.

Dapper: The most cool, seems to be hundreds of K.

And so on ...

Codefirst+mysql Use Posture

Title, this tutorial is to use the sentiment description of the ENTITYFRAMEWORK6 framework (crawling with tears) to treat the same fellow.

Since learning the basic use of EF, are in others to develop a project framework to do crud, a lot of details did not realize that this time there is a chance to personally build the project framework, in-depth experience of the secret.

Configuration: win10+vs2015community+.netframework4.5.2+mysql5.6

First, the basic operation
1. Create a console project: Codefirstdemo.

2. Install through NuGet Package Manager: EntityFramework6.1.3, mysql.data.entity6.9.8
Check whether the references in the project have four references shown

Add New Item →ado.net entity Object Model (named Mycontext) → empty codefirst model. The model automatically adds the connection string in app. config:

< connectionStrings >    <  name= "mycontext"  connectionString= "Data source= (LocalDb) \mssqllocaldb; Initial catalog=codefirstdemo.mycontext;integrated security=true; Multipleactiveresultsets=true; App=entityframework "  providerName=" System.Data.SqlClient "/></  connectionStrings>

Obviously this is the default MSSQLSERVER configuration and will be modified later.

3, in the Mycontext, the anti-comment out myentities extremely implements the class code.

 Public Virtual Get set; }

 Public class myentity    {        publicintgetset;}          Public string Get Set ; }    }

This is the object in the program.

According to the Codefirst convention: The ID is set as the primary key.

More conventions are described in MSDN (https://msdn.microsoft.com/zh-cn/data/jj679962).

4, how to map the myentities to the MySQL database? Three-step walk:

Open the VS tool

First step: Enter Enable-migrations (Start migration) in the console. Note: The default project must be the project where the model resides.

The migration folder is automatically generated in the project

Let me explain below the Configuration.cs class

This is the migrated configuration class, which is executed when the migration is last performed.

It has two methods:

A, congifuration (): Non-parametric construction method. You can configure the actions before migration here, which are described in more detail later in this article.

B, Send (mycontext): void: This method is executed after a successful migration, typically used to initialize the database. For example: I want to initialize a myentities record when I build the database.

protected Override voidSeed (Codefirstdemo.mycontext context) {//This method is called after migrating to the latest version. //You can use the dbset<t>. AddOrUpdate () Helper extension method//To avoid creating duplicate seed data.            e.g. //            //context. People.addorupdate (//p = p.fullname,//new Person {FullName = "Andrew Peters"},//new Person {FullName = "Brice Lambson"},//new Person {FullName = "Rowan Miller"}//    ); //Context. Myentities.addorupdate (Newmyentity () {Name="Zhang San"            }); Context.        SaveChanges (); }

Step two: In console input: Add-migration initmodel. (Initmodel: A name for this migration)

The migration record file is automatically generated in the migration folder of the project, and the name of the current time _ is the class name.

A record file has a design class and a resource class and a method for migrating specific operations.

Design class: The specific record of this migration. Automatically generated, maintained.

Resource class: Default schema and Target. Automatically generated, maintained.

What to do: This class maintains up () and down () two methods, respectively, for upgrade and demotion. Sometimes we need our own customization, so to understand it, you can refer to the comments I wrote.

 Public Partial classinitmodel:dbmigration {/// <summary>        ///The specific operation that this migration performs, namely upgrade the database. /// </summary>         Public Override voidUp () {//Create a database table with a schema of "dbo" and a table named "Myentities". CreateTable ("dbo. Myentities", C=New{Id= C.int (Nullable:false, Identity:true),//id,int Type, non-nullable, identity column (autogrow). Name = C.string (),//name,string Type                    })                . PrimaryKey (t= T.id);//set primary key to ID                    }        /// <summary>        ///This demotion method is executed later if the database is rolled back. /// </summary>         Public Override voidDown () {droptable ("dbo. Myentities");//Delete the database table with the schema "dbo" and the table named "Myentities".         }    }

Step three: Enter Update-database–script in the console. Generate SQL scripts. (When the migration process is often a wonderful problem, generate SQL script to check it.) ）

CREATE TABLE [dbo].[myentities] (    [Id] [int]  not NULL IDENTITY,    [Name] [nvarchar](Max),    CONSTRAINT [pk_dbo. Myentities] PRIMARY KEY([Id]))CREATE TABLE [dbo].[__migrationhistory] (    [Migrationid] [nvarchar]( Max) not NULL,    [Contextkey] [nvarchar]( -) not NULL,    [Model] [varbinary](Max) not NULL,    [productversion] [nvarchar]( +) not NULL,    CONSTRAINT [pk_dbo.__migrationhistory] PRIMARY KEY([Migrationid],[Contextkey]))INSERT [dbo].[__migrationhistory]([Migrationid],[Contextkey],[Model],[productversion])VALUES(N'201601021118458_initmodel'N'CodeFirstDemo.Migrations.Configuration',0x1f8b0800000000000400cd57db6e1b37107d0fd07f20f8d400b6e8cb4b6aac12b8b25d18896c23ebe49dda1dc94479d9925c43fb6d79c827f5173ad 4de7725457683a2102068676786670ee70ca9bfbf7d8f3eac9524cf609d307a4a4f272794804e4c2af46a4a73bf3c7e473fbcffe54d749daa35f95afb 9d073f8cd46e4a9fbccf2e1873c91328ee264a24d638b3f493c428c653c3ce4e4e7e63a7a70c3005c55c84449f73ed8582cd033ece8c4e20f33997739 38274951ddfc49bace48e2b70194f604a67e87123acf357a00c259752700411835c52c2b5369e7b8478f1c541ecadd1ab384303978f4506e8b7e4d241 05fda2753fb48a93b350056b03eb5449eebc512f4c787a5ed1c286e1af229736b42171d748b02f42d51bf2a6745e94264a868b5dcca40d8e036e2775c 411e9d98f9a36c06e091f7c9f4b9f5b986ac8bde5f2883ce40b29928f503c9a3f414f752e65171de2c3773d039a1eacc9c0fae2332c2bccb72925ac1f C786814d5827a6ace656fbf3334aee7071be90d06c7ea7f2d81b0b7f8006cb3da40fdc7bb03ae4808a2bb677adf05daf86dd869aa164ced79f40affc1 352ced794dc8835a4b5a542f0450b941806799b437f9188b53b37de4f148ae702e1369b1a2cb0f65b761515506dacab16ead752668cc1f71b4480a3a4 c5504a6bd276cf36b00dac56b8ac546ead70b643e2d19c6719f2d6917c652171a5f7e3f8e56a50650e96b82da268d0362b6117f0150cde76e5c03d5ff0b073b3548ddcda4dd84170bdce88e7a 1025adaeb90f0bb0cdbaecd618a96bf1b2c49611b6faa83064b67108c4237f3964b6eb7486a6664aef42e59ee8b2e45d28d2f2de30c111b601ff2c346 040d86c390f27dbd3a7469566f7a76d09b51d5272f39a3aac6295d28416a9e451a9a262e9c0735090e93f82f399302eb6d1de65c8b25385fce4f8a7df D6e70d6fd7fce1de65c2a0f3c7cfef3434004567f38e64767c4e1735f3f739b3c71fbabe2ebb7dd4cff76b68fa7cf41f3bbd83bbdcb469cd2748177a7 C7126877f0bf72ba8f9511b1ee1d2fba0227566d8a70e3d39084966b93d63eb77a696ac2b1bc2ea2da65b01f73f03c45962ead174b9e787c9d80739bf 3f82b9739ba5cab05a4b7fa3ef759ee2f9d03b590bdd32c62fbd7df1c617dccd17d169edccf2801610a2c01eef5efb9906983fb66dc8fbb528486a99a 1c51e17d04d3ad8a26d39dd10726aae8bb820c7490c823a84c623277af63fe0cafc18697914fb0e249510fb8dd497ebc117ddaa32bc157962b57e568e 3c3ff1616feb8bcff07a10c3040ea0c0000N'6.1.3-40302')

As shown in: the first paragraph to create a table myentities is our goal, that's right, it's normal.

Second paragraph: CREATE table _migrationhistory, what the hell is this? Actually this is the table that the EF6 framework uses to maintain the model,

[Migrationid]: number, primary key, storage design class ID, is actually the name of this migration record.

[Contextkey]: marks the current context identifier, primary key, because Codefirst supports multiple context instances, so use this field to mark.

[model]: models, the specific description of the current model, encrypted.

[The version number of the Productversion]:ef frame.

The EF6 framework uses this table to record the changes in each migration, and it also checks the consistency of the relationship between the objects in the program and the database through this table.

Continue with the console input: Update-database–verbose (or-V). Update the database, and view the specific SQL statements that are executed.

Results:

Oh, by looking at the error message, we can know that the current configuration is MSSQLSERVER, and I did not install the MSSQLServer DB instance, so of course the connection is not. Our goal is the MySQL database, so we need to modify the connection string in app. Config.

Open App. Config:

Locate the connection string:

< connectionStrings >    <  name= "mycontext"  connectionString= "Data source= (LocalDb) \mssqllocaldb; Initial catalog=codefirstdemo.mycontext;integrated security=true; Multipleactiveresultsets=true; App=entityframework "  providerName=" System.Data.SqlClient "/>   </connectionStrings>

Modify the connection string to MySQL:

< connectionStrings >    <  name= "mycontext"  connectionString= "Data source=127.0.0.1;port=3306;i Nitial catalog=codefirstdemodb;user id=root;password=1234; " ProviderName = "MySql.Data.MySqlClient" />  </ connectionStrings >

In the console again, enter: Update-database–verbose.

This is because the current SQL generator is still MSSQLSERVER, so how do I start the MySQL SQL generator?

In the construction method of the configuration class Congifuration in the Migration folder:

 Public Configuration ()        {            false;            Setsqlgenerator ("MySql.Data.MySqlClient"new MySql.Data.Entity.MySqlMigrationSqlGenerator ()); // set SQL generator to MySQL        }

In the console again, enter: Update-database–verbose.

I wipe tears!!!

This is the Myentity.name property of type string, directly mapped to MySQL is longtext, while MySQL supports a maximum length of 767bytes.

You can use the DataAnnotations (data annotation) method to configure the mapping rules for the Myentity.name property:

 Public class myentity    {        publicintgetset;}        [MaxLength (+)]          Public string Get Set ; }    }

To be Continued ...

Count MySQL in a particularly fascinating (Keng) place

EF6 (Codefirst) +mysql development de-crater guide