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();//輸出至主控台 })); } } |
留言
張貼留言