.Net Core Web Api_笔记23_api结合EFCore资料库操作part1_专案前置准备

专案前置准备

建立并配置好visual studio .net core web api专案

.net5 专案中预设若我们将OpenAPI勾选起来会自动配置好Swagger文档相关的dll与设定
预设用的为Swashbuckle.AspNetCore
https://github.com/domaindrivendev/Swashbuckle.AspNetCore

预设专案的Startup.cs

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.OpenApi.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Net5EFCoreWebApiApp
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {

            services.AddControllers();
            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo { Title = "Net5EFCoreWebApiApp", Version = "v1" });
            });
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseSwagger();
                app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "Net5EFCoreWebApiApp v1"));
            }

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

当运行专案预设就会直接跳转至http://localhost:/swagger
这个swagger UI的画面

主要是因为在~\Properties\launchSettings.json
"launchUrl": "swagger" 设定的关系

{
  "$schema": "http://json.schemastore.org/launchsettings.json",
  "iisSettings": {
    "windowsAuthentication": false,
    "anonymousAuthentication": true,
    "iisExpress": {
      "applicationUrl": "http://localhost:12099",
      "sslPort": 0
    }
  },
  "profiles": {
    "IIS Express": {
      "commandName": "IISExpress",
      "launchBrowser": true,
      "launchUrl": "swagger",
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      }
    },
    "Net5EFCoreWebApiApp": {
      "commandName": "Project",
      "dotnetRunMessages": "true",
      "launchBrowser": true,
      "launchUrl": "swagger",
      "applicationUrl": "http://localhost:5000",
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      }
    }
  }
}

EF Core的安装
Microsoft.EntityFrameworkCore.sqlserver

在此要注意由於我目前还是用vs2019 .net5做开发
因此太高版别的EFCore会不支援(需要vs2022 .net6)

这里改为5.0.13版本(不要用6.x的)

CodeFirst开发前置准备

当我们经过一段时间的需求分析与资料朔模後
会产出一些ERD和表关联

这里就用简单的产品分类与产品项目资讯
设计资料库中存在的两张table关联

这里资料字典如下

Step1.准备好资料实体模型(各自都对应一张table)於Model Folder下

~\Models\Product.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace Net5EFCoreWebApiApp.Models
{
    public class Product
    {
        /// <summary>
        /// 产品ID
        /// </summary>
        [Key]
        public Guid ProId { get; set; }

        /// <summary>
        /// 产品名称
        /// </summary>
        [MaxLength(200)]
        public string ProTitle { get; set; }

        /// <summary>
        /// 产品总数
        /// </summary>
        public int ProSum { get; set; }

        /// <summary>
        /// 产品价格
        /// </summary>
        public Decimal ProPrice { get; set; }

        /// <summary>
        /// 产品类别ID
        /// </summary>
        public Guid PCategoryId { get; set; }
        [ForeignKey("PCategoryId")]
        public PCategory PCategory { get; set; }
    }
}

~\Models\PCategory.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace Net5EFCoreWebApiApp.Models
{
    public class PCategory
    {
        /// <summary>
        /// 产品分类ID
        /// </summary>
        [Key]
        public Guid CId { get; set; }

        /// <summary>
        /// 产品分类标题
        /// </summary>
        [MaxLength(100)]
        public string CTitle { get; set; }
    }
}

Step2.建立DbContext衍生类

~\Data\ProductDbContext.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Net5EFCoreWebApiApp.Models;

namespace Net5EFCoreWebApiApp.Data
{
    public class ProductDbContext : DbContext
    {
        public ProductDbContext(DbContextOptions<ProductDbContext> options) : base(options)
        {

        }
        public DbSet<Product> Products { get; set; }
        public DbSet<PCategory> PCategories { get; set; }
    }
}

Step3.至appsettings.json配置DB connection string

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "ProductDb" : "Server=.;Database=AspNetEFCoreDb;uid=sa;pwd=rootroot"
  }
}

Step4.至Startup.cs的ConfigureServices进行DB服务的依赖注入

Startup.cs多引入命名空间
using Microsoft.EntityFrameworkCore;
using Net5EFCoreWebApiApp.Data;

~\Startup.cs

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.OpenApi.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Net5EFCoreWebApiApp.Data;

namespace Net5EFCoreWebApiApp
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ProductDbContext>(options => 
                options.UseSqlServer(Configuration.GetConnectionString("ProductDb")));
            services.AddControllers();
            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo { Title = "Net5EFCoreWebApiApp", Version = "v1" });
            });
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseSwagger();
                app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "Net5EFCoreWebApiApp v1"));
            }

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

Step5.至主程序Program.cs中的Main()去实践第一次(只执行一次)的DB上下文建立
把原先的程序码CreateHostBuilder(args).Build().Run();删掉or注解掉
需引入命名空间Microsoft.Extensions.DependencyInjection
using Microsoft.Extensions.DependencyInjection;

~\Program.cs

using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using Net5EFCoreWebApiApp.Data;

namespace Net5EFCoreWebApiApp
{
    public class Program
    {
        public static void Main(string[] args)
        {
            //CreateHostBuilder(args).Build().Run();
            var host = CreateHostBuilder(args).Build();
            using (var scope = host.Services.CreateScope())
            {
                var services = scope.ServiceProvider;
                try
                {
                    var dbContext = services.GetRequiredService<ProductDbContext>();
                    dbContext.Database.EnsureCreated();
                }
                catch (Exception ex)
                {
                    var logger = services.GetRequiredService<ILogger<Program>>();
                    logger.LogError(ex, "Create DB structure error. ");
                }
            }
            host.Run();
        }

        public static IHostBuilder CreateHostBuilder(string[] args) =>
            Host.CreateDefaultBuilder(args)
                .ConfigureWebHostDefaults(webBuilder =>
                {
                    webBuilder.UseStartup<Startup>();
                });
    }
}

我们希望让Web应用於一开始启动时
就会在Startup class中注册的DB上下文服务能获得
我们自行建立的衍生DbContext物件 "ProductDbContext"

这里藉由dbContext.Database.EnsureCreated();此方法来创建DB
EnsureCreated() 回传Boolean
true 代表新的资料库结构已完成建立。
false 代表资料库结构已经存在不需重新建立

在启动以前先观察目前SSMS是还存在AspNetEFCoreDb这个资料库的
(沿用之前示范ado.net的资料库)

会发现再启动完并没有产生额外新table
因为该DB已存在

这里有两种方式(适应不同情境)

情境1.这是全新的一项专案
就是更改DB名称

再重执行一次应用程序

或是你想要用同一个Db只是若存在既有的先做删除後建立
// Drop the database if it exists
dbContext.Database.EnsureDeleted();

情境2.这是针对既有已存在的DB要再搭配EF进行开发
沿用之前示范ado.net的资料库 (AspNetEFCoreDb这个资料库)

我们首先要启用专案的migration机制
需要透过nuget补安装
Microsoft.EntityFrameworkCore.Tools

第一步骤.一定要先启用和产生初始化的DB Migration
这样子才能让EF Core得知有舍麽DB迁移变动跟额外要补增加哪几张Table到既有的DB当中

开启PMC (Package Manager Console)
「Tools」 - 「NuGet Package Manager」 - 「Package Manager Console」，输入以下指令：

add-migration  {自行命名migration名称}

在以前的EF还有要先输入(参考)
enable-migrations
启用migration机制

现在到了EF Core就不用了

第二步骤.
接着程序中
应该改为
dbContext.Database.Migrate();

再重启应用就会看到DB多产生目标资料表以前既有的table不会有影响资料都还在

当然如果不想用启动专方式也可以透过指令
update-database

当应用程序部属後通常不太可能类似自己在专案中
人工判断有无初始化或等待资料库迁移的任务因此也可以在程序端去藉由
DbContext.Database.GetPendingMigrations().Any()直接在应用程序做判断

最终Program.cs程序

using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using Net5EFCoreWebApiApp.Data;
using Microsoft.EntityFrameworkCore;

namespace Net5EFCoreWebApiApp
{
    public class Program
    {
        public static void Main(string[] args)
        {
            //CreateHostBuilder(args).Build().Run();
            var host = CreateHostBuilder(args).Build();
            using (var scope = host.Services.CreateScope())
            {
                var services = scope.ServiceProvider;
                try
                {
                    var dbContext = services.GetRequiredService<ProductDbContext>();
                    //dbContext.Database.Migrate();
                    //dbContext.Database.EnsureCreated();
                    var result = dbContext.Database.EnsureCreated();
                    if (!result)
                    {
                        if (dbContext.Database.GetPendingMigrations().Any())
                        {
                            dbContext.Database.Migrate();
                        }
                    }
                }
                catch (Exception ex)
                {
                    var logger = services.GetRequiredService<ILogger<Program>>();
                    logger.LogError(ex, "Create DB structure error. ");
                }
            }
            host.Run();
        }

        public static IHostBuilder CreateHostBuilder(string[] args) =>
            Host.CreateDefaultBuilder(args)
                .ConfigureWebHostDefaults(webBuilder =>
                {
                    webBuilder.UseStartup<Startup>();
                });
    }
}

Ref:
Automatic migration of Entity Framework Core
https://developpaper.com/automatic-migration-of-entity-framework-core/

Where should I put Database.EnsureCreated?
https://pretagteam.com/question/where-should-i-put-databaseensurecreated

EF core not creating tables on migrate method
https://stackoverflow.com/questions/50436910/ef-core-not-creating-tables-on-migrate-method

Database.Migrate() creating database but not tables, EF & .NET Core 2
https://stackoverflow.com/questions/50507668/database-migrate-creating-database-but-not-tables-ef-net-core-2

How and where to call Database.EnsureCreated and Database.Migrate?
https://stackoverflow.com/questions/38238043/how-and-where-to-call-database-ensurecreated-and-database-migrate

Can't enable migrations for Entity Framework on VS 2017 .NET Core
https://stackoverflow.com/questions/41403824/cant-enable-migrations-for-entity-framework-on-vs-2017-net-core/41403937

[Entity Framework 6] Code Frist (3) - Migration commands
https://karatejb.blogspot.com/2015/08/entity-framework-code-frist-3-migration.html

3. Migrations
   https://waynecheng.coderbridge.io/2021/03/30/Migrations/

Code First 大道番外-Migrations
https://www.ite2.com/News.aspx?BID=6126&CID=3

本篇已同步发表至个人部落格
https://coolmandiary.blogspot.com/2022/01/net-core-web-api23apiefcorepart1.html