【ASP.NET Core学习】Entity Framework Core
这里介绍在ASP.NET Core中使用EF Core，这里数据库选的是Sql Server
- 如何使用Sql Server
- 添加模型 && 数据库迁移
- 查询数据
- 保存数据
如何使用Sql Server
1. 安装dotnet-ef（已经安装忽略）
dotnet tool install --global dotnet-ef
2. 添加包Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Design
3. 添加包Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
4. 添加DbContext
public class EFCoreDbContext : DbContext { public EFCoreDbContext(DbContextOptions<EFCoreDbContext> options) : base(options) { } }
5.在ConfigureServices注入DbContext
public void ConfigureServices(IServiceCollection services) { services.AddRazorPages(); services.AddDbContext<Data.EFCoreDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))); }
经过上面5步，我们就可以在项目中使用数据库，在需要的地方注入DbContext即可
添加模型
我们就以学校 -> 学生这样的模型（一对多）为例，字段也尽量简洁，这里不是展示设计，以展示操作EF Core为主，所以类定义未必是最合适的。
学校类
[Table("School")] public class School { [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; } [Display(Name = "学校名称")] [Required(ErrorMessage = "学校名称不能为空")] [StringLength(100, ErrorMessage = "学校名称最大长度为100")] public string Name { get; set; } [Display(Name = "学校地址")] [Required(ErrorMessage = "学校地址不能为空")] [StringLength(200, ErrorMessage = "学校地址最大长度为200")] public string Address { get; set; } public List<Student> Students { get; set; } [Display(Name = "创建时间")] [DataType(DataType.DateTime), DisplayFormat(DataFormatString = "{0:yyyy-MM-dd HH:mm:ss}")] public DateTime CreateTime { get; set; } [Display(Name = "最后更新时间")] [DataType(DataType.DateTime), DisplayFormat(DataFormatString = "{0:yyyy-MM-dd HH:mm:ss}")] public DateTime? LastUpdateTime { get; set; } }
学生类
public class Student { [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; } [Display(Name = "学生姓名")] [Required(ErrorMessage = "学生姓名不能为空")] [StringLength(50, ErrorMessage = "学生姓名最大长度为50")] public string Name { get; set; } [Display(Name = "年龄")] [Required(ErrorMessage = "年龄不能为空")] [Range(minimum: 10, maximum: 100, ErrorMessage = "学生年龄必须在（10 ~ 100）之间")] public int Age { get; set; } public School School { get; set; } [Display(Name = "创建时间")] [DataType(DataType.DateTime), DisplayFormat(DataFormatString = "{0:yyyy-MM-dd HH:mm:ss}")] public DateTime CreateTime { get; set; } [Display(Name = "最后更新时间")] [DataType(DataType.DateTime), DisplayFormat(DataFormatString = "{0:yyyy-MM-dd HH:mm:ss}")] public DateTime? LastUpdateTime { get; set; } }
配置默认值
protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Models.School>() .Property(p => p.CreateTime) .HasDefaultValueSql("getdate()"); modelBuilder.Entity<Models.Student>() .Property(p => p.CreateTime) .HasDefaultValueSql("getdate()"); }
模型定义好之后，我们需要把模型添加到DbContext
public DbSet<Models.School> Schools { get; set; } public DbSet<Models.Student> Students { get; set; }
然后需要更新模型到数据库，执行下面两条命令
1. 新增一个迁移
dotnet ef migrations add DatabaseInit
2. 更新到数据库
dotnet ef migrations add DatabaseInit
查看数据库，我们可以看到下面关系图
在Student表里面多了一个SchoolId，这个我们是没有定义，是EF Core生成的阴影属性，当然我们也可以显示定义这个字段
实体类定义我们用到数据注释和Fluent API约束实体类生成，下面列取经常用到的
| 注释 | 用途 |
|---|---|
| Key | 主键 |
| Required | 必须 |
| MaxLength | 最大长度 |
| NotMapped | 不映射到数据库 |
| ConcurrencyCheck | 并发检查 |
|
Timestamp
|
时间戳字段 |
查询数据
一、联接查询
var query = from a in _context.School join b in _context.Student on a.Id equals b.School.Id select new { SchoolName = a.Name, StudentName = b.Name };
对应生成的Sql
SELECT [s].[Name] AS [SchoolName], [t].[Name] AS [StudentName] FROM [School] AS [s] INNER JOIN ( SELECT [s0].[Id], [s0].[Age], [s0].[CreateTime], [s0].[LastUpdateTime], [s0].[Name], [s0].[SchoolId], [s1].[Id] AS [Id0], [s1].[Address], [s1].[CreateTime] AS [CreateTime0], [s1].[LastUpdateTime] AS [LastUpdateTime0], [s1].[Name] AS [Name0] FROM [Student] AS [s0] LEFT JOIN [School] AS [s1] ON [s0].[SchoolId] = [s1].[Id] ) AS [t] ON [s].[Id] = [t].[Id0]
和我们预期有点不一致，预期是两个表的全连接，为什么出现这个，原因是Student里面的导航属性School，Linq遇到导航属性是通过连表得到，为了验证这个，我们不使用阴影属性，显示加上SchoolId试试
var query = from a in _context.School join b in _context.Student on a.Id equals b.SchoolId select new { SchoolName = a.Name, StudentName = b.Name };
对应生成的Sql
SELECT [s].[Name] AS [SchoolName], [s0].[Name] AS [StudentName] FROM [School] AS [s] INNER JOIN [Student] AS [s0] ON [s].[Id] = [s0].[SchoolId]
这次生成的Sql就很简洁，跟预期一样，所以如果使用联接查询，最好是避免使用阴影属性
两个Sql的执行计划
二、GroupBy查询
var query = from a in _context.School join b in _context.Student on a.Id equals b.SchoolId group a by a.Name into t where t.Count() > 0 orderby t.Key select new { t.Key, Count = t.Count(), };
对应生成的Sql
SELECT [s].[Name] AS [Key], COUNT(*) AS [Count] FROM [School] AS [s] INNER JOIN [Student] AS [s0] ON [s].[Id] = [s0].[SchoolId] GROUP BY [s].[Name] HAVING COUNT(*) > 0 ORDER BY [s].[Name]
EF Core 支持的聚合运算符如下所示
- 平均值
- 计数
- LongCount
- 最大值
- 最小值
- Sum
三、左连接
var query = from a in _context.School join b in _context.Student on a.Id equals b.SchoolId into t1 from t in t1.DefaultIfEmpty() select new { SchoolName = a.Name, StudentName = t.Name }; var list = query.AsNoTracking().ToList();
对应生成的Sql
SELECT [s].[Name] AS [SchoolName], [s0].[Name] AS [StudentName] FROM [School] AS [s] LEFT JOIN [Student] AS [s0] ON [s].[Id] = [s0].[SchoolId]
四、小结
全联接时避免使用导航属性连表
默认情况是跟踪查询，这表示可以更改这些实体实例，然后通过 SaveChanges() 持久化这些更改，
如果只需要读取，不需要修改可以指定非跟踪查询AsNoTracking
非跟踪查询可以在每个查询后面指定，还可以在上下文实例级别更改默认跟踪行为
context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
保存数据
一、关联数据
_context.School.Add(new Models.School { Name = "暨南大学", Address = "广州市黄埔大道西601号", Students = new System.Collections.Generic.List<Models.Student>() { new Models.Student { Name= "黄伟", Age = 21, }, }, }); _context.SaveChanges();
同时在School，Student表保存数据，自动维护Student表的SchoolId字段数据
二、级联删除
var school = _context.School.Include(m => m.Students).FirstOrDefault(m => m.Name == "济南大学"); _context.School.Remove(school); _context.SaveChanges();
对应生成的Sql
--1. 读取济南大学和他所有学生 SELECT [t].[Id], [t].[Address], [t].[CreateTime], [t].[LastUpdateTime], [t].[Name], [s0].[Id], [s0].[Age], [s0].[CreateTime], [s0].[LastUpdateTime], [s0].[Name], [s0].[SchoolId] FROM ( SELECT TOP(1) [s].[Id], [s].[Address], [s].[CreateTime], [s].[LastUpdateTime], [s].[Name] FROM [School] AS [s] WHERE [s].[Name] = N'济南大学' ) AS [t] LEFT JOIN [Student] AS [s0] ON [t].[Id] = [s0].[SchoolId] ORDER BY [t].[Id], [s0].[Id] --2. 循环每个学生删除 SET NOCOUNT ON; DELETE FROM [Student] WHERE [Id] = @p0; SELECT @@ROWCOUNT; SET NOCOUNT ON; DELETE FROM [Student] WHERE [Id] = @p0; SELECT @@ROWCOUNT; SET NOCOUNT ON; DELETE FROM [Student] WHERE [Id] = @p0; SELECT @@ROWCOUNT; --3. 删除学校 SET NOCOUNT ON; DELETE FROM [School] WHERE [Id] = @p1; SELECT @@ROWCOUNT;
级联删除要用Include把子项也包含到实体
三、使用事务
默认情况下，如果数据库提供程序支持事务，则会在事务中应用对 SaveChanges() 的单一调用中的所有更改。 如果其中有任何更改失败，则会回滚事务且所有更改都不会应用到数据库。 这意味着，SaveChanges() 可保证完全成功，或在出现错误时不修改数据库。
对于大多数应用程序，此默认行为已足够。 如果应用程序要求被视为有必要，则应该仅手动控制事务中间调用多次SaveChanges()也不会直接保存到数据库，最后transaction.Commit()
using (var transaction = _context.Database.BeginTransaction()) { var school = _context.School.Add(new Models.School { Name = "济南大学", Address = "山东省济南市南辛庄西路336号", }); _context.SaveChanges(); System.Threading.Thread.Sleep(2000); //for testing _context.Student.Add(new Models.Student { Name = "张三", Age = 29, School = school.Entity }); _context.SaveChanges(); transaction.Commit(); }
下面是Sql Server Profiler
注意两次RPC:Completed时间，每次调用SaveChanges提交到数据库执行，外面包一层事务，所以事务里面要尽可能的控制操作最少，时间最少
四、并发冲突
EF Core实现的是乐观并发，有关乐观并发和悲观并发这里就不展开。
EF处理并发分两种情况，单个属性并发检查和时间戳（又叫行版本），单个属性只保证单个字段并发修改，时间戳是保证整条数据的并发修改
我们在Student的Age加上[ConcurrencyCheck]，在School加上行版本
[ConcurrencyCheck] public int Age { get; set; }
[Timestamp] public byte[] RowVersion { get; set; }
1. 模拟Age并发冲突
var student = _context.Student.Single(m => m.Id == 1); student.Age = 32; #region 模拟另外一个用户修改了Age var task = Task.Run(() => { var options = HttpContext.RequestServices.GetService<DbContextOptions<Data.EFCoreDbContext>>(); using (var context = new Data.EFCoreDbContext(options)) { var student = context.Student.Single(m => m.Id == 1); student.Age = 23; context.SaveChanges(); } }); task.Wait(); #endregion try { _context.SaveChanges(); } catch (DbUpdateConcurrencyException ex) { _logger.LogError(ex, "database update error"); }
2. 数据库数据
可以看到是Task里面的更新成功了
3. 异常信息
Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded
异常信息描述很明确,就是数据库操作期望1行被影响,实际是0行,数据可能被修改或删除自从实体加载后
4. SQL
exec sp_executesql N'SET NOCOUNT ON; UPDATE [Student] SET [Age] = @p0 WHERE [Id] = @p1 AND [Age] = @p2; SELECT @@ROWCOUNT; ',N'@p1 int,@p0 int,@p2 int',@p1=1,@p0=23,@p2=25 exec sp_executesql N'SET NOCOUNT ON; UPDATE [Student] SET [Age] = @p0 WHERE [Id] = @p1 AND [Age] = @p2; SELECT @@ROWCOUNT; ',N'@p1 int,@p0 int,@p2 int',@p1=1,@p0=32,@p2=25
加上并发检查的字段会在where条件后面加上原始值，Timestamp也是一样道理，只是Timestamp是每次(插入/更新)数据库会更新这个字段,数字递增的形式。
5. 解决并发冲突
要解决上面冲突，先要介绍EF Core里面三组数值
原始值：实体从数据库加载时的值 (例子：Age = 25)
当前值：实体当前的值 (例子：Age = 32)
数据库值：当前数据库中的值 (例子：Age = 23)
public void SaveToDb() { var student = _context.Student.Single(m => m.Id == 1); student.Age = 32; //模拟另外一个用户修改了Age var task = Task.Run(() => { var options = HttpContext.RequestServices.GetService<DbContextOptions<Data.EFCoreDbContext>>(); using (var context = new Data.EFCoreDbContext(options)) { var student = context.Student.Single(m => m.Id == 1); student.Age = 23; context.SaveChanges(); } }); task.Wait(); //到这，另外一个线程已经将Age修改成23 var trySave = 0; //若并发冲突异常，重试3次 while (trySave++ < 3) { if (TrySaveData()) break; } bool TrySaveData() { try { _context.SaveChanges(); return true; } catch (DbUpdateConcurrencyException ex) { _logger.LogError(ex, $"database update concurrency exception : retry: {trySave}"); //3次尝试保存失败，抛出异常等上层处理，不应该吃掉异常，不然返回成功，实际保存没成功 if (trySave >= 3) throw ex; //若冲突不是当前处理的对象，抛出异常等上层处理 if (!ex.Entries.Any(m => m.Entity is Models.Student)) throw ex; var entry = ex.Entries.Select(m => m).Single(m => m.Entity is Models.Student); //获取当前实体值 var currentValues = entry.CurrentValues; //获取数据库值 var databaseValues = entry.GetDatabaseValues(); //这里获取当前需要修改的字段 var property = currentValues.Properties.FirstOrDefault(m => m.Name == nameof(student.Age)); var currentValue = currentValues[property]; var databaseValue = databaseValues[property]; //这里赋值多个选择方案，1. 使用当前值 2. 使用数据库值 3. 处理后的值（例如余额，数据库余额 - 当前余额 & 大于0) currentValues[property] = currentValue; // 刷新原始值，这里原始值是做并发检查 entry.OriginalValues.SetValues(databaseValues); return false; } } }
数据库更新为我们预期的值
转发请标明出处：https://www.cnblogs.com/WilsonPan/p/11792722.html
示例代码：https://github.com/WilsonPan/AspNetCoreExamples/tree/master/EFCore
