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

 為了怕之後有新人來
一來要帶人
一來還要趕專案
因此筆記一下工作常用的開發工具



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




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


如要快速於本機產生此DB及相應Tables
執行如下T-SQL即可

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
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指令

1
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方法

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
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的簡單範本

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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對應生成之語法

1
2
3
4
5
6
7
8
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程式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
        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();//輸出至主控台
                }));
            }
        }















留言

這個網誌中的熱門文章

何謂淨重(Net Weight)、皮重(Tare Weight)與毛重(Gross Weight)

Architecture(架構) 和 Framework(框架) 有何不同?_軟體設計前的事前規劃的藍圖概念

(2021年度)駕訓學科筆試準備題庫歸納分析_法規是非題