EntityFramework Core筆記(3)_DbFirst指令_監聽SQL語法兩種方式(SQL Server Profiler/LoggerFactory)

為了怕之後有新人來

一來要帶人

一來還要趕專案

因此筆記一下工作常用的開發工具

專案.net core主控台新建並安裝好如下四套件

資料庫中目前存在的資料表

如要快速於本機產生此DB及相應Tables

執行如下T-SQL即可

USE [master]
GO
/****** Object:  Database [AdvancedCustomerDB]    Script Date: 2021/10/29 11:40:58 ******/
CREATE DATABASE [AdvancedCustomerDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'AdvancedCustomerDB', FILENAME = N'E:\IT_Db\AdvancedCustomerDB.mdf' , SIZE = 73728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'AdvancedCustomerDB_log', FILENAME = N'E:\IT_Db\AdvancedCustomerDB_log.ldf' , SIZE = 204800KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
ALTER DATABASE [AdvancedCustomerDB] SET COMPATIBILITY_LEVEL = 150
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AdvancedCustomerDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [AdvancedCustomerDB] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET ARITHABORT OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [AdvancedCustomerDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [AdvancedCustomerDB] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET  DISABLE_BROKER 
GO
ALTER DATABASE [AdvancedCustomerDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [AdvancedCustomerDB] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET RECOVERY FULL 
GO
ALTER DATABASE [AdvancedCustomerDB] SET  MULTI_USER 
GO
ALTER DATABASE [AdvancedCustomerDB] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [AdvancedCustomerDB] SET DB_CHAINING OFF 
GO
ALTER DATABASE [AdvancedCustomerDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [AdvancedCustomerDB] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [AdvancedCustomerDB] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [AdvancedCustomerDB] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
EXEC sys.sp_db_vardecimal_storage_format N'AdvancedCustomerDB', N'ON'
GO
ALTER DATABASE [AdvancedCustomerDB] SET QUERY_STORE = OFF
GO
USE [AdvancedCustomerDB]
GO
/****** Object:  Table [dbo].[Commodity]    Script Date: 2021/10/29 11:40:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Commodity](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ProductId] [bigint] NULL,
	[CategoryId] [int] NULL,
	[Title] [varchar](500) NULL,
	[Price] [decimal](18, 2) NULL,
	[Url] [varchar](1000) NULL,
	[ImageUrl] [varchar](1000) NULL,
 CONSTRAINT [PK_Commodity] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[CompanyInfo]    Script Date: 2021/10/29 11:40:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CompanyInfo](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[CreateTime] [datetime] NULL,
	[CreatorId] [int] NOT NULL,
	[LastModifierId] [int] NULL,
	[LastModifyTime] [datetime] NULL,
 CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SysUser]    Script Date: 2021/10/29 11:40:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SysUser](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[Password] [varchar](50) NULL,
	[Status] [int] NOT NULL,
	[Phone] [varchar](12) NULL,
	[Mobile] [varchar](12) NULL,
	[Address] [varchar](500) NULL,
	[Email] [varchar](50) NULL,
	[QQ] [bigint] NULL,
	[WeChat] [varchar](50) NULL,
	[Sex] [int] NULL,
	[LastLoginTime] [datetime] NULL,
	[CreateTime] [datetime] NULL,
	[CreateId] [int] NULL,
	[LastModifyTime] [datetime] NULL,
	[LastModifyId] [int] NULL,
	[CompanyId] [int] NULL,
 CONSTRAINT [PK_SysUser] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Commodity] ON 
GO

於專案中設置好啟動專案為有添加四套件的專案(若屬於方案不只一專案的情況)

並開啟套件管理器主控台

執行如下DB First指令

Scaffold-DbContext "Data Source=LAPTOP-86Q2OIMP\SQLEXPRESS;Initial Catalog=AdvancedCustomerDB;User ID=sa;Password=rootroot" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -ContextDir / -Context CustomerDbContext -Force

-OutputDir : 實體文件所存放的文件目錄

-ContextDir : DbContext文件存放的目錄

-Context : DbContext文件名

-Schemas : 需要生成實體data的資料表所在的模式

-Tables : 需要生成實體data的資料表的集合

-DataAnnotations

-UseDatabaseNames : 直接使用DB中的table名和column名（for特定幾版本）

-Force : 強制執行，重寫已經存在的實體文件

注意!! 在下Scaffold-DbContext指令時，程式不能存有任何編譯上的錯誤，否則將會失敗

預設產生之Db上下文的.cs程式檔案內容

會是以partial修飾當前CustomerDbContext 與其相應的OnModelCreatingPartial方法

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using EFCoreDbFirst_ConsoleApp.Models;

#nullable disable

namespace EFCoreDbFirst_ConsoleApp
{
    public partial class CustomerDbContext : DbContext
    {
        public CustomerDbContext()
        {
        }

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

        public virtual DbSet<Commodity> Commodities { get; set; }
        public virtual DbSet<CompanyInfo> CompanyInfos { get; set; }
        public virtual DbSet<SysUser> SysUsers { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Data Source=LAPTOP-86Q2OIMP\\SQLEXPRESS;Initial Catalog=AdvancedCustomerDB;User ID=sa;Password=rootroot");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasAnnotation("Relational:Collation", "Chinese_Taiwan_Stroke_CI_AS");

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

                entity.Property(e => e.ImageUrl)
                    .HasMaxLength(1000)
                    .IsUnicode(false);

                entity.Property(e => e.Price).HasColumnType("decimal(18, 2)");

                entity.Property(e => e.Title)
                    .HasMaxLength(500)
                    .IsUnicode(false);

                entity.Property(e => e.Url)
                    .HasMaxLength(1000)
                    .IsUnicode(false);
            });

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

                entity.Property(e => e.CreateTime).HasColumnType("datetime");

                entity.Property(e => e.LastModifyTime).HasColumnType("datetime");

                entity.Property(e => e.Name)
                    .HasMaxLength(50)
                    .IsUnicode(false);
            });

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

                entity.Property(e => e.Address)
                    .HasMaxLength(500)
                    .IsUnicode(false);

                entity.Property(e => e.CreateTime).HasColumnType("datetime");

                entity.Property(e => e.Email)
                    .HasMaxLength(50)
                    .IsUnicode(false);

                entity.Property(e => e.LastLoginTime).HasColumnType("datetime");

                entity.Property(e => e.LastModifyTime).HasColumnType("datetime");

                entity.Property(e => e.Mobile)
                    .HasMaxLength(12)
                    .IsUnicode(false);

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

                entity.Property(e => e.Password)
                    .HasMaxLength(50)
                    .IsUnicode(false);

                entity.Property(e => e.Phone)
                    .HasMaxLength(12)
                    .IsUnicode(false);

                entity.Property(e => e.Qq).HasColumnName("QQ");

                entity.Property(e => e.WeChat)
                    .HasMaxLength(50)
                    .IsUnicode(false);
            });

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

於Program.cs主程式寫測式CRUD的簡單範本

using EFCoreDbFirst_ConsoleApp.Models;
using System;
using System.Linq;

namespace EFCoreDbFirst_ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (CustomerDbContext context = new CustomerDbContext())
                {
                    Console.WriteLine("[Begin]======EFCore 資料庫添加======");
                    var addData = new Models.CompanyInfo()
                    {
                        Name = "Facebook",
                        CreateTime = DateTime.Now,
                        CreatorId = 1,
                        LastModifierId = 1,
                        LastModifyTime = DateTime.Now
                    };
                    context.CompanyInfos.Add(addData);
                    context.SaveChanges();
                    Console.WriteLine("[End]======EFCore 資料庫添加======");

                    Console.WriteLine("======EFCore 資料庫查詢======");
                    CompanyInfo companyInfo = context.CompanyInfos.OrderByDescending(item => item.Id).FirstOrDefault();

                    Console.WriteLine("======EFCore 資料庫修改更新======");
                    companyInfo.Name = "Meta";
                    context.SaveChanges();

                    Console.WriteLine("======EFCore 資料庫修改刪除======");
                    context.CompanyInfos.Remove(companyInfo);
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }
    }
}

起初只有13329筆資料

在執行完insert後多增一筆，變13330。

在執行完更新公司名後資料庫也成功被異動

最後運行刪除

預設透過Db First映射

會自動生成Table對應實體類別名稱

如果這時候把DB裡面CompanyInfo更名為Company

就會導致DBFirst原先自動生成的Class無法存取

這時候再次運行會發覺程式失效

如果是跟團隊合作開發

通常其他人也會再去下一次DB First 的 efcore sync指令

來把目前資料庫最新狀態給同步至專案資料結構中

而通常會需要去觀察監聽SQL對應語句生成為何

第一種方法.

這裡可至SSMS打開 Profiler

確保TSQL下面兩項皆有勾選起來

預設日誌可能不是很乾淨有殘留其他的紀錄

可以按橡皮擦來清除

在每次運行完SaveChange後都會觸發底層生成之SQL語句執行

以這邊.Add對應生成之語法

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [CompanyInfo] ([CreateTime], [CreatorId], [LastModifierId], [LastModifyTime], [Name])
VALUES (@p0, @p1, @p2, @p3, @p4);
SELECT [Id]
FROM [CompanyInfo]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

',N'@p0 datetime,@p1 int,@p2 int,@p3 datetime,@p4 varchar(50)',@p0='2022-04-17 15:49:06.907',@p1=1,@p2=1,@p3='2022-04-17 15:49:06.910',@p4='Facebook'

第二種方法.

至 CustomerDbContext 引入Microsoft.Extensions.Logging 的 namespace

Nuget要多裝一個套件叫做

Microsoft.Extensions.Logging.Console

並到OnConfiguring方法增加LoggerFactory.Create程式

        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. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
                optionsBuilder.UseSqlServer("Data Source=LAPTOP-86Q2OIMP\\SQLEXPRESS;Initial Catalog=AdvancedCustomerDB;User ID=sa;Password=rootroot");

                optionsBuilder.UseLoggerFactory(LoggerFactory.Create(option =>
                {
                    option.AddConsole();//輸出至主控台
                }));
            }
        }