.NetCore中EFCore for MySql整理(三)之Pomelo.EntityFrameworkCore.MySql

一、Pomelo.EntityFrameworkCore.MySql简介

Git源代码地址：https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql

注：这是第三方的 EF Core 的ORM框架，支持Synac I/O访问操作MySql数据，不是MySql官方的数据驱动。

1.本框架支持Code First 和Server First

问题：Server First 生成代码没有附带主外键关联。

二、Server First方式使用示例

1.创建.Net Core 控制台项目

2.安装Pomelo.EntityFrameworkCore.MySql

使用命令：

Install-Package Pomelo.EntityFrameworkCore.MySql

或者在包管理工具中搜索安装

创建数据库角色-菜单命令：

-- 创建角色&菜单简单逻辑表
create database RoleMenu;
grant all on *.* to 'userone'@'localhost' identified by '123';

use RoleMenu;
-- drop table Role_Menu,Role,Menu;
-- 创建角色表
create table Role(
RoleID int not null  auto_increment,
Name nvarchar(50) not null,
SortValue int not null,
primary key(RoleID)
);

-- 创建菜单表 
create table Menu(
MenuID int not null auto_increment,
Name nvarchar(50) not null,
Title nvarchar(100) not null,
LinkUrl varchar(200) null,
Icon varchar(100) null,
primary key(MenuID)
);

-- 创建角色-菜单表 
create table Role_Menu(
ID int not null auto_increment,
RoleID int not null,
MenuID int not null,
primary key(ID),
foreign key(RoleID) references Role(RoleID)
on delete cascade,
foreign key(MenuID) references Menu(MenuID)
on delete no action
);

-- 添加测试数据 
insert Role(Name,SortValue) values('系统管理员',1);
insert Role(Name,SortValue) values('服务中心',2);

-- 添加菜单数据 
insert Menu(Name,Title) values('个人信息','个人信息管理');
insert Menu(Name,Title) values('修改密码','修改登录密码&二级密码');

-- 添加关联 
insert Role_Menu(RoleID,MenuID) values(1,1);
insert Role_Menu(RoleID,MenuID) values(1,2);
insert Role_Menu(RoleID,MenuID) values(2,1);
insert Role_Menu(RoleID,MenuID) values(2,2);

View Code

使用PM命令，链接数据库生成model层和上下文：

Scaffold-DbContext "Server=127.0.0.1;port=3306;Database=Md5Data;uid=xxx;pwd=xxx;Character Set=utf8;" MySql.Data.EntityFrameworkCore -OutputDir models2

生成代码结果：

    public partial class Role
    {
        public int RoleId { get; set; }
        public string Name { get; set; }
        public int SortValue { get; set; }
    }
    public partial class Menu
    {
        public int MenuId { get; set; }
        public string Name { get; set; }
        public string Title { get; set; }
        public string LinkUrl { get; set; }
        public string Icon { get; set; }
    }
    public partial class RoleMenu
    {
        public int Id { get; set; }
        public int RoleId { get; set; }
        public int MenuId { get; set; }
    }
 public partial class RoleMenuContext : DbContext
    {
        public virtual DbSet<Menu> Menu { get; set; }
        public virtual DbSet<Role> Role { get; set; }
        public virtual DbSet<RoleMenu> RoleMenu { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
                optionsBuilder.UseMySql("Server=127.0.0.1;port=3306;Database=RoleMenu;uid=userone;pwd=123;Character Set=utf8;");
            }
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Menu>(entity =>
            {
                entity.Property(e => e.MenuId)
                    .HasColumnName("MenuID")
                    .HasColumnType("int(11)");

                entity.Property(e => e.Icon).HasMaxLength(100);

                entity.Property(e => e.LinkUrl).HasMaxLength(200);

                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(50);

                entity.Property(e => e.Title)
                    .IsRequired()
                    .HasMaxLength(100);
            });

            modelBuilder.Entity<Role>(entity =>
            {
                entity.Property(e => e.RoleId)
                    .HasColumnName("RoleID")
                    .HasColumnType("int(11)");

                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(50);

                entity.Property(e => e.SortValue).HasColumnType("int(11)");
            });

            modelBuilder.Entity<RoleMenu>(entity =>
            {
                entity.ToTable("Role_Menu");

                entity.HasIndex(e => e.MenuId)
                    .HasName("MenuID");

                entity.HasIndex(e => e.RoleId)
                    .HasName("RoleID");

                entity.Property(e => e.Id)
                    .HasColumnName("ID")
                    .HasColumnType("int(11)");

                entity.Property(e => e.MenuId)
                    .HasColumnName("MenuID")
                    .HasColumnType("int(11)");

                entity.Property(e => e.RoleId)
                    .HasColumnName("RoleID")
                    .HasColumnType("int(11)");
            });
        }
    }