實驗環境:
- window10 64位
- vs2019 Enterprise 16.7.4
- .netcoreapp3.1
- EntityFrameworkCore 3.1.9
- sqlserver 2008 R2
實驗目的:
- 驗證EntityFrameworkCore的增删改查
說明:
使用“EntityFrameworkCore”是有兩種方式:DBFirst和CodeFirst,小型項目可以用CodeFirst,大型項目一般用DBFirst,這裡主要采用DBFirst
下面采用DBFirst實驗,最後說明CodeFirst
一、準備資料庫
建立表student、book、teacher、studentbook、studentinfo
他們之間存在1對多、多對多的關系:
- student和book通過studentbook實作多對多
- studentinfo與student是一對一
-
book與teacher是一對多
建庫腳本如下:
--***************先清空具有外鍵的表:studentbook/studentinfo/teacher ***********
if exists (select 1
from sysobjects
where id = object_id('studentbook')
and type = 'U')
begin
drop table studentbook
print '已删除表:studentbook'
end
go
if exists (select 1
from sysobjects
where id = object_id('studentinfo')
and type = 'U')
begin
drop table studentinfo
print '已删除表:studentinfo'
end
go
if exists (select 1
from sysobjects
where id = object_id('teacher')
and type = 'U')
begin
drop table teacher
print '已删除表:teacher'
end
go
--**************************************
--****************<book>**************
if exists (select 1
from sysobjects
where id = object_id('book')
and type = 'U')
begin
drop table book
print '已删除表:book'
end
go
create table [book] (
[id] int not null unique,
[name] varchar(50)
)
ALTER TABLE [book] ADD CONSTRAINT PK_gene_book_id PRIMARY KEY(id)
print '已建立:book'
go
--****************</book>**************
--****************<student>**************
if exists (select 1
from sysobjects
where id = object_id('student')
and type = 'U')
begin
drop table student
print '已删除表:student'
end
go
create table [student] (
[id] int not null unique,
[name] varchar(50) not null ,
[addr] varchar(500) ,
[birth] datetime ,
[img] image
)
ALTER TABLE [student] ADD CONSTRAINT PK_gene_student_id PRIMARY KEY(id)
print '已建立:student'
go
--****************</student>**************
--****************<studentbook>**************
create table [studentbook] (
[sid] int not null ,
[bookid] int not null
)
ALTER TABLE [studentbook] ADD CONSTRAINT PK_gene_studentbook_sid_bookid PRIMARY KEY([sid],[bookid])
--************外鍵限制<FK_userbook_userbook>*****************
ALTER TABLE studentbook ADD CONSTRAINT FK_userbook_userbook FOREIGN KEY(sid) REFERENCES test.dbo.student (id) ON DELETE NO ACTION ON UPDATE NO ACTION
--************外鍵限制</FK_userbook_userbook>*****************
--************外鍵限制<FK_userbook_userbook1>*****************
ALTER TABLE studentbook ADD CONSTRAINT FK_userbook_userbook1 FOREIGN KEY(bookid) REFERENCES test.dbo.book (id) ON DELETE NO ACTION ON UPDATE NO ACTION
--************外鍵限制</FK_userbook_userbook1>*****************
print '已建立:studentbook'
go
--****************</studentbook>**************
--****************<studentinfo>**************
create table [studentinfo] (
[sid] int not null unique,
[indate] datetime ,
[type] int ,
[desc] varchar(500)
)
ALTER TABLE [studentinfo] ADD CONSTRAINT PK_gene_studentinfo_sid PRIMARY KEY(sid)
--************外鍵限制<FK_studentinfo_student>*****************
ALTER TABLE studentinfo ADD CONSTRAINT FK_studentinfo_student FOREIGN KEY(sid) REFERENCES test.dbo.student (id) ON DELETE NO ACTION ON UPDATE NO ACTION
--************外鍵限制</FK_studentinfo_student>*****************
print '已建立:studentinfo'
go
--****************</studentinfo>**************
--****************<teacher>**************
create table [teacher] (
[id] int not null unique,
[name] varchar(50) not null ,
[bid] int not null
)
ALTER TABLE [teacher] ADD CONSTRAINT PK_gene_teacher_id PRIMARY KEY(id)
--************外鍵限制<FK_teacher_teacher>*****************
ALTER TABLE teacher ADD CONSTRAINT FK_teacher_teacher FOREIGN KEY(bid) REFERENCES test.dbo.book (id) ON DELETE NO ACTION ON UPDATE NO ACTION
--************外鍵限制</FK_teacher_teacher>*****************
print '已建立:teacher'
go
--****************</teacher>**************
二、準備工程
打開vs2019,建立兩個.netcore控制台項目如下:
編輯dbfirst工程,添加EntityFrameworkCore包:
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>netcoreapp3.1</TargetFramework>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.10" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.10">
<PrivateAssets>all</PrivateAssets>
<IncludeAssets>runtime; build; native; contentfiles; analyzers</IncludeAssets>
</PackageReference>
<PackageReference Include="Microsoft.Extensions.Logging.Console" Version="3.1.10" />
<PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="3.1.10" />
</ItemGroup>
</Project>
或者使用vs安裝如下:
注意:上面包的作用
- Microsoft.EntityFrameworkCore.SqlServer:efcore操作sqlserver;
- Microsoft.EntityFrameworkCore.Tools:efcore的工具包,可用來做dbfirst/codefirst
- Microsoft.Extensions.Logging.Console:将日志輸出到控制台
- Microsoft.Extensions.Logging.Debug:将日志輸出到vs的調試視窗
三、從資料庫生成模型代碼
打開包管理控制台視窗:
執行指令:
Scaffold-DbContext "Data Source = .;Initial Catalog = test;User Id = sa;Password = 123456;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
完成後觀察項目中自動生成了的代碼:
Book.cs:
using System;
using System.Collections.Generic;
namespace dbfirst.Models
{
public partial class Book
{
public Book()
{
Studentbook = new HashSet<Studentbook>();
Teacher = new HashSet<Teacher>();
}
public int Id { get; set; }
public string Name { get; set; }
public virtual ICollection<Studentbook> Studentbook { get; set; }
public virtual ICollection<Teacher> Teacher { get; set; }
}
}
Student.cs
using System;
using System.Collections.Generic;
namespace dbfirst.Models
{
public partial class Student
{
public Student()
{
Studentbook = new HashSet<Studentbook>();
}
public int Id { get; set; }
public string Name { get; set; }
public string Addr { get; set; }
public DateTime? Birth { get; set; }
public byte[] Img { get; set; }
public virtual Studentinfo Studentinfo { get; set; }
public virtual ICollection<Studentbook> Studentbook { get; set; }
}
}
Teacher:
using System;
using System.Collections.Generic;
namespace dbfirst.Models
{
public partial class Teacher
{
public int Id { get; set; }
public string Name { get; set; }
public int Bid { get; set; }
public virtual Book B { get; set; }
}
}
Studentbook.cs:
using System;
using System.Collections.Generic;
namespace dbfirst.Models
{
public partial class Studentbook
{
public int Sid { get; set; }
public int Bookid { get; set; }
public virtual Book Book { get; set; }
public virtual Student S { get; set; }
}
}
Studentinfo.cs:
using System;
using System.Collections.Generic;
namespace dbfirst.Models
{
public partial class Studentinfo
{
public int Sid { get; set; }
public DateTime? Indate { get; set; }
public int? Type { get; set; }
public string Desc { get; set; }
public virtual Student S { get; set; }
}
}
testContext.cs:
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
namespace dbfirst.Models
{
public partial class testContext : DbContext
{
public testContext()
{
}
public testContext(DbContextOptions<testContext> options)
: base(options)
{
}
public virtual DbSet<Book> Book { get; set; }
public virtual DbSet<Student> Student { get; set; }
public virtual DbSet<Studentbook> Studentbook { get; set; }
public virtual DbSet<Studentinfo> Studentinfo { get; set; }
public virtual DbSet<Teacher> Teacher { get; set; }
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. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
optionsBuilder.UseSqlServer("Data Source = .;Initial Catalog = test;User Id = sa;Password = 123456;");
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasAnnotation("ProductVersion", "2.2.6-servicing-10079");
modelBuilder.Entity<Book>(entity =>
{
entity.ToTable("book");
entity.HasIndex(e => e.Id)
.HasName("UQ__book__3213E83ED78FF78C")
.IsUnique();
entity.Property(e => e.Id)
.HasColumnName("id")
.ValueGeneratedNever();
entity.Property(e => e.Name)
.HasColumnName("name")
.HasMaxLength(50)
.IsUnicode(false);
});
modelBuilder.Entity<Student>(entity =>
{
entity.ToTable("student");
entity.HasIndex(e => e.Id)
.HasName("UQ__student__3213E83E18FC0D17")
.IsUnique();
entity.Property(e => e.Id)
.HasColumnName("id")
.ValueGeneratedNever();
entity.Property(e => e.Addr)
.HasColumnName("addr")
.HasMaxLength(500)
.IsUnicode(false);
entity.Property(e => e.Birth)
.HasColumnName("birth")
.HasColumnType("datetime");
entity.Property(e => e.Img)
.HasColumnName("img")
.HasColumnType("image");
entity.Property(e => e.Name)
.IsRequired()
.HasColumnName("name")
.HasMaxLength(50)
.IsUnicode(false);
});
modelBuilder.Entity<Studentbook>(entity =>
{
entity.HasKey(e => new { e.Sid, e.Bookid })
.HasName("PK_gene_studentbook_sid_bookid");
entity.ToTable("studentbook");
entity.Property(e => e.Sid).HasColumnName("sid");
entity.Property(e => e.Bookid).HasColumnName("bookid");
entity.HasOne(d => d.Book)
.WithMany(p => p.Studentbook)
.HasForeignKey(d => d.Bookid)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_userbook_userbook1");
entity.HasOne(d => d.S)
.WithMany(p => p.Studentbook)
.HasForeignKey(d => d.Sid)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_userbook_userbook");
});
modelBuilder.Entity<Studentinfo>(entity =>
{
entity.HasKey(e => e.Sid)
.HasName("PK_gene_studentinfo_sid");
entity.ToTable("studentinfo");
entity.HasIndex(e => e.Sid)
.HasName("UQ__studenti__DDDFDD370E427379")
.IsUnique();
entity.Property(e => e.Sid)
.HasColumnName("sid")
.ValueGeneratedNever();
entity.Property(e => e.Desc)
.HasColumnName("desc")
.HasMaxLength(500)
.IsUnicode(false);
entity.Property(e => e.Indate)
.HasColumnName("indate")
.HasColumnType("datetime");
entity.Property(e => e.Type).HasColumnName("type");
entity.HasOne(d => d.S)
.WithOne(p => p.Studentinfo)
.HasForeignKey<Studentinfo>(d => d.Sid)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_studentinfo_student");
});
modelBuilder.Entity<Teacher>(entity =>
{
entity.ToTable("teacher");
entity.HasIndex(e => e.Id)
.HasName("UQ__teacher__3213E83EAD9D5139")
.IsUnique();
entity.Property(e => e.Id)
.HasColumnName("id")
.ValueGeneratedNever();
entity.Property(e => e.Bid).HasColumnName("bid");
entity.Property(e => e.Name)
.IsRequired()
.HasColumnName("name")
.HasMaxLength(50)
.IsUnicode(false);
entity.HasOne(d => d.B)
.WithMany(p => p.Teacher)
.HasForeignKey(d => d.Bid)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_teacher_teacher");
});
}
}
}
四、 修改生成的 testContext
,增加日志輸出
testContext
修改
testContext
的
OnConfiguring
方法如下:
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. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
optionsBuilder.UseSqlServer("Data Source = .;Initial Catalog = test;User Id = sa;Password = 123456;");
//準備依賴容器
var services = new ServiceCollection();
services.AddLogging(builder =>
{
builder.ClearProviders();
//将日志輸出到控制台
builder.AddConsole();
//将日志輸出到vs的調試視窗
builder.AddDebug();
});
var provider = services.BuildServiceProvider();
//從容器中拿到日志工廠
var loggerFactory = provider.GetRequiredService<ILoggerFactory>();
//啟用efcore的日志記錄
optionsBuilder.UseLoggerFactory(loggerFactory);
//使efcore的日志中輸出敏感資訊(含參數化值)
optionsBuilder.EnableSensitiveDataLogging(true);
}
}
五、編寫增删改查代碼
隻需修改Program.cs即可:
using System;
using System.Text;
using System.Threading.Tasks;
using dbfirst.Models;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
namespace dbfirst
{
class Program
{
static void Main(string[] args)
{
//TestAdd();
//TestQuery();
//TestUpdate();
//TestDelete();
Console.WriteLine("Hello World!");
}
private static void TestDelete()
{
using (testContext db = new testContext())
{
var id = db.Student.Where(i => i.Name == "小明").Select(i => i.Id).FirstOrDefault();
db.Studentbook.RemoveRange(db.Studentbook.Where(i => i.Sid == id));
db.SaveChanges();
}
}
private static void TestUpdate()
{
using (testContext db = new testContext())
{
Book b = db.Book.Where(i => i.Name == "外語").FirstOrDefault();
if (b != null)
{
b.Name = "英語";
}
db.SaveChanges();
}
}
private static void TestQuery()
{
using (testContext db = new testContext())
{
var set = (from stu in db.Student join b in db.Studentbook on stu.Id equals b.Sid join book in db.Book on b.Bookid equals book.Id where stu.Name.Contains("小") select new { StuName = stu.Name, stuId = stu.Id, bookName = book.Name, bookId = book.Id });
var list = set.ToList();
foreach (var i in list)
{
Console.WriteLine(i.bookId + ":" + i.bookName + "," + i.StuName + ":" + i.stuId);
}
}
}
private static void TestAdd()
{
using (testContext db = new testContext())
{
//建立四名學生(小明、小紅、小剛、小軍)
Student xiaoming = new Student()
{
Id = 1,
Name = "小明",
Birth = DateTime.Parse("1995-02-03"),
Addr = "天民路28号",
Img = Encoding.UTF8.GetBytes("哈哈 this is a 測試")
};
Student xiaohong = new Student()
{
Id = 2,
Name = "小紅",
Birth = DateTime.Parse("1998-07-03"),
Addr = "順豐路28号",
Img = Encoding.UTF8.GetBytes("哈哈 this is a 測試")
};
Student xiaogang = new Student()
{
Id = 3,
Name = "小剛",
Birth = DateTime.Parse("1994-07-03"),
Addr = "江山28号",
Img = Encoding.UTF8.GetBytes("哈哈 this is a 測試")
};
Student xiaojun = new Student()
{
Id = 4,
Name = "小軍",
Birth = DateTime.Parse("1995-07-03"),
Addr = "江山29号",
Img = Encoding.UTF8.GetBytes("哈哈 this is a 測試")
};
//建立三門課(國文、數學、外語)
Book yuwen = new Book()
{
Id = 1,
Name = "國文"
};
Book shuxue = new Book()
{
Id = 2,
Name = "數學"
};
Book waiyu = new Book()
{
Id = 3,
Name = "外語"
};
//建立三個老師分别教三門課
Teacher yuwenlaoshi = new Teacher()
{
Id = 1,
Name = "國文老師",
Bid = yuwen.Id
};
Teacher shuxuelaoshi = new Teacher()
{
Id = 2,
Name = "數學老師",
Bid = shuxue.Id
};
Teacher waiyulaoshi = new Teacher()
{
Id = 3,
Name = "外語老師",
Bid = waiyu.Id
};
//給三個學生都選擇語數外三門課
Studentbook xiaohongyuwen = new Studentbook()
{
Bookid = yuwen.Id,
Sid = xiaohong.Id
};
Studentbook xiaohongshuxue = new Studentbook()
{
Bookid = shuxue.Id,
Sid = xiaohong.Id
};
Studentbook xiaohongwaiyu = new Studentbook()
{
Bookid = waiyu.Id,
Sid = xiaohong.Id
};
Studentbook xiaomingyuwen = new Studentbook()
{
Bookid = yuwen.Id,
Sid = xiaoming.Id
};
Studentbook xiaomingshuxue = new Studentbook()
{
Bookid = shuxue.Id,
Sid = xiaoming.Id
};
Studentbook xiaomingwaiyu = new Studentbook()
{
Bookid = waiyu.Id,
Sid = xiaoming.Id
};
Studentbook xiaogangyuwen = new Studentbook()
{
Bookid = yuwen.Id,
Sid = xiaogang.Id
};
Studentbook xiaogangshuxue = new Studentbook()
{
Bookid = shuxue.Id,
Sid = xiaogang.Id
};
Studentbook xiaogangwaiyu = new Studentbook()
{
Bookid = waiyu.Id,
Sid = xiaogang.Id
};
Studentbook xiaojunyuwen = new Studentbook()
{
Bookid = yuwen.Id,
Sid = xiaojun.Id
};
Studentbook xiaojunshuxue = new Studentbook()
{
Bookid = shuxue.Id,
Sid = xiaojun.Id
};
Studentbook xiaojunwaiyu = new Studentbook()
{
Bookid = waiyu.Id,
Sid = xiaojun.Id
};
//給四名同學添加擴充資訊
Studentinfo xiaominginfo = new Studentinfo()
{
Sid = xiaoming.Id,
Indate = DateTime.Now,
Type = 1,
Desc = "一段描述"
};
Studentinfo xiaojuninfo = new Studentinfo()
{
Sid = xiaojun.Id,
Indate = DateTime.Now,
Type = 1,
Desc = "一段描述"
};
Studentinfo xiaohonginfo = new Studentinfo()
{
Sid = xiaohong.Id,
Indate = DateTime.Now,
Type = 1,
Desc = "一段描述"
};
Studentinfo xiaoganginfo = new Studentinfo()
{
Sid = xiaogang.Id,
Indate = DateTime.Now,
Type = 1,
Desc = "一段描述"
};
db.AddRange(xiaoming, xiaogang, xiaojun, xiaohong);
db.AddRange(yuwen, waiyu, shuxue);
db.AddRange(yuwenlaoshi, shuxuelaoshi, waiyulaoshi);
db.AddRange(xiaominginfo, xiaoganginfo, xiaojuninfo, xiaohonginfo);
db.AddRange(xiaomingyuwen, xiaomingshuxue, xiaomingwaiyu);
db.AddRange(xiaohongyuwen, xiaohongshuxue, xiaohongwaiyu);
db.AddRange(xiaogangyuwen, xiaogangshuxue, xiaogangwaiyu);
db.AddRange(xiaojunyuwen, xiaojunshuxue, xiaojunwaiyu);
db.SaveChanges();
}
}
}
}
六、測試效果
先運作“TestAdd”添加資料,觀察如下:
控制台日志輸出如下:
接下來依次運作:
TestQuery
、
TestUpdate
、
TestDelete
即可。
七、codefirst模式
在dbfirst實驗的基礎上,完成codefirst實驗,修改
codefirst
工程:
- 添加nuget包(和dbfirst一樣即可)
- 将
中的代碼拷貝到dbfirst工程
中,并将命名空間修改為codefirst工程
codefirst
- 修改資料庫為
(在test2
中修改)testContext.cs
最終效果如下:
- 将
設為啟動程式codefirst
- 在包管理平台中執行
指令,如下所示:Add-Migration init
- 可看到生成的遷移類和快照
- 在包管理平台中執行
指令,如下所示:Update-Database
- 觀察資料庫test2:
可以看到,自動建了資料庫test2并且建好了表結構
- 拷貝dbfirst的Program.cs的代碼到codefirst中去(注意:修改命名空間),依次執行測試代碼即可。
八、說一下codefirst模式和dbfirst模式
首先說明dbfirst就是先建好資料庫,然後使用指令
Scaffold-DbContext "Data Source = .;Initial Catalog = test;User Id = sa;Password = 123456;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
将指定的資料庫中所有表生成代碼到指定的工程目錄中去,這就結束了。
再看一下codefirst模式,在這種模式下最後不要在資料庫用戶端修改資料庫的結構,如果想修改資料庫結構,就先改代碼,然後讓EntityFrameworkCore工具自動去執行修改資料庫結構。
codefirst一般操作流程如下:
- 工程中建好實體model和dbcontext(注意工程需要是可執行程式)
- 在包管理控制台中使用
指令生成用來執行遷移操作的代碼(init是每次遷移的一個辨別,可自定義)Add-Migration init
- 如果此時資料庫結構有改動需要再次修改工程的實體model和dbcontext,那麼你可以執行
指令直接将最近的依次遷移代碼删除掉Remove-Migration
- 确認工程的實體model和dbcontext沒問題并且生成的遷移代碼沒有問題,那麼可以執行指令
将遷移代碼應用到資料庫中,此時codefirst也就完成了。Update-Database
codefirst涉及到的指令說明:
- Add-Migration [自定義辨別] ,示例:
Add-Migration init
(注:這個過程不需要和資料庫互動)
初次使用将根據DbContext找到對應的實體類,然後根據實體類生成“20190910082556_init”遷移類,這個類裡面記錄的是要建立的表和字段,并且生成此時這個表的快照(根據實體類生成)
非初次運作這個指令的時候,将對比目前的實體類和這個表的快照,然後根據對比的結果生成遷移類并更新快照
-
Update-Database
首先連接配接資料庫擷取資料庫已經遷移過的版本号(表:__EFMigrationsHistory中記錄曆史更新記錄),然後對比目前工程下的遷移代碼的版本号,如果發現還有未執行的遷移代碼那麼就執行遷移代碼,如果沒有的話就不執行
-
Remove-Migration
首先連接配接資料庫擷取資料庫已經遷移過的版本号,然後對比目前工程最新的遷移類的版本号,如果資料庫中沒有記錄這個版本号表示遷移類還未執行,那麼直接删除掉這個遷移類,并更新快照,如果發現已經執行了這個遷移類那麼就報錯提示“已經更新到資料庫了”
-
Get-DbContext
擷取目前工程的上下文環境
-
Script-Migration
将目前的遷移代碼翻譯成sql腳本