asp.net mvc entityframework sql server 迁移至 mysql方法以及遇到的问题

阿新

发布于 2020-05-12 13:12:24

1.3K0

发布于 2020-05-12 13:12:24

背景：

　　我原来的项目是asp.net mvc5 + entityframework 6.4 for sql server(localdb,sql server),现在需要把数据库切换成mysql，理论上entityframework是可以完全做到无缝切换，毕竟ORM的设计就是为了兼容不同的底层数据库。

步骤：

下载 mysql-connector-net-8.0.20.msi 并安装(其实没有什么必要，可以直接从nuget下安装)
nuget 安装 MySql.Data.EntityFramework，MySql.Data，注意：MySql.Data.Entity 和 MySql.Data.EntityFramework 应该只要一个就好了，我就安装 MySql.Data.EntityFramewor
配置webconfig 修改dbcontext 1 // 使用scaffold生成controller 时要先注销以下配置 2 [DbConfigurationType(typeof(MySqlEFConfiguration))] 3 public class StoreContext : DbContext 4 { 5 6 7 public StoreContext() 8 : base("name=DefaultConnection") { 9 //获取登录用户信息,tenantid 10 //QueryFilterManager.AllowPropertyFilter = true; 11 //var claimsidentity = (ClaimsIdentity)HttpContext.Current.User?.Identity; 12 //var tenantclaim = claimsidentity?.FindFirst("http://schemas.microsoft.com/identity/claims/tenantid"); 13 //var tenantid = Convert.ToInt32(tenantclaim?.Value); 14 //设置当对Work对象进行查询时默认添加过滤条件 15 //QueryDbSetFilterManager.Filter<Work>(q => q.Where(x => x.TenantId == tenantid)); 16 //this.Filter<Work>(q => q.Where(x => x.TenantId == tenantid)); 17 //设置当对Order对象进行查询时默认添加过滤条件 18 //QueryDbSetFilterManager.Filter<Order>(q => q.Where(x => x.TenantId == tenantid)); 19 //this.Filter<Order>(q => q.Where(x => x.TenantId == tenantid)); 20 //QueryDbSetFilterManager.InitilizeGlobalFilter(this); 21 22 } 23 }

 1 <connectionStrings>
 2     <remove name="DefaultConnection"/>
 3     <!--使用scaffold生成controller 时要先注销以下配置-->
 4     <add name="DefaultConnection" connectionString="server=localhost;port=3306;database=webapp;uid=root;password=root;Persist Security Info=True" providerName="MySql.Data.MySqlClient" />
 5     <!--<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB; Initial Catalog=webappv4;Integrated Security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />-->
 6   </connectionStrings>
 7 <!--<entityFramework >
 8     <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
 9     <providers>
10       <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
11     </providers>
12   </entityFramework>-->
13   <!--使用scaffold生成controller 时要先注销以下配置-->
14   <entityFramework codeConfigurationType="MySql.Data.EntityFramework.MySqlEFConfiguration, MySql.Data.EntityFramework">
15     <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
16       <parameters>
17         <parameter value="mssqllocaldb" />
18       </parameters>
19     </defaultConnectionFactory>
20     <providers>
21       <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
22       <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.EntityFramework, Version=8.0.20.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
23 
24     </providers>
25   </entityFramework>
26   <system.data>
27     <DbProviderFactories>
28       <remove invariant="MySql.Data.MySqlClient" />
29       <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=8.0.20.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
30     </DbProviderFactories>
31   </system.data>

执行迁移命令，重新创建数据库表名

1 //执行命令前先删除项目Migrations下的文件
2 enable-migrations 
3 add-migration md_init

修改 dbcontext

 1 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 2     {
 3       //Oracle 表所有者，（SQL 改成 dbo(默认)，也可删除此设置）
 4       modelBuilder.HasDefaultSchema(string.Empty);
 5       //默认把string类型转换成nvarchar,原本nlogtext
 6       modelBuilder.Properties<string>().Configure(c => c.IsUnicode(false));
 7       base.OnModelCreating(modelBuilder);
 8      
 9 
10     }