把旧系统迁移到.Net Core 2.0 日记（4) - 使用EF+Mysql

因为Mac 不能装SqlServer, 所以把数据库迁移到MySql,然后EntityFramework要改成Pomelo.EntityFrameworkCore.MySql

数据库迁移时,nvarchar 要改成varchar,编码选utf-8

在Startup.cs里

        public void ConfigureServices(IServiceCollection services)
        {

            //Use MySql-----------------------
            string connString = "Server=localhost;Port=3306;Database=test;User=root;Password=xxx;";
            services.AddDbContextPool<CRMContext>(tt => tt.UseMySql(connString));
        }

CRMContext类

    public class CRMContext: DbContext
    {
        public CRMContext(DbContextOptions<CRMContext> options): base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {   //手工指定table名
            //modelBuilder.Entity<User>().ToTable("User");

            //批量指定前缀CRM
            foreach (var entity in modelBuilder.Model.GetEntityTypes())
            {
                entity.Relational().TableName =
                    $"CRM{entity.ClrType.Name}";
            }

            //base.OnModelCreating(modelBuilder);
        }
        //https://stackoverflow.com/questions/4270794/why-is-my-dbcontext-dbset-null
        //必须设为property,如果是field,则会一直为null
        public DbSet<User> Users { get; set; }
        public DbSet<Department> Departments{ get; set; }
    }
}

在Controller里可以通过构造函数,依赖注入使用

    [Route("api/[controller]")]
    public class UserController : Controller
    {
        private readonly CRMContext _context;

        public UserController(CRMContext context)
        {
            _context = context;
        }

        // GET: api/values
        [HttpGet]
        public IEnumerable<string> Get()
        {
             return _context.Users.Select(tt=>tt.UserName).ToList();
        }
    }

 netCore的solution里多了一个WWWRoot的文件夹,里面都是放一些静态文件js,css,image等, 这个目录默认就是网站根目录,这样把动态文件和静态文件完全分离开了.

请参考这篇文章: ASP.NET Core使用静态文件、目录游览与MIME类型管理

 

如果要用多数据库迁移.可以这样

//Use MySql-----------------------
//services.AddDbContextPool<CRMContext>(tt => tt.UseMySql(Configuration.GetConnectionString("MySqlConnection")));

//-----Use SqlServer, 默认用offset分页(这个是sql2012版本之后才有的功能),之前的版本要改成RowNumber分页

 services.AddDbContextPool<CRMContext>( tt => tt.UseSqlServer(Configuration.GetConnectionString("SqlServerConnection"), opt=>opt.UseRowNumberForPaging()) );

 

把多个数据库连接写在appsetting.json

{
  "ConnectionStrings": {
    "SqlServerConnection": "Server=.\\sqlexpress;Database=test;User ID=sa;Password=xxxx;",
    "MySqlConnection": "Server=localhost;Port=3306;Database=test;User=root;Password=xxxx;"
  },
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Warning"
    }
  }
}

用dotnet ef的命令迁移数据库.

dotnet build
dotnet ef migrations remove
dotnet ef migrations add Initial
dotnet ef database update Initial
pause

 

如果出错, 未找到与命令“dotnet-ef”匹配的可执行文件,

               请参考这个http://www.cnblogs.com/dupeng0811/p/no-executable-found-matching-command-dotnet-ef.html

如果出错, 

The current CSharpHelper cannot scaffold literals of type 'Microsoft.EntityFrameworkCore.Metadata.Internal.DirectConstructorBinding'. Configure your services to use one that can.

解决方案：程序包管理控制台->Install-Package Microsoft.EntityFrameworkCore.Design

 如果出现这个错误: 

table 'TableName' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.  

要在DbContext文件里指定

            modelBuilder.Entity<Product>().HasOne(p => p.Category)
            .WithMany(c => c.Products).OnDelete(DeleteBehavior.Restrict)
            .HasForeignKey(p => p.CategoryId);

 

对比Data Annotation, Fluent API

Entity Framework Code First Fluent API - 配置关系

 

Entity Framework Code First Fluent API - 配置属性/类型

 

Entity Framework Code First Data Annotations