（三）.netcore+vue 创建数据库表——EFCodeFirst

一、项目结构分层

 

 

 

 

二、实体类

SysRole——角色表
SysUser——用户表
SysMenu——菜单表
SysAction——接口表，关联菜单表
SysUserRole——用户角色表
SysRoleMenu——角色菜单授权表
先只做到页面授权级，如果做到按钮级，就在菜单表里加个字段区别是页面还是按钮

 

    /// <summary>
    /// 角色表
    /// </summary>
    public class SysRole 
    {
        public SysRole()
        {            
        }
        
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        /// <summary>
        /// 角色名
        /// </summary>
        [MaxLength(50)]
        [Column(TypeName = "nvarchar(50)")]
        public string RoleName { get; set; }

        
        /// <summary>
        ///描述
        /// </summary>
        [MaxLength(100)]
        [Column(TypeName = "nvarchar(100)")]
        public string Description { get; set; }

        /// <summary>
        ///排序
        /// </summary>
        public int OrderSort { get; set; }

        /// <summary>
        /// 是否启用
        /// </summary>
        public bool Enabled { get; set; }

        /// <summary>
        ///是否删除
        /// </summary>
        public bool? IsDeleted { get; set; }

        /// <summary>
        /// 创建ID
        /// </summary>
        public int? CreateId { get; set; }

        /// <summary>
        /// 创建者
        /// </summary>
        [MaxLength(50)]
        [Column(TypeName = "nvarchar(50)")]
        public string CreateBy { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; } = DateTime.Now;

        /// <summary>
        /// 修改ID
        /// </summary>
        public int? ModifyId { get; set; }
        /// <summary>
        /// 修改者
        /// </summary>
        public string ModifyBy { get; set; }

        /// <summary>
        /// 修改时间
        /// </summary>
        public DateTime? ModifyTime { get; set; } = DateTime.Now;

    }

    /// <summary>
    /// 用户表
    /// </summary>
    public class SysUser
    {       
        public SysUser() { }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
                
        /// <summary>
        /// 登录账号
        /// </summary>
        [MaxLength(200)]
        [Column(TypeName = "nvarchar(50)")]
        public string LoginName { get; set; }

        /// <summary>
        /// 登录密码
        /// </summary>
        [MaxLength(200)]
        [Column(TypeName = "nvarchar(50)")]
        public string LoginPWD { get; set; }

        /// <summary>
        /// 真实姓名
        /// </summary>
        [MaxLength(200)]
        [Column(TypeName = "nvarchar(50)")]
        public string RealName { get; set; }       

        /// <summary>
        /// 备注
        /// </summary>
        [MaxLength(2000)]
        [Column(TypeName = "nvarchar(2000)")]
        public string Remark { get; set; }


        // 性别
        public int? Sex { get; set; } = 0;

        // 年龄
        public int? Age { get; set; }

        // 生日
        public DateTime? Birth { get; set; } = DateTime.Now;

        // 地址
        [MaxLength(200)]
        [Column(TypeName = "nvarchar(100)")]
        public string Addr { get; set; }

        /// <summary>
        /// 状态
        /// </summary>
        public int Status { get; set; }
        //是否删除
        public bool? IsDelete { get; set; }

        /// <summary>
        /// 创建ID
        /// </summary>
        public int? CreateId { get; set; }

        /// <summary>
        /// 创建者
        /// </summary>
        [MaxLength(50)]
        [Column(TypeName = "nvarchar(50)")]
        public string CreateBy { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; } = DateTime.Now;

        /// <summary>
        /// 修改ID
        /// </summary>
        public int? ModifyId { get; set; }
        /// <summary>
        /// 修改者
        /// </summary>
        public string ModifyBy { get; set; }

        /// <summary>
        /// 修改时间
        /// </summary>
        public DateTime? ModifyTime { get; set; } = DateTime.Now;


    }

/// <summary>
    /// 菜单表
    /// </summary>
    public class SysMenu
    {
        public SysMenu()
        {           
        }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        /// <summary>
        /// 上一级菜单（0表示上一级无菜单）
        /// </summary>
        public int Pid { get; set; }

      
        /// <summary>
        /// 菜单显示名
        /// </summary>
        [MaxLength(50)]
        [Column(TypeName = "nvarchar(50)")]
        public string Name { get; set; }

      
        /// <summary>
        /// 排序
        /// </summary>
        public int OrderSort { get; set; }

        /// <summary>
        /// 菜单图标
        /// </summary>
        [MaxLength(100)]
        [Column(TypeName = "nvarchar(100)")]
        public string Icon { get; set; }


        /// <summary>
        /// 菜单描述    
        /// </summary>
        [MaxLength(100)]
        [Column(TypeName = "nvarchar(200)")]
        public string Description { get; set; }


        /// <summary>
        /// 启用状态
        /// </summary>
        public bool Enabled { get; set; }

        /// <summary>
        ///是否删除
        /// </summary>
        public bool? IsDeleted { get; set; }

        /// <summary>
        /// 创建ID
        /// </summary>
        public int? CreateId { get; set; }
        /// <summary>
        /// 创建者
        /// </summary>
        [MaxLength(50)]
        [Column(TypeName = "nvarchar(50)")]
        public string CreateBy { get; set; }
        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; } = DateTime.Now;
        /// <summary>
        /// 修改ID
        /// </summary>
        public int? ModifyId { get; set; }
        /// <summary>
        /// 修改者
        /// </summary>
        [MaxLength(50)]
        [Column(TypeName = "nvarchar(50)")]
        public string ModifyBy { get; set; }

        /// <summary>
        /// 修改时间
        /// </summary>
        public DateTime? ModifyTime { get; set; } = DateTime.Now;

        [NotMapped]
        public virtual ICollection<SysAction> SysActions { get; set; }
    }

/// <summary>
    /// API接口表
    /// </summary>
    public class SysAction
    {
        public SysAction()
        {            
        }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        /// <summary>
        /// 菜单ID
        /// </summary>
        public int MenuId { get; set; }
        /// <summary>
        /// 名称
        /// </summary>
        [MaxLength(50)]
        [Column(TypeName = "nvarchar(50)")]
        public string Name { get; set; }

        /// <summary>
        /// API地址
        /// </summary>
        [MaxLength(100)]
        [Column(TypeName = "nvarchar(100)")]
        public string LinkUrl { get; set; }

      
        /// <summary>
        /// 控制器名称
        /// </summary>
        [MaxLength(2000)]
        [Column(TypeName = "nvarchar(100)")]
        public string Controller { get; set; }

        /// <summary>
        /// Action名称
        /// </summary>
        [MaxLength(2000)]
        [Column(TypeName = "nvarchar(100)")]
        public string Action { get; set; }
       
        /// <summary>
        /// 菜单编号
        /// </summary>
        [MaxLength(10)]
        [Column(TypeName = "nvarchar(10)")]
        public string Code { get; set; }
        /// <summary>
        /// 排序
        /// </summary>
        public int OrderSort { get; set; }
        /// <summary>
        /// /描述
        /// </summary>
        [MaxLength(100)]
        [Column(TypeName = "nvarchar(200)")]
        public string Description { get; set; }
     
        /// <summary>
        /// 是否激活
        /// </summary>
        public bool Enabled { get; set; }

        /// <summary>
        ///是否删除
        /// </summary>
        public bool? IsDeleted { get; set; }

        /// <summary>
        /// 创建ID
        /// </summary>
        public int? CreateId { get; set; }
        /// <summary>
        /// 创建者
        /// </summary>
        [MaxLength(50)]
        [Column(TypeName = "nvarchar(50)")]
        public string CreateBy { get; set; }
        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; } = DateTime.Now;
        /// <summary>
        /// 修改ID
        /// </summary>
        public int? ModifyId { get; set; }
        /// <summary>
        /// 修改者
        /// </summary>
        [MaxLength(100)]
        [Column(TypeName = "nvarchar(50)")]
        public string ModifyBy { get; set; }
        /// <summary>
        /// 修改时间
        /// </summary>
        public DateTime? ModifyTime { get; set; } = DateTime.Now;

        [NotMapped]
        public virtual SysMenu SysMenu { get; set; }

    }

/// <summary>
    /// 用户和角色关系表
    /// </summary>
    public class SysUserRole
    {
        public SysUserRole() { }

        
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        /// <summary>
        /// 用户ID
        /// </summary>
        //[ForeignKey("Target")]
        public int UserId { get; set; }
        //public sysUserInfo Target { get; set; }

        /// <summary>
        /// 角色ID
        /// </summary>   
        public int RoleId { get; set; }
        //[ForeignKey("RoleId ")]
        //public Role Target2 { get; set; }

        /// <summary>
        ///是否删除
        /// </summary>
        public bool? IsDeleted { get; set; }

        /// <summary>
        /// 创建ID
        /// </summary>
        public int? CreateId { get; set; }
        /// <summary>
        /// 创建者
        /// </summary>
        [MaxLength(50)]
        [Column(TypeName = "nvarchar(50)")]
        public string CreateBy { get; set; }
        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; }
        /// <summary>
        /// 修改ID
        /// </summary>
        public int? ModifyId { get; set; }

        /// <summary>
        /// 修改者
        /// </summary>
        [MaxLength(50)]
        [Column(TypeName = "nvarchar(50)")]
        public string ModifyBy { get; set; }

        /// <summary>
        /// 修改时间
        /// </summary>
        public DateTime? ModifyTime { get; set; }

       

    


    }

/// <summary>
    /// 角色和菜单关联表
    /// </summary>
    public class SysRoleMenu
    {
        public SysRoleMenu()
        {         

        }
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        /// <summary>
        /// 角色ID
        /// </summary>
        public int RoleId { get; set; }
        /// <summary>
        /// 菜单ID
        /// </summary>
        public int MenuId { get; set; }

        
        /// <summary>
        ///是否删除
        /// </summary>
        public bool? IsDeleted { get; set; }

        /// <summary>
        /// 创建ID
        /// </summary>
        public int? CreateId { get; set; }
        /// <summary>
        /// 创建者
        /// </summary>
        [MaxLength(50)]
        [Column(TypeName = "nvarchar(50)")]
        public string CreateBy { get; set; }
        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; } = DateTime.Now;
        /// <summary>
        /// 修改ID
        /// </summary>
        public int? ModifyId { get; set; }
        /// <summary>
        /// 修改者
        /// </summary>
        [MaxLength(50)]
        [Column(TypeName = "nvarchar(50)")]
        public string ModifyBy { get; set; }
        /// <summary>
        /// 修改时间
        /// </summary>
        public DateTime? ModifyTime { get; set; } = DateTime.Now;
        
    }

 

三、引入 EFCore

安装EFCore的以下3个包：Microsoft.EntityFrameworkCore、Microsoft.EntityFrameworkCore.SqlServer 、Microsoft.EntityFrameworkCore.Tools

前两个安装到Entity项目，后一个安装到APICore

  

 编写 DbContext类,继承DbContext，把model对象加进上下文对象里

 

 

 代码如下（为了学习如何CodeFirst方式配置主外键关系，这里对菜单表和接口表增加了主外键关系）：

 public class BaseCoreContext : DbContext
    {
 
        public BaseCoreContext(DbContextOptions<BaseCoreContext> options)
            : base(options)
        {
        }
       
        public DbSet<SysRole> SysRole { get; set; }
        public DbSet<SysUser> SysUser { get; set; }
        public DbSet<SysMenu> SysMenu { get; set; }
        public DbSet<SysAction> SysAction { get; set; }
        public DbSet<SysUserRole> SysUserRole { get; set; }
        public DbSet<SysRoleMenu> SysRoleMenu { get; set; }
       
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {            
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            //主外键关系
            modelBuilder.Entity<SysAction>(entity => {
                entity.HasOne(t => t.SysMenu)
                .WithMany(t => t.SysActions)
                .HasForeignKey(t => t.MenuId);
            });
        }

    }

 

 

四、生成表结构

 1、appsettings.json配置数据库连接信息

"AppSettings": {
    "SqlServer": {
      "Enabled": true,
      "SqlServerConnection": "server=.;database=EmptyTemplate;uid=sa;pwd=sa123!@#;",
      "ProviderName": "System.Data.SqlClient"
    }
  }

2、Startup的ConfigureServices方法里注入EFCore上下文对象，并把连接字符串给上下文对象

#region EF
            if (Configuration["AppSettings:SqlServer:Enabled"].ObjToBool())
            {
                var ConnectionString = Configuration["AppSettings:SqlServer:SqlServerConnection"];
                services.AddDbContextPool<BaseCoreContext>(options =>
                    options.UseSqlServer(ConnectionString));
            }            
            #endregion

3、打开nuget 程序包管理控制台，将默认项目设置为 EmptyTemplate.Entity

控制台里输入命令：Add-Migration V0 

没有出错，则表示迁移版本已创建成功

 

 然后输入命令：update-database V0

数据库和表已创建好。