EF Core 中多次从数据库查询实体数据,DbContext跟踪实体的情况
使用EF Core时,如果多次从数据库中查询一个表的同一行数据,DbContext中跟踪(track)的实体到底有几个呢?我们下面就分情况讨论下。
数据库
首先我们的数据库中有一个Person表,其建表脚本如下:
CREATE TABLE [dbo].[Person]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NULL, [Age] [int] NULL, [CreateTime] [datetime] NULL, CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
其中ID是自增的主键,Name是一个Person的名字,Age是一个Person的年龄,CreateTime表示数据是何时创建的
其次我们的数据库中还有一个Book表,其建表脚本如下:
CREATE TABLE [dbo].[Book]( [ID] [int] IDENTITY(1,1) NOT NULL, [PersonID] [int] NULL, [BookName] [nvarchar](50) NULL, [BookDescription] [nvarchar](50) NULL, CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Book] WITH CHECK ADD CONSTRAINT [FK_Book_Person] FOREIGN KEY([PersonID]) REFERENCES [dbo].[Person] ([ID]) GO ALTER TABLE [dbo].[Book] CHECK CONSTRAINT [FK_Book_Person] GO
其中ID是自增的主键,PersonID为外键,其对应Person表的主键列ID,BookName是一本书的名字,BookDescription是一本书的描述信息
Person表和Book表之间是一对多关系,外键FK_Book_Person,通过Person表的主键列ID和Book表的外键列PersonID关联,为强制约束(WITH CHECK),没有级联更新和级联删除。
实体
新建一个.NET Core控制台项目,我们在EF Core中用Scaffold-DbContext指令自动生成实体。
Person实体的代码如下:
using System; using System.Collections.Generic; namespace EFCoreMultipleSelections.Entities { public partial class Person { public Person() { Book = new HashSet<Book>(); } public int Id { get; set; } public string Name { get; set; } public int? Age { get; set; } public DateTime? CreateTime { get; set; } public ICollection<Book> Book { get; set; } } }
其每一个属性都和数据库Person表的列对应,导航属性Book是ICollection<Book>类型的集合,表示一个Person实体包含多个Book实体。
Book实体的代码如下:
using System; using System.Collections.Generic; namespace EFCoreMultipleSelections.Entities { public partial class Book { public int Id { get; set; } public int? PersonId { get; set; } public string BookName { get; set; } public string BookDescription { get; set; } public Person Person { get; set; } } }
其每一个属性都和数据库Book表的列对应,导航属性Person是一个Person实体,表示一个Book实体对应一个Person实体。
Scaffold-DbContext指令生成的DbContext类TestDBContext如下:
using EFCoreMultipleSelections.Logger; using Microsoft.EntityFrameworkCore; namespace EFCoreMultipleSelections.Entities { public partial class TestDBContext : DbContext { public TestDBContext() { } public TestDBContext(DbContextOptions<TestDBContext> options) : base(options) { } public virtual DbSet<Book> Book { get; set; } public virtual DbSet<Person> Person { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (!optionsBuilder.IsConfigured) { optionsBuilder.UseLoggerFactory(new EFLoggerFactory()); optionsBuilder.UseSqlServer("Server=localhost;User Id=sa;Password=1qaz!QAZ;Database=TestDB"); } } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Book>(entity => { entity.Property(e => e.Id).HasColumnName("ID"); entity.Property(e => e.BookDescription).HasMaxLength(50); entity.Property(e => e.BookName).HasMaxLength(50); entity.Property(e => e.PersonId).HasColumnName("PersonID"); entity.HasOne(d => d.Person) .WithMany(p => p.Book) .HasForeignKey(d => d.PersonId) .HasConstraintName("FK_Book_Person"); }); modelBuilder.Entity<Person>(entity => { entity.Property(e => e.Id).HasColumnName("ID"); entity.Property(e => e.CreateTime).HasColumnType("datetime"); entity.Property(e => e.Name).HasMaxLength(50); }); } } }
可以看到没什么特别的,与数据库中Person表和Book表一致,Fluent API设置了它们之间的一对多关系和外键。
测试
.NET Core控制台项目中Program类的代码如下:
using EFCoreMultipleSelections.Entities; using Microsoft.EntityFrameworkCore; using System; using System.Collections.Generic; using System.Linq; namespace EFCoreMultipleSelections { class Program { /// <summary> /// 初始化数据库Person表和Book表的数据,删除数据库中Person表和Book表的老数据,并重新插入三条Person数据和三条Book数据 /// </summary> static void InitData() { using (TestDBContext dbContext = new TestDBContext()) { dbContext.Database.ExecuteSqlCommand("DELETE FROM [dbo].[Book]"); dbContext.Database.ExecuteSqlCommand("DELETE FROM [dbo].[Person]"); var jim = new Person() { Name = "Jim", Age = 20, CreateTime = DateTime.Now }; var tom = new Person() { Name = "Tom", Age = 25, CreateTime = DateTime.Now }; var bill = new Person() { Name = "Bill", Age = 30, CreateTime = DateTime.Now }; //Tom拥有三本书 tom.Book = new List<Book>() { new Book(){ BookName="Chinese", BookDescription="Chinese"}, new Book(){ BookName="English", BookDescription="English"}, new Book(){ BookName="Japanese", BookDescription="Japanese"} }; dbContext.Person.Add(jim); dbContext.Person.Add(tom); dbContext.Person.Add(bill); dbContext.SaveChanges(); } } /// <summary> /// 两次从数据库中查询Person实体tom,更改其Age属性值 /// </summary> static void SelectData() { using (TestDBContext dbContext = new TestDBContext()) { var tom = dbContext.Person.First(p => p.Name == "Tom");//第一次从数据库中查询Person实体tom Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//由于此时Person实体tom才从数据库中被查出来,所以此时其EntityState为Unchanged Console.WriteLine($"Tom's age is : {tom.Age.ToString()} before change");//此时Person实体tom的Age输出为25,和数据库初始化数据一样 tom.Age = tom.Age + 10;//更改Person实体tom的Age,增加10 Console.WriteLine($"Tom's age is : {tom.Age.ToString()} after change");//此时Person实体tom的Age输出为35,为加10后的值 Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//调用DbContext.Entry()方法后,DbContext发现了Person实体tom的Age值已经被更改,所以这里Person实体tom的EntityState为Modified dbContext.Database.ExecuteSqlCommand("UPDATE [dbo].[Person] SET AGE=AGE+30 WHERE [Name]=N'Tom'");//执行这行代码后,数据库Person表中,Tom的Age会变为55 Console.WriteLine(); var tomAgain = dbContext.Person.First(p => p.Name == "Tom");//第二次从数据库中查询Person实体tom,但赋值给另一个变量tomAgain Console.WriteLine($"Tom's age is : {tom.Age.ToString()} after change");//此时Person实体tom的Age输出还是为35,并不是数据库中Person表的实际值55 Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//此时Person实体tom的EntityState还是为Modified Console.WriteLine($"TomAgin's age is : {tomAgain.Age.ToString()}");//Person实体tomAgain的Age输出为35,并不是数据库中Person表的实际值55 Console.WriteLine($"TomAgin's entity state is : {dbContext.Entry(tomAgain).State.ToString()}");//Person实体tomAgain的EntityState为Modified Console.WriteLine($"Whether Tom is TomAgin ? {(tom == tomAgain).ToString()}");//输出为true,表明tom和tomAgain指向的是同一个实体对象 } } /// <summary> /// 两次从数据库中查询Person实体tom,更改其Age属性值,在第二次查询前,将Person实体tom的EntityState设置回Unchanged /// </summary> static void SelectDataWithUnchanged() { using (TestDBContext dbContext = new TestDBContext()) { var tom = dbContext.Person.First(p => p.Name == "Tom");//第一次从数据库中查询Person实体tom Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//由于此时Person实体tom才从数据库中被查出来,所以此时其EntityState为Unchanged Console.WriteLine($"Tom's age is : {tom.Age.ToString()} before change");//此时Person实体tom的Age输出为25,和数据库初始化数据一样 tom.Age = tom.Age + 10;//更改Person实体tom的Age,增加10 Console.WriteLine($"Tom's age is : {tom.Age.ToString()} after change");//此时Person实体tom的Age输出为35,为加10后的值 Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//调用DbContext.Entry()方法后,DbContext发现了Person实体tom的Age值已经被更改,所以这里Person实体tom的EntityState为Modified dbContext.Database.ExecuteSqlCommand("UPDATE [dbo].[Person] SET AGE=AGE+30 WHERE [Name]=N'Tom'");//执行这行代码后,数据库Person表中,Tom的Age会变为55 Console.WriteLine(); dbContext.Entry(tom).State = EntityState.Unchanged;//更改Person实体tom的EntityState为Unchanged Console.WriteLine($"Tom's age is : {tom.Age.ToString()} after unchanged");//此时Person实体tom的Age属性值变回25,为第一次刚从数据库中查询出来时的值(var tom = dbContext.Person.First(p => p.Name == "Tom")) Console.WriteLine(); var tomAgain = dbContext.Person.First(p => p.Name == "Tom");//第二次从数据库中查询Person实体tom,但赋值给另一个变量tomAgain Console.WriteLine($"Tom's age is : {tom.Age.ToString()} after second query");//此时Person实体tom的Age输出还是为25,并不是数据库中Person表的实际值55 Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//此时Person实体tom的EntityState还是为Unchanged Console.WriteLine($"TomAgin's age is : {tomAgain.Age.ToString()}");//Person实体tomAgain的Age输出为25,并不是数据库中Person表的实际值55 Console.WriteLine($"TomAgin's entity state is : {dbContext.Entry(tomAgain).State.ToString()}");//Person实体tomAgain的EntityState为Unchanged Console.WriteLine($"Whether Tom is TomAgin ? {(tom == tomAgain).ToString()}");//输出为true,表明tom和tomAgain指向的是同一个实体对象 } } /// <summary> /// 两次从数据库中查询Person实体tom,更改其Age属性值,在第二次查询前,调用DbContext.Attach方法,将Person实体tom重新Attach到DbContext被跟踪的实体集合中 /// </summary> static void SelectDataWithAttach() { using (TestDBContext dbContext = new TestDBContext()) { var tom = dbContext.Person.First(p => p.Name == "Tom");//第一次从数据库中查询Person实体tom Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//由于此时Person实体tom才从数据库中被查出来,所以此时其EntityState为Unchanged Console.WriteLine($"Tom's age is : {tom.Age.ToString()} before change");//此时Person实体tom的Age输出为25,和数据库初始化数据一样 tom.Age = tom.Age + 10;//更改Person实体tom的Age,增加10 Console.WriteLine($"Tom's age is : {tom.Age.ToString()} after change");//此时Person实体tom的Age输出为35,为加10后的值 Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//调用DbContext.Entry()方法后,DbContext发现了Person实体tom的Age值已经被更改,所以这里Person实体tom的EntityState为Modified dbContext.Database.ExecuteSqlCommand("UPDATE [dbo].[Person] SET AGE=AGE+30 WHERE [Name]=N'Tom'");//执行这行代码后,数据库Person表中,Tom的Age会变为55 Console.WriteLine(); dbContext.Attach(tom);//调用DbContext.Attach方法,将Person实体tom重新Attach到DbContext被跟踪的实体集合中 Console.WriteLine($"Tom's age is : {tom.Age.ToString()} after attach");//此时Person实体tom的Age输出还是为35,为加10后的值 Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()} after attach"); //由于上面调用了DbContext.Attach方法,此时Person实体tom的EntityState变回Unchanged Console.WriteLine(); var tomAgain = dbContext.Person.First(p => p.Name == "Tom");//第二次从数据库中查询Person实体tom,但赋值给另一个变量tomAgain Console.WriteLine($"Tom's age is : {tom.Age.ToString()} after second query");//此时Person实体tom的Age输出还是为35,并不是数据库中Person表的实际值55 Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//此时Person实体tom的EntityState还是为Unchanged Console.WriteLine($"TomAgin's age is : {tomAgain.Age.ToString()}");//Person实体tomAgain的Age输出为35,并不是数据库中Person表的实际值55 Console.WriteLine($"TomAgin's entity state is : {dbContext.Entry(tomAgain).State.ToString()}");//Person实体tomAgain的EntityState为Unchanged Console.WriteLine($"Whether Tom is TomAgin ? {(tom == tomAgain).ToString()}");//输出为true,表明tom和tomAgain指向的是同一个实体对象 } } /// <summary> /// 两次从数据库中查询Person实体tom,更改其Age属性值,在第二次查询前,将Person实体tom从DbContext被跟踪的实体集合中Detach掉 /// </summary> static void SelectDataWithDetach() { using (TestDBContext dbContext = new TestDBContext()) { var tom = dbContext.Person.First(p => p.Name == "Tom");//第一次从数据库中查询Person实体tom Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//由于此时Person实体tom才从数据库中被查出来,所以此时其EntityState为Unchanged Console.WriteLine($"Tom's age is : {tom.Age.ToString()} before change");//此时Person实体tom的Age输出为25,和数据库初始化数据一样 tom.Age = tom.Age + 10;//更改Person实体tom的Age,增加10 Console.WriteLine($"Tom's age is : {tom.Age.ToString()} after change");//此时Person实体tom的Age输出为35,为加10后的值 Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//调用DbContext.Entry()方法后,DbContext发现了Person实体tom的Age值已经被更改,所以这里Person实体tom的EntityState为Modified dbContext.Database.ExecuteSqlCommand("UPDATE [dbo].[Person] SET AGE=AGE+30 WHERE [Name]=N'Tom'");//执行这行代码后,数据库Person表中,Tom的Age会变为55 dbContext.Entry(tom).State = EntityState.Detached;//从DbContext被跟踪的实体集合中Detach Person实体tom,之后DbContext不再跟踪Person实体tom Console.WriteLine(); var tomAgain = dbContext.Person.First(p => p.Name == "Tom");//第二次从数据库中查询Person实体tom,但赋值给另一个变量tomAgain Console.WriteLine($"Tom's age is : {tom.Age.ToString()} after change");//此时Person实体tom的Age输出还是为35 Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//此时Person实体tom的EntityState是Detached,因为前面我们通过改变Person实体tom的State属性,将其从DbContext中Detach掉了 Console.WriteLine($"TomAgin's age is : {tomAgain.Age.ToString()}");//Person实体tomAgain的Age输出为55,和数据库中Person表的数据一致 Console.WriteLine($"TomAgin's entity state is : {dbContext.Entry(tomAgain).State.ToString()}");//因为从数据库中被查出来后,我们没有对Person实体tomAgain的任何属性做更改,所以这里其EntityState为Unchanged Console.WriteLine($"Whether Tom is TomAgin ? {(tom == tomAgain).ToString()}");//输出为false,表明tom和tomAgain指向的不是同一个实体对象 } } /// <summary> /// 三次从数据库中查询Person实体tom: /// 第一次查询不使用EF Core中Eager Loading的Include方法加载Book表的数据 /// 第二次查询使用EF Core中Eager Loading的Include方法加载Book表的数据 /// 第三次查询不使用EF Core中Eager Loading的Include方法加载Book表的数据 /// </summary> static void SelectDataWithNavigationProperty() { using (TestDBContext dbContext = new TestDBContext()) { var tom = dbContext.Person.First(p => p.Name == "Tom");//第一次从数据库中查询Person实体tom,不使用EF Core中Eager Loading的Include方法加载Book表的数据 Console.WriteLine($"Tom's book count is : {tom.Book.Count.ToString()}");//由于第一次查询Person实体tom时,没有使用EF Core中Eager Loading的Include方法加载Book表的数据,所以Person实体tom的导航属性Book集合中没有任何Book实体,其Count为0 Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//由于我们没有对Person实体tom做任何更改,所以此时其EntityState为Unchanged Console.WriteLine(); tom = dbContext.Person.Include(p => p.Book).First(p => p.Name == "Tom");//第二次从数据库中查询Person实体tom,使用EF Core中Eager Loading的Include方法加载Book表的数据 Console.WriteLine($"Tom's book count is : {tom.Book.Count.ToString()}");//由于第二次查询Person实体tom时,使用了EF Core中Eager Loading的Include方法加载Book表的数据,所以Person实体tom的导航属性Book集合中现在有三个Book实体,其Count为3,对应数据库Book表中的三行数据 Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//由于我们没有对Person实体tom做任何更改,所以此时其EntityState为Unchanged Console.WriteLine(); tom = dbContext.Person.First(p => p.Name == "Tom");//第三次从数据库中查询Person实体tom,不使用EF Core中Eager Loading的Include方法加载Book表的数据 Console.WriteLine($"Tom's book count is : {tom.Book.Count.ToString()}");//由于第二次查询Person实体tom时,使用了EF Core中Eager Loading的Include方法加载Book表的数据,尽管我们第三次从数据库中查询Person实体tom时,没有用Include方法,但是Person实体tom的导航属性Book并没有被重置,所以Person实体tom的导航属性Book集合中现在还是有三个Book实体,其Count为3,对应数据库Book表中的三行数据 Console.WriteLine($"Tom's entity state is : {dbContext.Entry(tom).State.ToString()}");//由于我们没有对Person实体tom做任何更改,所以此时其EntityState为Unchanged } } static void Main(string[] args) { InitData(); SelectData(); SelectDataWithUnchanged(); SelectDataWithAttach(); SelectDataWithDetach(); SelectDataWithNavigationProperty(); Console.WriteLine(); Console.WriteLine("Press any key to quit..."); Console.ReadKey(); } } }
这就是我们的测试代码,其中有这么几个方法:
- InitData方法,用于初始化数据库Person表和Book表的数据,删除数据库中Person表和Book表的老数据,并重新插入三条Person数据和三条Book数据
- SelectData方法,两次从数据库中查询Person实体tom,更改其Age属性值
- SelectDataWithUnchanged方法,两次从数据库中查询Person实体tom,更改其Age属性值,在第二次查询前,将Person实体tom的EntityState设置回Unchanged
- SelectDataWithAttach方法,两次从数据库中查询Person实体tom,更改其Age属性值,在第二次查询前,调用DbContext.Attach方法,将Person实体tom重新Attach到DbContext被跟踪的实体集合中
- SelectDataWithDetach方法,两次从数据库中查询Person实体tom,更改其Age属性值,在第二次查询前,将Person实体tom从DbContext被跟踪的实体集合中Detach掉
- SelectDataWithNavigationProperty方法,三次从数据库中查询Person实体tom,第一次查询不使用EF Core中Eager Loading的Include方法加载Book表的数据,第二次查询使用EF Core中Eager Loading的Include方法加载Book表的数据,第三次查询不使用EF Core中Eager Loading的Include方法加载Book表的数据
每次执行 InitData方法后,数据库Person表的数据除了列ID和列CreateTime外,都应该如下:
每次执行 InitData方法后,数据库Book表的数据除了列ID和列PersonID外,都应该如下:
SelectData方法测试
首先我们更改Program类Main方法的代码如下,测试SelectData方法:
static void Main(string[] args) { InitData(); SelectData(); //SelectDataWithUnchanged(); //SelectDataWithAttach(); //SelectDataWithDetach(); //SelectDataWithNavigationProperty(); Console.WriteLine(); Console.WriteLine("Press any key to quit..."); Console.ReadKey(); }
其输出结果如下:
这个输出每一行结果值的解释可以从上面的示例代码注释中看到,
这里主要总结下:
- 第一次从数据库中查询Person实体tom后,DbContext开始跟踪Person实体tom,我们更改了其Age属性值,所以后来Person实体tom的EntityState变为了Modified。
- 然后我们使用DbContext中的ExecuteSqlCommand方法,使用SQL的UPDATE语句更新了数据库Person表中Tom的Age。
- 之后我们第二次从数据库中查询Person实体tom,但赋值给另一个变量tomAgain, 事实证明tom和tomAgain指向的其实是同一个Person对象,这很明确地说明了,不管从数据库中查询Person表多少次,只要DbContext被跟踪的实体集合中有Key属性(即Person实体的ID属性)相同的Person对象,那么DbContext就不会添加新的Person实体到被跟踪的实体集合中,也不会更换老的Person跟踪实体。因为在SelectData方法中,我们第二次查询 Person实体tom后,Person实体tom和tomAgain指向的是同一个Person对象,而且Age属性值还是35和数据库中的值55已经不一致,且 Person实体tom的EntityState还是Modified,说明DbContext并没有因为多次查询数据库Person表,而去更新被跟踪实体集合中的值。
我们可以看到执行SelectData方法后,数据库Person表中Tom的Age列已经变为了55:
SelectData方法中第一次查询Person实体tom( var tom = dbContext.Person.First(p => p.Name == "Tom"))时EF Core后台生成的日志如下,可以看到开启了一个数据库连接做查询:
=============================== EF Core log started =============================== Opening connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Opened connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== A data reader was disposed. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closing connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closed connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished ===============================
SelectData方法中第二次查询Person实体tom( var tomAgain = dbContext.Person.First(p => p.Name == "Tom"))时EF Core后台生成的日志如下,可以看到又开启了一个数据库连接做查询:
=============================== EF Core log started =============================== Opening connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Opened connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executed DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== A data reader was disposed. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closing connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closed connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished ===============================
SelectDataWithUnchanged方法测试
接着我们更改Program类Main方法的代码如下,测试SelectDataWithUnchanged方法:
static void Main(string[] args) { InitData(); //SelectData(); SelectDataWithUnchanged(); //SelectDataWithAttach(); //SelectDataWithDetach(); //SelectDataWithNavigationProperty(); Console.WriteLine(); Console.WriteLine("Press any key to quit..."); Console.ReadKey(); }
其输出结果如下:
这个输出每一行结果值的解释同样可以从上面的示例代码注释中看到,
这里还是主要总结下:
- 第一次从数据库中查询Person实体tom后,DbContext开始跟踪Person实体tom,我们更改了其Age属性值为35,所以后来Person实体tom的EntityState变为了Modified。
- 然后我们使用DbContext中的ExecuteSqlCommand方法,使用SQL的UPDATE语句更新了数据库Person表中Tom的Age。
- 然后我们将Person实体tom的EntityState改回了Unchanged,接着Person实体tom的Age属性值也变回了25,这说明将一个EF Core实体的EntityState改回Unchanged后,会将其还原为才从数据库中查询出来的状态,包括其所有属性值都会被还原为刚放入DbContext中被跟踪时的值。
- 之后我们第二次从数据库中查询Person实体tom,但赋值给另一个变量tomAgain, 因为当前DbContext被跟踪的实体集合中有相同Key属性(即Person实体的ID属性)的Person对象,所以tom和tomAgain指向的其实是同一个Person对象,而且Age属性值还是为25即第一次查询后的值,和当前数据库中的值55不一致,且 Person实体tom的EntityState是Unchanged,说明DbContext并没有因为多次查询数据库Person表,而去更新被跟踪实体集合中的值。
我们可以看到执行SelectDataWithUnchanged方法后,数据库Person表中Tom的Age列也变为了55:
SelectDataWithUnchanged方法中第一次查询Person实体tom(var tom = dbContext.Person.First(p => p.Name == "Tom"))时EF Core后台生成的日志如下,可以看到开启了一个数据库连接做查询:
=============================== EF Core log started =============================== Opening connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Opened connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executed DbCommand (50ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== A data reader was disposed. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closing connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closed connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished ===============================
SelectDataWithUnchanged方法中第二次查询Person实体tom(var tomAgain = dbContext.Person.First(p => p.Name == "Tom"))时EF Core后台生成的日志如下,可以看到又开启了一个数据库连接做查询:
=============================== EF Core log started =============================== Opening connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Opened connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executed DbCommand (35ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== A data reader was disposed. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closing connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closed connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished ===============================
SelectDataWithAttach方法测试
接着我们更改Program类Main方法的代码如下,测试SelectDataWithAttach方法:
static void Main(string[] args) { InitData(); //SelectData(); //SelectDataWithUnchanged(); SelectDataWithAttach(); //SelectDataWithDetach(); //SelectDataWithNavigationProperty(); Console.WriteLine(); Console.WriteLine("Press any key to quit..."); Console.ReadKey(); }
其输出结果如下:
这个输出每一行结果值的解释同样可以从上面的示例代码注释中看到,
这里还是主要总结下:
- 第一次从数据库中查询Person实体tom后,DbContext开始跟踪Person实体tom,我们更改了其Age属性值为35,所以后来Person实体tom的EntityState变为了Modified。
- 然后我们使用DbContext中的ExecuteSqlCommand方法,使用SQL的UPDATE语句更新了数据库Person表中Tom的Age。
- 接着我们使用DbContext.Attach方法,将Person实体tom重新Attach到DbContext被跟踪的实体集合中,导致其EntityState变回Unchanged,这样做和SelectDataWithUnchanged方法中将Person实体tom的EntityState设置为Unchanged有一个最大的不同,在SelectDataWithUnchanged方法中当Person实体tom的EntityState设置为Unchanged后,其实体的所有属性都还原为了第一次从数据库中查询出来的值。但是SelectDataWithAttach方法中,将Person实体tom用DbContext.Attach方法重新Attach到DbContext被跟踪的实体集合中后,其实体的所有属性值并没有被还原,也就是说DbContext将Attach方法调用后的Person实体tom,认定为了新的还原点。
- 之后我们第二次从数据库中查询Person实体tom,但赋值给另一个变量tomAgain, 因为当前DbContext被跟踪的实体集合中有相同Key属性(即Person实体的ID属性)的Person对象,所以tom和tomAgain指向的其实是同一个Person对象,而且Age属性值还是35和当前数据库中的值55不一致,且 Person实体tom的EntityState是Unchanged,说明DbContext并没有因为多次查询数据库Person表,而去更新被跟踪实体集合中的值。
我们可以看到执行SelectDataWithAttach方法后,数据库Person表中Tom的Age列也变为了55:
SelectDataWithAttach方法中第一次查询Person实体tom(var tom = dbContext.Person.First(p => p.Name == "Tom"))时EF Core后台生成的日志如下,可以看到开启了一个数据库连接做查询:
=============================== EF Core log started =============================== Opening connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Opened connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executed DbCommand (46ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== A data reader was disposed. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closing connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closed connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished ===============================
SelectDataWithAttach方法中第二次查询Person实体tom(var tomAgain = dbContext.Person.First(p => p.Name == "Tom"))时EF Core后台生成的日志如下,可以看到又开启了一个数据库连接做查询:
=============================== EF Core log started =============================== Opening connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Opened connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executed DbCommand (42ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== A data reader was disposed. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closing connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closed connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished ===============================
SelectDataWithDetach方法测试
接着我们更改Program类Main方法的代码如下,测试SelectDataWithDetach方法:
static void Main(string[] args) { InitData(); //SelectData(); //SelectDataWithUnchanged(); //SelectDataWithAttach(); SelectDataWithDetach(); //SelectDataWithNavigationProperty(); Console.WriteLine(); Console.WriteLine("Press any key to quit..."); Console.ReadKey(); }
其输出结果如下:
这个输出每一行结果值的解释同样可以从上面的示例代码注释中看到,
这里还是主要总结下:
- 第一次从数据库中查询Person实体tom后,DbContext开始跟踪Person实体tom,我们更改了其Age属性值,所以后来Person实体tom的EntityState变为了Modified。
- 然后我们使用DbContext中的ExecuteSqlCommand方法,使用SQL的UPDATE语句更新了数据库Person表中Tom的Age。
- 然后我们将第一次从数据库中查询出的Person实体tom从DbContext中Detach掉,DbContext被跟踪的实体集合中不再包含Name属性为Tom的对象。
- 之后我们第二次从数据库中查询Person实体tom,但赋值给另一个变量tomAgain,由于第二次查询时,DbContext被跟踪的实体集合中没有Name属性为Tom的对象(其实是根据Person实体的Key属性ID来判断的),所以现在第二次从数据库中被查询出来的Person实体tomAgain为一个新的Person对象,并被放入DbContext被跟踪的实体集合中,开始跟踪,Person实体tomAgain的EntityState为Unchanged。
- 由于Person实体tomAgain为从数据库Person表中查询出的新 Person对象,所以其Age属性值为55和数据库值一致。
- 由于Person实体tom和tomAgain现在指向的是不同的 Person对象,所以tom == tomAgain返回false
我们可以看到执行SelectDataWithDetach方法后,数据库Person表中Tom的Age列也变为了55:
SelectDataWithDetach方法中第一次查询Person实体tom(var tom = dbContext.Person.First(p => p.Name == "Tom"))时EF Core后台生成的日志如下,可以看到开启了一个数据库连接做查询:
=============================== EF Core log started =============================== Opening connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Opened connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== A data reader was disposed. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closing connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closed connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished ===============================
SelectDataWithDetach方法中第二次查询Person实体tom(var tomAgain = dbContext.Person.First(p => p.Name == "Tom"))时EF Core后台生成的日志如下,可以看到又开启了一个数据库连接做查询:
=============================== EF Core log started =============================== Opening connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Opened connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== A data reader was disposed. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closing connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closed connection to database 'TestDB' on server 'localhost'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Context 'Person' started tracking 'TestDBContext' entity. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see key values. =============================== EF Core log finished ===============================
SelectDataWithNavigationProperty方法测试
接着我们更改Program类Main方法的代码如下,测试SelectDataWithNavigationProperty方法:
static void Main(string[] args) { InitData(); //SelectData(); //SelectDataWithUnchanged(); //SelectDataWithAttach(); //SelectDataWithDetach(); SelectDataWithNavigationProperty(); Console.WriteLine(); Console.WriteLine("Press any key to quit..."); Console.ReadKey(); }
其输出结果如下:
这个输出每一行结果值的解释同样可以从上面的示例代码注释中看到,
这里还是主要总结下:
- 我们在第一次从数据库中查询Person实体tom时,由于没有使用EF Core中Eager Loading的Include方法加载Book表的数据,所以Person实体tom的导航属性Book集合中没有任何Book实体(可以看到即便是没有使用Include方法来加载Book表的数据,Person实体tom的导航属性Book也并不为null,是一个长度为0的集合,但是如果是非集合的导航属性,例如Book实体的Person属性,在没有使用Include方法,也没有开启Lazy Loading的情况下就为null了),其Count为0。并且Person实体tom的EntityState为Unchanged。
- 第二次从数据库中查询Person实体tom时,由于使用了EF Core中Eager Loading的Include方法加载Book表的数据,所以Person实体tom的导航属性Book集合中现在有三个Book实体,其Count为3,可以看到即便是第一次查询Person实体tom时没有加载导航属性Book,第二次查询Person实体tom时也可以用Include方法来追加其导航属性Book。
- 第三次从数据库中查询Person实体tom时,即便没有使用EF Core中Eager Loading的Include方法加载Book表的数据,但是Person实体tom的导航属性Book集合中还是有三个Book实体,其Count为3,说明一旦导航属性被前面的查询加载之后,接下来的查询不管是否使用Include方法,都会保存Person实体tom的导航属性Book集合的值,不会因为接下来的查询没有使用Include方法而将其清空。
SelectDataWithNavigationProperty方法中第一次从数据库中查询Person实体tom(var tom = dbContext.Person.First(p => p.Name == "Tom"))时EF Core后台生成的日志如下,可以看到开启了一个数据库连接做查询:
=============================== EF Core log started =============================== Opening connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Opened connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executed DbCommand (41ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== A data reader was disposed. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closing connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closed connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished ===============================
SelectDataWithNavigationProperty方法中第二次从数据库中查询Person实体tom(tom = dbContext.Person.Include(p => p.Book).First(p => p.Name == "Tom"))时EF Core后台生成的日志如下,可以看到开启了一个数据库连接做查询,其中不仅查询了Person表,还使用了SQL中的Join来查询了Book表的数据:
=============================== EF Core log started =============================== Opening connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Opened connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' ORDER BY [p].[ID] =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executed DbCommand (39ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' ORDER BY [p].[ID] =============================== EF Core log finished =============================== =============================== EF Core log started =============================== A data reader was disposed. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [p.Book].[ID], [p.Book].[BookDescription], [p.Book].[BookName], [p.Book].[PersonID] FROM [Book] AS [p.Book] INNER JOIN ( SELECT TOP(1) [p0].[ID] FROM [Person] AS [p0] WHERE [p0].[Name] = N'Tom' ORDER BY [p0].[ID] ) AS [t] ON [p.Book].[PersonID] = [t].[ID] ORDER BY [t].[ID] =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executed DbCommand (36ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [p.Book].[ID], [p.Book].[BookDescription], [p.Book].[BookName], [p.Book].[PersonID] FROM [Book] AS [p.Book] INNER JOIN ( SELECT TOP(1) [p0].[ID] FROM [Person] AS [p0] WHERE [p0].[Name] = N'Tom' ORDER BY [p0].[ID] ) AS [t] ON [p.Book].[PersonID] = [t].[ID] ORDER BY [t].[ID] =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Context 'Book' started tracking 'TestDBContext' entity. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see key values. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Context 'Book' started tracking 'TestDBContext' entity. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see key values. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Context 'Book' started tracking 'TestDBContext' entity. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see key values. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== A data reader was disposed. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closing connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closed connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished ===============================
SelectDataWithNavigationProperty方法中第三次从数据库中查询Person实体tom(tom = dbContext.Person.First(p => p.Name == "Tom"))时EF Core后台生成的日志如下,可以看到开启了一个数据库连接做查询:
=============================== EF Core log started =============================== Opening connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Opened connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Executed DbCommand (28ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(1) [p].[ID], [p].[Age], [p].[CreateTime], [p].[Name] FROM [Person] AS [p] WHERE [p].[Name] = N'Tom' =============================== EF Core log finished =============================== =============================== EF Core log started =============================== A data reader was disposed. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closing connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished =============================== =============================== EF Core log started =============================== Closed connection to database 'TestDB' on server 'CNGDCAAITSQL01'. =============================== EF Core log finished ===============================