标签：Create 改查 Update DbContext 查询 context SQL var new

更新记录
转载请注明出处：
2022年10月19日 发布。
2022年10月10日 从笔记迁移到博客。

增删改查

增删改说明

对实体进行增删改操作，会改变实体的EntityState属性

插入（Insert）操作

常用API

DbContext Methods	DbSet Methods	Description
DbContext.Attach	DbSet.Attach	Attach an entity to DbContext. Set Unchanged state for an entity whose Key property has a value and Added state for an entity whose Key property is empty or the default value of data type.
DbContext.Add	DbSet.Add	Attach an entity to DbContext with Added state.
DbContext.AddRange	DbSet.AddRange	Attach a collection of entities to DbContext with Added state.
DbContext.Entry	-	Gets an EntityEntry for the specified entity which provides access to change tracking information and operations.
DbContext.AddAsync	DbSet.AddAsync	Asynchronous method for attaching an entity to DbContext with Added state and start tracking it if not. Data will be inserted into the database when SaveChangesAsync() is called.
DbContext.AddRangeAsync	DbSet.AddRangeAsync	Asynchronous method for attaching multiple entities to DbContext with Added state in one go and start tracking them if not. Data will be inserted into the database when SaveChangesAsync() is called.

插入关联的实体数据

var stdAddress = new StudentAddress()
{
    City = "SFO",
    State = "CA",
    Country = "USA"
};

var std = new Student()
{
    Name = "Steve",
    Address = stdAddress
};
using (var context = new SchoolContext())
{
    // Attach an entity to DbContext with Added state
    context.Add<Student>(std);

    // Calling SaveChanges to insert a new record into Students table
    context.SaveChanges();
}

添加多条数据

var studentList = new List<Student>() {
    new Student(){ Name = "Bill" },
    new Student(){ Name = "Steve" }
};

using (var context = new SchoolContext())
{
    context.AddRange(studentList);
    context.SaveChanges();
}

一次性添加不同类型的实体

var std1 = new Student(){ Name = "Bill" };
var std2 = new Student(){ Name = "Steve" };
var computer = new Course() { CourseName = "Computer Science" };
var entityList = new List<Object>() {
    std1,
    std2,
    computer
};

using (var context = new SchoolContext())
{                
    context.AddRange(entityList);

    // or 
    // context.AddRange(std1, std2, computer);

    context.SaveChanges();
}

在DbSet上插入数据

var std = new Student()
{
    Name = "Bill"
};

using (var context = new SchoolContext())
{
    context.Students.Add(std);

    // or
    // context.Students.Attach(std);

    context.SaveChanges();
}

删除（Delete）数据

常用API

DbContext Methods	DbSet Methods	Description
DbContext.Remove	DbSet.Remove	Attaches the specified entity to the DbContext with Deleted state and starts tracking it.
DbContext.RemoveRange	DbSet.RemoveRange	Attaches a collection or array of entities to the DbContext with Deleted state and starts tracking them.

删除数据-直接在上下文对象上操作

// entity to be deleted
var student = new Student() {
        StudentId = 1
};

using (var context = new SchoolContext()) 
{
    context.Remove<Student>(student);
   
    // or the followings are also valid
    // context.RemoveRange(student);
    //context.Students.Remove(student);
    //context.Students.RemoveRange(student);
    //context.Attach<Student>(student).State = EntityState.Deleted;
    //context.Entry<Student>(student).State = EntityState.Deleted;
    
    context.SaveChanges();
}

删除数据-直接在上下文对象上操作

var student = new Student() {
    StudentId = 50
};

using (var context = new SchoolContext()) {

    context.Remove<Student>(student);

    context.SaveChanges();
}

删除操作-检测数据不存在的情况

var student = new Student() {
    StudentId = 50
};

using (var context = new SchoolContext()) 
{
    try
    {
        context.Remove<Student>(deleteStudent);
        context.SaveChanges();
    }    
    catch (DbUpdateConcurrencyException ex)
    {
        throw new Exception("Record does not exist in the database");
    }
    catch (Exception ex)
    {
        throw;
    }
}

删除数据-直接设置实体的状态

using (var db = new EmployeeDbContext())
{
    Employee employeeToBeDeleted = db.Employees
                                        .Where(emp => emp.EmployeeId == 1)
                                        .FirstOrDefault();
    if (employeeToBeDeleted != null)
    {
        db.Entry(employeeToBeDeleted).State = Microsoft.EntityFrameworkCore.EntityState.Deleted;
        int recordsDeleted = db.SaveChanges();
        Console.WriteLine("Number of records deleted:" + recordsDeleted);
        Console.ReadLine();
    }
}

删除多条数据

IList<Student> students = new List<Student>() {
    new Student(){ StudentId = 1 },
    new Student(){ StudentId = 2 },
    new Student(){ StudentId = 3 },
    new Student(){ StudentId = 4 }
};

using (var context = new SchoolContext()) 
{
    context.RemoveRange(students);
    
    // or
    // context.Students.RemoveRange(students);
    
    context.SaveChanges();
}

删除多表多条数据（使用事务）

static void DeleteEmployee()
{
    using (var db = new EmployeeDbContext())
    using(var transaction = db.Database.BeginTransaction())
    {
        Employee employeeToBeDeleted = db.Employees
                                        .Where(emp => emp.EmployeeId == 1)
                                        .FirstOrDefault();
        if (employeeToBeDeleted != null)
        {
            db.Entry(employeeToBeDeleted).State = Microsoft.EntityFrameworkCore.EntityState.Deleted;
            int recordsDeleted = db.SaveChanges();
            Console.WriteLine("Number of records deleted:" + recordsDeleted);
            Console.ReadLine();
        }
        transaction.Commit();
    }
}

配置级联删除-使用Fluent API（Configure Cascade Delete using Fluent API）

删除父表的时候，同时删除子表的数据

学生实体：

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    //外键导航属性
    public int CurrentGradeId { get; set; }
    //导航属性
    public Grade Grade { get; set; }
}

年级实体：

public class Grade
{
    public int GradeId { get; set; }
    public string GradeName { get; set; }
    public string Section { get; set; }
    //集合导航属性
    public ICollection<Student> Students { get; set; }
}

定义FluentAPI

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Grade>()
        .HasMany<Student>(g => g.Students)  //（多）年级实体对应多个学生
        .WithOne(s => s.Grade)              //（一）学生实体只有一个年级导航属性
        .HasForeignKey(s => s.CurrentGradeId)   //学生实体有一个外键导航属性
        .OnDelete(DeleteBehavior.Cascade);      //定义级联删除
}

OnDelete方法使用DeleteBehavior参数，持以下枚举值：

DeleteBehavior.Cascade         //删除主体实体时，从属实体将被删除
DeleteBehavior.ClientSetNull   //依赖实体中外键属性的值将设置为null
DeleteBehavior.SetNull         //依赖实体中外键属性的值将设置为null
DeleteBehavior.Restrict        //防止级联删除

更新（Update）操作

常用API

DbContext Methods	DbSet Methods	Description
DbContext.Update	DbSet.Update	Attach an entity to DbContext with Modified state.
DbContext.UpdateRange	DbSet.UpdateRange	Attach collection of entities to DbContext with Modified state.

更新数据-直接在上下文对象上操作

// Disconnected Student entity
var stud = new Student(){ StudentId = 1, Name = "Bill" };

stud.Name = "Steve"; 

using (var context = new SchoolContext())
{
    context.Update<Student>(stud);

    // or the followings are also valid
    // context.Students.Update(stud);
    // context.Attach<Student>(stud).State = EntityState.Modified;
    // context.Entry<Student>(stud).State = EntityState.Modified; 

    context.SaveChanges(); 
}

更新数据-直接在上下文对象上操作

var newStudent = new Student()
{
    Name = "Bill"
};

var modifiedStudent = new Student()
{
    StudentId = 1,
    Name = "Steve"
};

using (var context = new SchoolContext())
{
    context.Update<Student>(newStudent);
    context.Update<Student>(modifiedStudent);
}

一次性更新多条数据

var modifiedStudent1 = new Student()
{
    StudentId = 1,
    Name = "Bill"
};
var modifiedStudent2 = new Student()
{
    StudentId = 3,
    Name = "Steve"
};
var modifiedStudent3 = new Student()
{
    StudentId = 3,
    Name = "James"
};
IList<Student> modifiedStudents = new List<Student>()
{
    modifiedStudent1,
    modifiedStudent2,
    modifiedStudent3
};

using (var context = new SchoolContext())
{
    context.UpdateRange(modifiedStudents);
    
    // or the followings are also valid
    //context.UpdateRange(modifiedStudent1, modifiedStudent2, modifiedStudent3);
    //context.Students.UpdateRange(modifiedStudents);
    //context.Students.UpdateRange(modifiedStudent1, modifiedStudent2, modifiedStudent3);
                
    context.SaveChanges();
}

查询（Update）操作

级联查询（如何让子表关联数据也被同时查询出来）

使用Include()方法

//注意：记得加上引用using Microsoft.EntityFrameworkCore;
var result = applicationDbContext.Person
                                .Include(p => p.Addresses)
                                .Select(i => i);

foreach (var item in result)
{
    Console.WriteLine(item.Id);
    Console.WriteLine(item.Name);
    Console.WriteLine(item.Age);
    //输出关联的子表内容
    Console.WriteLine("===输出关联的子表内容===");
    foreach (var item2 in item.Addresses)
    {
        Console.WriteLine(item2.Id);
        Console.WriteLine(item2.Detial);
    }
    Console.WriteLine("===输出关联的子表内容===");
}

使用ThenIncludeInclude()方法

在Include()的基础上对Include的实体的字段继续去引用其他表

//注意：记得加上引用using Microsoft.EntityFrameworkCore;
var result = applicationDbContext.Person
                                .Include(p => p.Addresses)
                                .ThenInclude(i=>i.Detial)
                                .Select(i => i);

而不是直接返回记录

foreach (var item in DbContext.Person.Select(items =>new {
                                                    items.Id,
                                                    items.Name,
                                                    items.Addresses
                                                }))

原生SQL

使用EF Core API执行原生SQL

使用Database的ExecuteSqlRaw()方法

实例：

rowsAffected = BlogContext.Database.ExecuteSqlRaw("select * from t1 where id = @p0", _id);

实例：

_context.Database.ExecuteSqlRaw("AddLookUpItem @p0,@p1,@p2", new object[] { code, description, lookUpType });

使用Database的ExecuteSqlInterpolated()方法

使用上基本和ExecuteSqlRaw()相同，但是参数使用的是插值字符串

实例：

rowsAffected = BlogContext.Database.ExecuteSqlInterpolated($"select * from t1 where id = {id}");

使用DbSet的FromSqlRaw()方法

先要安装EF Core 和 引入命名空间

using Microsoft.EntityFrameworkCore;

简单无参数的SQL语句

//执行查询语句
var result = dbContext.Posts.FromSqlRaw("Select * from dbo.Posts");

带参数的SQL语句（简单形式）

注意：不建议使用简单形式，容易造成SQL注入问题

int id = 666;
var result3 = dbContext.Posts.FromSqlRaw("Select * from dbo.Blog WHEREId = {0}", id);

带参数的SQL语句（参数绑定形式）

int id = 666;
var result = dbContext.Posts
    .FromSqlRaw("Select * from dbo.Blog WHERE Id = @id", new SqlParameter("id", id))
    .FirstOrDefault();

还可以和LINQ混合使用

var result = dbContext.Posts
            .FromSqlRaw("Select * from dbo.Blog")
            .Where(x => x.Title == "Panda Article")
            .OrderByDescending(x => x.Id)
            .ToList();

使用DbSet的FromSqlInterpolated()方法

使用上基本和FromSqlRaw()相同，但是参数使用的是插值字符串。

FromSqllnterpolated()方法的返回值是IQueryable类型的，因此我们可以在实际执行IQueryable之前，对IQueryable进行进一步的处理。

实例：

context.Blogs.FromSqlInterpolated($"SELECT * FROM dbo.Blogs").OrderBy(b => b.Name)

实例：

context.Blogs.FromSqlInterpolated($"SELECT * FROM [dbo].[SearchBlogs]({userSuppliedSearchTerm})")

EF Core API执行原生SQL的限制

SQL查询必须返回实体类型对应数据库表的所有列；
结果集中的列名必须与属性映射到的列名称匹配。
只能单表查询，不能使用Join语句进行关联查询。但是可以在查询后面使用Include()来进行关联数据的获取。

EF Core执行原生SQL的技巧

EF Core中允许把视图或存储过程映射为实体，因此可以把复杂的查询语句写成视图或存储过程，然后再声明对应的实体类，并且在DbContext中配置对应的DbSet。

使用ADO.NET执行原生SQL

就是ADO.NET的语法，这里不说了。

dbCxt.Database.GetDbConnection();		//获得ADO.NET Core的数据库连接对象

EF Core 批量删除、修改数据

当前EF Core还不支持批量删除、修改数据，所以可以考虑使用SQL语句批量进行这样的操作。

或者使用这个包：https://github.com/yangzhongke/Zack.EFCore.Batch

查看生成的SQL语句

使用ToQueryString()方法

EF Core的Where方法返回的是IQueryable类型，DbSet也实现了IQueryable接口。IQueryable有扩展方法ToQueryString()可以获得SQL，不需要真的执行查询才获取SQL语句。

注意：只能获取查询操作的。

//注意：引入命名空间 using Microsoft.EntityFrameworkCore;
using(PandaDbContext db = new PandaDbContext())
{
    //获得查询的SQL语句
    string sqlString = db.Students
        .Where(i => i.Age > 888)
        .Select(i => i.Name)
        .ToQueryString();

    //输出SQL字符串
    Console.WriteLine(sqlString);
}

使用EF Core的日志

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    //定义连接字符串
    string connectionString = "Server=.;Database=PandaTest;User Id=sa;Password=.;";
    optionsBuilder.UseSqlServer(connectionString);

    //注册日志并直接输出到控制台
    optionsBuilder.LogTo((msg) => Console.WriteLine(msg));
}

使用SQL Server Profiler查看SQLServer

查询细节

IEnumerable和IQueryable区别

对普通集合和DbSet调用的LINQ方法，虽然用起来一样，但是“转到定义”后看到的是不同的方法。
普通集合的版本（IEnumerable)是在内存中过滤（客户端评估），而IQueryable版本则是把查询操作翻译成SQL语句（服务器端评估）。

虽然看起来类似，但在底层IQueryable是在数据库端进行查询，IEnumerable是在本地内存中进行查询。

//使用IQueryable查询
IQueryable<Book> books = ctx.Books;
books.Where(b => b.Price > 1.1)
//使用IEnumerable查询
IEnumerable<Book> books = ctx.Books;
books.Where(b => b.Price > 1.1)

强制将数据库端查询转为本地内存中查询

//强制将数据库端查询转为本地内存中查询
var cmts = IEnumerable<Comment>(ctx.Comments.Select(c => new { Id = c.Id, Pre = c.Message.Substring(0,2) + "..." }));
foreach (var c in cmts)
{
    Console.WriteLine(c.Id + c.Pre);
}

IQueryable延迟执行

IQueryable只是代表一个“可以放到数据库服务器去执行的查询”，它没有立即执行，只是“可以被执行”而已。
对于IQueryable接口调用非终结方法的时候不会执行查询，而调用终结方法的时候则会立即执行查询。
终结方法：遍历、ToArray()、ToList()、Min()、Max()、Count()等；非终结方法：GroupBy()、OrderBy()、Include()、Skip()、Take()等。
简单判断：一个方法的返回值类型如果是IQueryable类型，那么这个方法一般就是非终结方法，否则就是终结方法。

IQueryable代表一个对数据库中数据进行查询的一个逻辑，这个查询是一个延迟查询。我们可以调用非终结方法向IQueryable中添加查询逻辑，当执行终结方法的时候才真正生成SQL语句来执行查询。
可以实现以前要靠SQL拼接实现的动态查询逻辑。

EF Core 分页查询公式

分页获得数据：

var items = arts.Skip((pageIndex - 1) * pageSize).Take (pageSize) ;

总页数：

long pageCount =(long)Math.Ceiling(count * 1.0 / pageSize);

IQueryable底层是如何读取数据的

EF Core底层依赖ADO.NET，ADO.NET读取数据的方式：

DataReader：分批从数据库服务器读取数据。内存占用小、DB连接占用时间长。
DataTable：把所有数据都一次性从数据库服务器都加载到客户端内存中。内存占用大，节省DB连接。

IQueryable内部就是在调用DataReader。
优点：节省客户端内存。
缺点：如果处理的慢，会长时间占用连接。

EF Core 如何一次性加载数据到内存

一次性加载数据到内存：用IQueryable的ToArray()、ToArrayAsync()、ToList()、ToListAsync()等方法。等ToArray()执行完毕，再断服务器试一下。

EF Core 何时需要一次性加载

场景1:遍历IQueryable并且进行数据处理的过程很耗时。
场景2:如果方法需要返回查询结果，并且在方法里销毁
DbCohtext的话，是不能返回IQueryable的。必须一次性加载返回。
场景3:多个IQueryable的遍历嵌套。很多数据库的ADO.NET Core Provider是不支持多个DataReader同时执行的。把连接字符串中的MultipleActiveResultSets=true删掉，其他数据库不支持这个。

优先考虑使用EF Core+LINQ，而不是SQL语句

原生SQL语句需要把表名、列名等硬编码到SQL语句中，不符合模型驱动、分层隔离等思想，程序员直接面对数据库表，无法利用EF Core强类型的特性，如果模型发生改变，必须手动变更SQL语句。
使用SQL语句无法利用EF Core强大的SQL翻译机制来屏蔽不同底层数据库的差异。

全局查询筛选器

说明

全局查询筛选器：EF Core会自动将这个查询筛选器应用于涉及这个实体类型的所有LINQ查询。

场景：软删除、多租户。

实现

在实体配置中使用HasQueryFilter()方法即可。

builder.HasQueryFilter(b=>b.IsDeleted==false);

实例：配置全局查询筛选器进行过滤已经删除的数据。

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace PandaTest
{
    /// <summary>
    /// 人员实体配置
    /// </summary>
    public class PersonEntityConfig : IEntityTypeConfiguration<Person>
    {
        public void Configure(EntityTypeBuilder<Person> builder)
        {
            //设置数据默认不是删除状态
            builder.Property(i=>i.IsDeleted).HasDefaultValue(false);
            //过滤掉已经删除的数据
            builder.HasQueryFilter(i => i.IsDeleted == false);
        }
    }
}

标签：Create,改查,Update,DbContext,查询,context,SQL,var,new
From： https://www.cnblogs.com/cqpanda/p/16797931.html