【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(, ErrorMessage = "学校名称最大长度为100")]

    public string Name { get; set; }

    [Display(Name = "学校地址")]

    [Required(ErrorMessage = "学校地址不能为空")]

    [StringLength(, 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(, ErrorMessage = "学生姓名最大长度为50")]

    public string Name { get; set; }

    [Display(Name = "年龄")]

    [Required(ErrorMessage = "年龄不能为空")]

    [Range(minimum: , maximum: , 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

查看数据库，我们可以看到下面关系图

【ASP.NET Core学习】Entity Framework Core-LMLPHP

在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的执行计划

【ASP.NET Core学习】Entity Framework Core-LMLPHP

二、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 = ,

        },

    },

});

_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();  //for testing

    _context.Student.Add(new Models.Student

    {

        Name = "张三",

        Age = ,

        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 == );

student.Age = ;

#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 == );

        student.Age = ;

        context.SaveChanges();

    }

});

task.Wait();

#endregion

try

{

    _context.SaveChanges();

}

catch (DbUpdateConcurrencyException ex)

{

    _logger.LogError(ex, "database update error");

}

2. 数据库数据

【ASP.NET Core学习】Entity Framework Core-LMLPHP

可以看到是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 == );

    student.Age = ;

    //模拟另外一个用户修改了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 == );

            student.Age = ;

            context.SaveChanges();

        }

    });

    task.Wait();

    //到这，另外一个线程已经将Age修改成23

    var trySave = ;

    //若并发冲突异常，重试3次

    while (trySave++ < )

    {

        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 >= ) 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