【ASP.NET Core学习】Entity Framework Core

 这里介绍在asp.net core中使用ef core，这里数据库选的是sql server

1. 如何使用sql server
2. 添加模型 && 数据库迁移
3. 查询数据
4. 保存数据

如何使用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)

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

var (trysave, issave) = (0, false);

while (!issave && trysave++ < 3)
{
    try
    {
        _context.savechanges();
    }
    catch (dbupdateconcurrencyexception ex)
    {
        _logger.logerror(ex, "database update error");

        foreach (var entry in ex.entries)
        {
            if (entry.entity is models.student)
            {
                var currentvalues = entry.currentvalues;
                var databasevalues = entry.getdatabasevalues();

                foreach (var property in currentvalues.properties)
                {
                    var currentvalue = currentvalues[property];
                    var databasevalue = databasevalues[property];

                    //这里选择保存哪个值，这里简单选择当前（30）保存到数据库，实际可能还需处理，如余额，就需要数据库当前余额 - 当前数值
                    currentvalues[property] = currentvalue;
                }
                // 刷新原始值
                entry.originalvalues.setvalues(databasevalues);
            }
        }
    }
}

 

数据库更新为我们预期的值

 

转发请标明出处：https://www.cnblogs.com/wilsonpan/p/11792722.html

示例代码：https://github.com/wilsonpan/aspnetcoreexamples/tree/master/efcore