EF Core创建数据库

EF Core创建数据库

代码优先

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

方式一

EF 上下文

using Bunny.EFCore.DbFirst.Models;
using Microsoft.EntityFrameworkCore;

namespace Bunny.EFCore.DbFirst.Context;

public sealed class EfCoreContext : DbContext
{
    /// <summary>
    ///     连接字符串
    /// </summary>
    private readonly string ConnectionString;

    public EfCoreContext(DbContextOptions<EfCoreContext> options) : base(options)
    {
    }

    public EfCoreContext(string connectionString)
    {
        ConnectionString = connectionString;
    }

    public DbSet<UserEntity> UserEntities { get; set; }

    public DbSet<RoleEntity> RoleEntities { get; set; }

    public DbSet<UserRoleMapEntity> UserRoleMapEntities { get; set; }

    public DbSet<MenuEntity> MenuEntities { get; set; }

    public DbSet<RoleMenuMapEntity> RoleMenuMapEntities { get; set; }

    public DbSet<SystemLog> SystemLogs { get; set; }

    /// <summary>
    ///     配置DbContext需要的参数---例如  数据库连接字符串
    /// </summary>
    /// <param name="optionsBuilder"></param>
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(ConnectionString);
    }

    /// <summary>
    ///     配置数据库表和实体之间的映射关系
    /// </summary>
    /// <param name="modelBuilder"></param>
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<UserEntity>(entity => entity.ToTable("UserEntity").HasKey(u => u.UserId));
        modelBuilder.Entity<MenuEntity>(entity => entity.ToTable("MenuEntity").HasKey(u => u.Id));
        modelBuilder.Entity<RoleEntity>(entity => entity.ToTable("RoleEntity").HasKey(u => u.RoleId));
        modelBuilder.Entity<RoleMenuMapEntity>(entity => entity.ToTable("RoleMenuMapEntity").HasKey(u => u.Id));
        modelBuilder.Entity<UserRoleMapEntity>(entity => entity.ToTable("UserRoleMapEntity").HasKey(u => u.Id));
        modelBuilder.Entity<SystemLog>(entity => entity.ToTable("SystemLog").HasKey(u => u.Id));
    }
}

启动类

直接在启动类上写数据库连接，创建数据库。

using Bunny.EFCore.DbFirst.Context;

namespace Bunny.EFCore.DbFirst;

public class Program
{
    public static void Main(string[] args)
    {
        var builder = WebApplication.CreateBuilder(args);

        var serviceCollection = builder.Services;
        serviceCollection.AddControllers();
        serviceCollection.AddEndpointsApiExplorer();
        serviceCollection.AddSwaggerGen();

        var app = builder.Build();
   
        var str =
            "Data Source=192.168.3.98;Initial Catalog=bunny_test;Persist Security Info=True;User ID=sa;Password=abc1234.;Pooling=False;Encrypt=True;Trust Server Certificate=True";

        using (var context = new EfCoreContext(str))
        {
            // 删除数据库
            context.Database.EnsureDeleted();
            // 创建数据库
            context.Database.EnsureCreated();
        }

        if (app.Environment.IsDevelopment())
        {
            app.UseSwagger();
            app.UseSwaggerUI();
        }

        app.UseAuthorization();
        app.MapControllers();
        app.Run();
    }
}

方式二

在配置文件读取ConnectionStrings，因为C#有自带的读取配置文件，读取配置文件中ConnectionStrings之后找到自己的字符串，其中MSSQLServer这个名字可以自定义。

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "MSSQLServer": "Data Source=192.168.3.98;Initial Catalog=bunny_test;Persist Security Info=True;User ID=sa;Password=abc1234.;Pooling=False;Encrypt=True;Trust Server Certificate=True"
  }
}

EFContext上下文对象

需要删除OnConfiguring否则连接会报错，没有初始化…

using Bunny.EFCore.DbFirst.Models;
using Microsoft.EntityFrameworkCore;

namespace Bunny.EFCore.DbFirst.Context;

public sealed class EfCoreContext : DbContext
{
    /// <summary>
    ///     连接字符串
    /// </summary>
    private readonly string ConnectionString;

    public EfCoreContext(DbContextOptions<EfCoreContext> options) : base(options)
    {
    }

    public EfCoreContext(string connectionString)
    {
        ConnectionString = connectionString;
    }

    public DbSet<UserEntity> UserEntities { get; set; }

    public DbSet<RoleEntity> RoleEntities { get; set; }

    public DbSet<UserRoleMapEntity> UserRoleMapEntities { get; set; }

    public DbSet<MenuEntity> MenuEntities { get; set; }

    public DbSet<RoleMenuMapEntity> RoleMenuMapEntities { get; set; }

    public DbSet<SystemLog> SystemLogs { get; set; }

    /// <summary>
    ///     配置数据库表和实体之间的映射关系
    /// </summary>
    /// <param name="modelBuilder"></param>
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<UserEntity>(entity => entity.ToTable("UserEntity").HasKey(u => u.UserId));
        modelBuilder.Entity<MenuEntity>(entity => entity.ToTable("MenuEntity").HasKey(u => u.Id));
        modelBuilder.Entity<RoleEntity>(entity => entity.ToTable("RoleEntity").HasKey(u => u.RoleId));
        modelBuilder.Entity<RoleMenuMapEntity>(entity => entity.ToTable("RoleMenuMapEntity").HasKey(u => u.Id));
        modelBuilder.Entity<UserRoleMapEntity>(entity => entity.ToTable("UserRoleMapEntity").HasKey(u => u.Id));
        modelBuilder.Entity<SystemLog>(entity => entity.ToTable("SystemLog").HasKey(u => u.Id));
    }
}

连接数据库

using Bunny.EFCore.DbFirst.Context;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;

namespace Bunny.EFCore.DbFirst
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var builder = WebApplication.CreateBuilder(args);

            var serviceCollection = builder.Services;
            serviceCollection.AddControllers();
            serviceCollection.AddEndpointsApiExplorer();
            serviceCollection.AddSwaggerGen();

            // 注册DbContext服务
            serviceCollection.AddDbContext<EfCoreContext>(options =>
            {
                var connectionString = builder.Configuration.GetConnectionString("MSSQLServer");
                options.UseSqlServer(connectionString);
            });

            var app = builder.Build();

            // 创建数据库
            using (var scope = app.Services.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<EfCoreContext>();
                dbContext.Database.EnsureDeleted();
                dbContext.Database.EnsureCreated();
            }

            if (app.Environment.IsDevelopment())
            {
                app.UseSwagger();
                app.UseSwaggerUI();
            }

            app.UseAuthorization();
            app.MapControllers();
            app.Run();
        }
    }
}

方式三

JSON文件修改

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "MSSQLServer": "Data Source=192.168.3.98;Initial Catalog=bunny_test;Persist Security Info=True;User ID=sa;Password=abc1234.;Pooling=False;Encrypt=True;Trust Server Certificate=True"
}

自定义字符串连接，上下文对象保持不变。

using Bunny.EFCore.DbFirst.Context;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;

namespace Bunny.EFCore.DbFirst
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var builder = WebApplication.CreateBuilder(args);

            var serviceCollection = builder.Services;
            serviceCollection.AddControllers();
            serviceCollection.AddEndpointsApiExplorer();
            serviceCollection.AddSwaggerGen();

            // 注册DbContext服务
            serviceCollection.AddDbContext<EfCoreContext>(options =>
            {
               IConfiguration config = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appsettings.json", optional: true, reloadOnChange: true).Build();
               var connectionString = config["MSSQLServer"];
                options.UseSqlServer(connectionString);
            });

            var app = builder.Build();

            // 创建数据库
            using (var scope = app.Services.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<EfCoreContext>();
                dbContext.Database.EnsureDeleted();
                dbContext.Database.EnsureCreated();
            }

            if (app.Environment.IsDevelopment())
            {
                app.UseSwagger();
                app.UseSwaggerUI();
            }

            app.UseAuthorization();
            app.MapControllers();
            app.Run();
        }
    }
}

数据库优先

先决条件，需要安装dotnet-ef

dotnet tool install --global dotnet-ef

如果不行指定版本

dotnet tool install --global dotnet-ef --version 6.0

需要下载的包

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.SqlServer.Design
Microsoft.EntityFrameworkCore.Tools

执行脚本

Scaffold-DbContext "Data Source=192.168.3.98;Initial Catalog=bunny_test;Persist Security Info=True;User ID=sa;Password=abc1234.;Pooling=False;Encrypt=True;Trust Server Certificate=True" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -ContextDir Models -Context CustomerDbContext -Force

-OutputDir *** 实体文件所存放的文件目录
-ContextDir *** DbContext文件存放的目录
-Context *** DbContext文件名
-Schemas *** 需要生成实体数据的数据表所在的模式
-Tables *** 需要生成实体数据的数据表的集合
-DataAnnotations
-UseDatabaseNames 直接使用数据库中的表名和列名（某些版本不支持）
-Force 强制执行，重写已经存在的实体文件