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

时间：2023-03-08 17:45:14

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

把旧系统迁移到.Net Core 2.0 日记（4) - 使用EF+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://*.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

相关文章