MVC4,MVC3,VS2012+ entity framework Migration from Sqlserver

在开发的初期个人认为因VS与Sqlserver的配合很默契，即可以方便的实现Code First，又可以使用SqlServer Manager很漂亮的进行建模与变更，也许是个人的使用习惯MS的界面做的很好，乎开源的产品美感上都追不上商用版。 所以个人比较喜欢的开发流程是初次开发以

在开发的初期个人认为因VS与Sqlserver的配合很默契，即可以方便的实现Code First，又可以使用SqlServer Manager很漂亮的进行建模与变更，也许是个人的使用习惯MS的界面做的很好，似乎开源的产品美感上都追不上商用版。

所以个人比较喜欢的开发流程是初次开发以SqlServer Manager进行数据库建模，初步建构业务逻辑；

其次使用Vs的Entity FrameWork 快速的建立起ROM；

最后当开发进行到一定程度时再进行数据库迁移，把SqlServer 转换为MySql。

一、环境：Win 8.1 + Vs2012 + MysqlServer 5.7 + WorkBench 6.0 CE 

注意事项：

1. 在某种怪异的情况下Win 8.1 + vs2012 +mysql 5.7 此种搭配是无法使用的，在StackOverFlow中也有很多码友们，包括身边的同事也都有遇到过类似问题，

现象是在Win8.1下安装mysql5.7 时，mysql for vs2012 与 mysql connection net 5.7无法正常安装，即使卸载Mysql也无用，自然在Vs2012中添加Ado.net entity model时未有Mysql conncetion集成到Vs中，但往往在Vs的SqlServer Explore中你可以添加Mysql，但不要试图填写conncetion string，因为那样会让您老人家Vs崩溃。

原因可能是Mysql 在配合MS的新家伙Win 8.1 系统时有Bug存在。

解决办法：此种情况并非每次都会出现，但出现以后很难解决，如果哪位码友知道请赐教；重装系统或者自己运气好，软件自动修复了。

2.使用WorkBench可以很方便的进行数据库迁移，很人性化，自动把SqlServer转为MySql，但某些字段的转换会失败，需要手动修复了。

3.在Vs连接Mysql时不要使用Root，尽量配置以项目为区分的私有帐户，这样可以最大化的实现自定义，配置为允许远程连接的即可。

4.在VS2012创建MySql的Ado.net entity framework时生成的实体全是小写，会使原有建立在SqlServer实体化基础上的代码出现问题，木有关系，把原来的Copy过来即可，Simple.

 不过还有一个办法就是使用DbLinq（https://code.google.com/p/dblinq2007/）来替代EF，但不建议使用在正规场合，学习可以，因为其最终版本是2007年，此后再无更新。

二、流程：

　　数据库迁移：直接使用WorkBench进行数据库从SqlServer 迁移到MySql。

　　代码迁移：　　

　　1.  确保成功安装Connector NET 6.7.4 与 MySQL for Visual Studio 1.0.2

　　2.  创建MVC3 Web Application，添加引用从NuGet或者MySQL for Visual Studio 1.0.2中获取最新版本的三个DLL：MySql.Data，MySql.Data.Entity，MySql.Web。

　　3.  如果以上步骤OK的话，当向项目中添加Ado.net entity framework时，New Connection 就可以看到Mysql database选项，否则你就倒霉了，可以参考环境->注意事项；

　　4.  修改Web.Config，需要增加及修改的示例代码如下：

<span><span>connectionStrings</span><span>></span>
    <span><span>add </span><span>name</span><span>="MySQLConnString"</span><span>
         connectionString</span><span>="Server=192.168.29.157;Port=3306;Database=**;Uid=**;Pwd=**;"</span><span>
         providerName</span><span>="MySql.Data.MySqlClient"</span> <span>/></span>
    <span><span>add </span><span>name</span><span>="newsoftgatewayEntities"</span><span>
         connectionString</span><span>="metadata=res://*/NewsoftGateWay.csdl|res://*/NewsoftGateWay.ssdl|res://*/NewsoftGateWay.msl;provider=MySql.Data.MySqlClient;provider connection string="server=192.168.29.157;user id=**;password=**;database=newsoftgateway""</span><span>
         providerName</span><span>="System.Data.EntityClient"</span> <span>/></span>
  <span></span><span>connectionStrings</span><span>></span></span></span></span>

  <span><span>system.web</span><span>></span>
<span><span>membership </span><span>defaultProvider</span><span>="MySqlMembershipProvider"</span><span>></span>
      <span><span>providers</span><span>></span>
        <span><span>clear </span><span>/></span>
        <span><span>add </span><span>name</span><span>="MySqlMembershipProvider"</span><span>
             type</span><span>="MySql.Web.Security.MySQLMembershipProvider,MySql.Web,Version=6.7.4,Culture=neutral,PublicKeyToken=c5687fc88969c44d"</span><span>
             connectionStringName</span><span>="MySQLConnString"</span><span> enablePasswordRetrieval</span><span>="false"</span><span> enablePasswordReset</span><span>="true"</span><span>
             requiresQuestionAndAnswer</span><span>="false"</span><span> requiresUniqueEmail</span><span>="true"</span><span> passwordFormat</span><span>="Hashed"</span><span>
             maxInvalidPasswordAttempts</span><span>="5"</span><span> minRequiredPasswordLength</span><span>="6"</span><span> minRequiredNonalphanumericCharacters</span><span>="0"</span><span>
             passwordAttemptWindow</span><span>="10"</span><span> applicationName</span><span>="/"</span><span> autogenerateschema</span><span>="true"</span> <span>/></span>
      <span></span><span>providers</span><span>></span>
    <span></span><span>membership</span><span>></span>
    <span><span>profile</span><span>></span>
      <span><span>providers</span><span>></span>
        <span><span>clear </span><span>/></span>
        <span><span>add </span><span>name</span><span>="MySQLProfileProvider"</span><span>
             type</span><span>="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.7.4.0,Culture=neutral,PublicKeyToken=c5687fc88969c44d"</span><span>
             connectionStringName</span><span>="MySQLConnString"</span><span> applicationName</span><span>="/"</span> <span>/></span>
      <span></span><span>providers</span><span>></span>
    <span></span><span>profile</span><span>></span>
    <span><span>roleManager </span><span>enabled</span><span>="true"</span><span> defaultProvider</span><span>="MySQLRoleProvider"</span><span>></span>
      <span><span>providers</span><span>></span>
        <span><span>clear </span><span>/></span>
        <span><span>add </span><span>name</span><span>="MySQLRoleProvider"</span><span>
             type</span><span>="MySql.Web.Security.MySQLRoleProvider, MySql.Web,Version=6.7.4.0,Culture=neutral,PublicKeyToken=c5687fc88969c44d"</span><span>
             connectionStringName</span><span>="MySQLConnString"</span><span> applicationName</span><span>="/"</span> <span>/></span>
      <span></span><span>providers</span><span>></span>
    <span></span><span>roleManager</span><span>></span>
    <span><span>sessionState </span><span>mode</span><span>="InProc"</span><span> customProvider</span><span>="MysqlSessionProvider"</span><span>></span>
      <span><span>providers</span><span>></span>
        <span><span>add </span><span>name</span><span>="MysqlSessionProvider"</span><span>
             type</span><span>="MySql.Web.SessionState.MySqlSessionStateStore, MySql.Web, Version=6.7.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"</span><span>
             connectionStringName</span><span>="MySQLConnString"</span> <span>/></span>
      <span></span><span>providers</span><span>></span>
    <span></span><span>sessionState</span><span>></span>
  <span></span><span>system.web</span><span>></span>
<span><span>system.data</span><span>></span>
    <span><span>DbProviderFactories</span><span>></span>
      <span><span>remove </span><span>invariant</span><span>="MySql.Data.MySqlClient"</span> <span>/></span>
      <span><span>add </span><span>name</span><span>="MySQL Data Provider"</span><span> invariant</span><span>="MySql.Data.MySqlClient"</span><span>
           description</span><span>=".Net Framework Data Provider for MySQL"</span><span>
           type</span><span>="MySql.Data.MySqlClient.MySqlClientFactory,MySql.Data"</span> <span>/></span>
    <span></span><span>DbProviderFactories</span><span>></span>
  <span></span><span>system.data</span><span>></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

　　5.  到目前为止，正常情况下或者使用CodeFirst或者使用Migration，应该都可以实现MVC3 在MySql下登录和注册模块的功能。：）恭喜已经接近成功了。

　　6.  之所以不直接使用MVC4是因为MySql 对MVC4的支持并不是太好，时不时会出现很多问题；所以建议使用MVC3按以上步骤调试OK后再往MVC4上转换；所谓转换只是用MVC3的相关权限功能模块来替换掉MVC4的，主要包括AccountController，AccountModels，相关View，Global.asax。

<span> 1</span>  <span>protected</span> <span>void</span><span> Application_Start()
</span><span> 2</span> <span>        {
</span><span> 3</span> <span>            AreaRegistration.RegisterAllAreas();
</span><span> 4</span> 
<span> 5</span> <span>            WebApiConfig.Register(GlobalConfiguration.Configuration);
</span><span> 6</span> <span>            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
</span><span> 7</span> <span>            RouteConfig.RegisterRoutes(RouteTable.Routes);
</span><span> 8</span>             BundleTable.EnableOptimizations = <span>true</span><span>;
</span><span> 9</span> <span>            BundleConfig.RegisterBundles(BundleTable.Bundles);
</span><span>10</span> <span>            AuthConfig.RegisterAuth();
</span><span>11</span>             BCRMonitor.RegisterGlobalMonitor(Server.MapPath(<span>string</span>.Format(<span>"</span><span>~/photos/</span><span>"</span><span>)));
</span><span>12</span> 
<span>13</span>             <span>//</span><span>AreaRegistration.RegisterAllAreas();
</span><span>14</span> 
<span>15</span>             <span>//</span><span> Use LocalDB for Entity Framework by default
</span><span>16</span>            <span>//</span><span> Database.DefaultConnectionFactory = new SqlConnectionFactory(@"Data Source=(localdb)\v11.0; Integrated Security=True; MultipleActiveResultSets=True");
</span><span>17</span> 
<span>18</span>            <span>//</span><span> RegisterGlobalFilters(GlobalFilters.Filters);
</span><span>19</span>             <span>//</span><span>RegisterRoutes(RouteTable.Routes);</span>
<span>20</span>         }

　　7. 可能在上一步骤进行过程中会出现一些问题，耐心调试就OK了。

　　注意事项：

　　　　a.　　mysql出现异常“ Every derived table must have its own alias” 此种问题在linq to mysql 的过程中也可能会遇到，只是linq to sql本身就是MS为Sqlserver开发的，当涉及到多表综合查询时，对mysql的支持就不太好了，如语句 return GetAll("").Where(x => x.Id == id).Take(1).SingleOrDefault(); 就会引起此类问题，可以换种linq写法规避。

　　 　　b.　　在使用NuGet引用MySql.Data.Entity时注意其版本需要和packages.config，web.config，MySQL for Visual Studio 1.0.2 等中一致，否则也会提示错误：

System.IO.FileNotFoundException: 未能加载文件或程序集"MySql.Data, Version=6.1.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"或它的某一个依赖项。系统找不到指定的文件。

　　　　c.　　System.ArgumentException: 不支持关键字: “metadata”。 错误原因可能是connection string provider 未修改成 providerName="MySql.Data.MySqlClient"