An example of code first development with entityframework and MySQL database Problems

In recent projects, I plan to use entityframework to work with MySQL databases, but I don't want to have some helpless problems. The development environment is as follows:

Entityframework version: 4.3.1

. NET Framework: 4.0

MySQL version: 5.5

Connector/Net version: 6.5.4

 

The Demo code is relatively simple. Create a console application project:

public class Post
{
public int Id { get; set; }
public string Title { get; set; }
public string Content { get; set; }
}

public class Blog : DbContext
{
public IDbSet<Post> Posts { get; set; }
}

static void Main(string[] args)
{
using (var blog = new Blog())
    {
var post = new Post()
          {
              Title = "Test Title",
              Content = "This is my content for test"
          };
          blog.Posts.Add(post);
          blog.SaveChanges();
     }
}

The configuration of APP. config is as follows:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<add name="Blog" connectionString="server=localhost;User Id=root;Pwd=password;Persist Security Info=True;database=Blog" providerName="MySql.Data.MySqlClient" />
</connectionStrings>
</configuration>

 

It can be executed normally in sqlserver, but after changing to MySQLFirst RunWill throw an exception, the content is as follows:

 

Exception: mysql. Data. mysqlclient. mysqlexception: You have an error in your SQL syntax;

Check the manual that corresponds to your MySQL Server version for the right syntax to use near

'Not null,

'Productversion' mediumtext not null );

Alter table 'historyrow' at line 6

 

I guess from the content that it is related to the new features of ef4.3.1. The new version adds the codefirst migrations function. During database initialization, A migrationhistory table will be created to the database, so I tried to capture the relevant SQL script as follows:

CREATE TABLE `__MigrationHistory`(
  `MigrationId` mediumtext NOT NULL, 
 `CreatedOn` datetime NOT NULL, 
 `Model` varbinary NOT NULL, 
 `ProductVersion` mediumtext NOT NULL);

This script is stored in the MySQL Command Line for execution, which is also the same exception information. Check the error message carefully and find that there is a problem with the varbinary type. In MySQL, it cannot be identified correctly. Instead, it can be passed only when it is changed to varbinary (1, that is, the length must be specified for this type. However, this is obviously generated automatically by EF and cannot be modified. Therefore, check the official documentation of msdn and find the configuration class for codefirst migrations. If you do not need to generate it automatically, you can configure the automaticmigrationsenabled attribute of dbmigrationsconfiguration to disable it, according to the help example, I wrote the relevant code to inherit this class. Unfortunately, it still has no effect. It may be that the settings are not correct. Of course, I want to use this migration function in more cases, but I don't know if EF provides an interface for developers to automatically generate scripts? No answer is found. Therefore, in order to smoothly use the combination of codefirst and MySQL, we had to avoid this problem, and reduced the EF version to 4.1.20.15, and then everything could run normally.