.Net Core Web Api_筆記23_api結合EFCore資料庫操作part1_專案前置準備

2021 iThome 鐵人賽

自我挑戰組

.NET Core WebApi網頁應用開發系列第 23 篇

13th鐵人賽

Samuel

2022-01-13 09:23:24

7013 瀏覽

分享至

專案前置準備

建立並配置好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