在EF中使用MySQL的方法及常见问题
有时需要在网上租用空间或数据库,mysql成本低一些,所以想将sql server转成mysql……
注意:在安装mysql时要选择文字集为utf8,否则将不能使用中文(当前也可以在创建数据库时使用utf8,不过我不知道在ef生成数据库时如何设置,希望高手指点)
一、在项目中引用mysql的ef包
通过nuget包管理器安装:entityframework6.1.3、mysql.data.entity6.9.8
也可以用nuget的命令行加入:
install-package mysql.data.entity
二、新建相关类
1、新建 user 实体类
并定义实例的字段长度,不定义的话会出现specified key was too long;max key length is 767 bytes 的错误,这是因为string 类型直接映射到mysql 中的话是longtext,而mysql 支持最大长度为767 bytes.
public class user { public int id { get; set; } [stringlength(30)] public string username { get; set; } [maxlength(30)] public string password { get; set; } }
2、新建 mycontext 类
并说明用mysql进行实现 [dbconfigurationtype(typeof(mysqlefconfiguration))]
[dbconfigurationtype(typeof(mysqlefconfiguration))] public class mycontext : dbcontext { public mycontext() : base("name=mycontext")//web.config中connectionstring的名字 { } public dbset<user> users { get; set; } }
3、写测试代码
database.setinitializer(new dropcreatedatabaseifmodelchanges<mycontext>()); var context = new mycontext(); //插入一行值 context.users.add(new user { username = "ef6mysql" }); context.savechanges();
三、配置web.config
在<connectionstrings>中加入以下代码:
<add name="mycontext" connectionstring="data source=localhost;port=3306;initial catalog=mysql_ef;user id=root;password=root;" providername="mysql.data.mysqlclient" />
完整的web.config如下:
<?xml version="1.0" encoding="utf-8"?> <configuration> <configsections> <!-- for more information on entity framework configuration, visit http://go.microsoft.com/fwlink/?linkid=237468 --> <section name="entityframework" type="system.data.entity.internal.configfile.entityframeworksection, entityframework, version=6.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089" requirepermission="false" /> </configsections> <entityframework> <defaultconnectionfactory type="system.data.entity.infrastructure.sqlconnectionfactory , entityframework" /> <providers> <provider invariantname="system.data.sqlclient" type="system.data.entity.sqlserver.sqlproviderservices, entityframework.sqlserver" /> <provider invariantname="mysql.data.mysqlclient" type="mysql.data.mysqlclient.mysqlproviderservices, mysql.data.entity.ef6, version=6.9.8.0, culture=neutral, publickeytoken=c5687fc88969c44d"> </provider> </providers> </entityframework> <system.data> <dbproviderfactories> <remove invariant="mysql.data.mysqlclient" /> <add name="mysql data provider" invariant="mysql.data.mysqlclient" description=".net framework data provider for mysql" type="mysql.data.mysqlclient.mysqlclientfactory, mysql.data, version=6.9.8.0, culture=neutral, publickeytoken=c5687fc88969c44d" /> </dbproviderfactories> </system.data> <connectionstrings> <add name="mycontext" connectionstring="data source=localhost;port=3306;initial catalog=mysql_ef;user id=root;password=root;" providername="mysql.data.mysqlclient" /> </connectionstrings> </configuration>
最后,运行程序,完成数据库自动创建
常见问题
•出现错误提示: specified key was too long;max key length is 767 bytes
1)查看实体的字符串类型属性是否设置了长度
2)mycontext 类中是否声明为生成为mysql 数据类型的 [dbconfigurationtype(typeof(mysqlefconfiguration))]
•出现错误提示: model compatibility cannot be checked because the database does not contain model metadata
删除已生成的数据库后重新运行程序
•出现错误提示:序列不包含任何匹配元素
检查一下:
例如:1.
public class employee { [key] public int employeeid { get; set; } public string name { get; set; } [foreignkey("managerid")] public employee manager { get; set; } public int managerid { get; set; } }[foreignkey("managerid")] public employee manager { get; set; } public int managerid { get; set; }这个外键设置。
2.[column(typename="varchar(254)")] public string columnname { get; set; } 这样的定义,改成: [maxlength(254)] [column(typename="varchar")] public string columnname { get; set; }3.(以下代码未测试,因为我不是这样用的,在下篇文章中将进行测试)
modelbuilder.entity<category>() .haskey(c => c.idcategory ) .hasoptional(p => p.children) .withmany() .hasforeignkey(c => c.childrenid);
改成:
modelbuilder.entity<category>() .haskey(c => c.idcategory ) .hasmany(p => p.children) .withoptional() .hasforeignkey(c => c.childrenid);
.withmany()换成.withoptional()
以上所述是小编给大家介绍的在ef中使用mysql的方法及常见问题的全部叙述,希望对大家有所帮助