第五单元数据查询

Entity Framework Core 使用语言集成查询 (LINQ) 来查询数据库中的数据。通过 LINQ 可使用 C#（或你选择的其他 .NET 语言）编写强类型查询。它使用你派生得到的上下文和实体类来引用数据库对象。 EF Core 将 LINQ 查询的表示形式传递给数据库提供程序。反过来，数据库提供程序将其转换为数据库特定的查询语言（例如，用于关系数据库的 SQL）。即使结果中返回的实体已存在于上下文中，也始终对数据库执行查询。

1. 查询数据

1. 入门

加载所有数据

using (var context = new BloggingContext())
{
    var blogs = context.Blogs.ToList();
}

加载单个实体

using (var context = new BloggingContext())
{
    var blog = context.Blogs
        .Single(b => b.BlogId == 1);
}

筛选

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Where(b => b.Url.Contains("dotnet"))
        .ToList();
}

2. 查询追踪

EFCore通过一种机制实时追踪实体的属性是否有改变的一种机制，如果实体属性发生改变，在调用SaveChange() 方法时，会更新实体属性对应的表字段。

如何跟踪实体

实体实例在以下情况下会被跟踪：

从针对数据库执行的查询返回
通过 Add、Attach、Update 或类似方法显示附加到 DbContext
检测为连接到现有跟踪实体的新实体

实体实例在以下情况下不再被跟踪：

DbContext 已释放
更改跟踪器已被清除（EF Core 5.0 及更高版本）
显式使用非跟踪查询

DbContext 旨在表示短期工作单元，如 DbContext 初始化和配置中所述。这意味着释放 DbContext 是停止跟踪实体的正常方式。换句话说，DbContext 的生存期应为：

创建 DbContext 实例
跟踪某些实体
对实体进行一些更改
调用 SaveChanges 以更新数据库
释放 DbContext 实例

提示

采用此方法时，无需清除更改跟踪器或显式拆离实体实例。但是，如果确实需要拆离实体，则调用 ChangeTracker.Clear 比逐个拆离实体更有效。

实体状态

create database efcore_demo default character set utf8;
use efcore_demo;

create table role
(
    role_id int primary key auto_increment comment '主键',
    role_name varchar(30) comment '角色名称',
    passport_id int comment '账户编号',     
    grade int comment '角色等级',
    create_time datetime comment '创建日期'
) comment '角色表' character set utf8;

Create table role_login_log
(
  log_id int auto_increment primary key comment '登录日志编号',
    passport_id int comment '账户编号', 
    role_id int comment '角色编号',
    login_time datetime comment '登录时间',
    device_cpu varchar(30)  comment '登录设置CPU',
    device_brand varchar(30) comment '登录设置品牌'
) character set utf8 comment '登录日志表';

create table passport
(
    passport_id int primary key auto_increment comment '账户编号',
    user_name varchar(30) comment '账户名称',
    `password` varchar(30) comment '账户密码'
) character set utf8 comment '账户表';

create table bill
(
    bill_id int auto_increment primary key comment '充值记录编号',
    passport_id int comment '账户编号',
    role_id int comment '角色编号',
    cash_type varchar(30) comment '充值方式',
    fee decimal(10,2) comment '充值金额',
    pay_time datetime comment '充值日期'
) CHARACTER set utf8 comment '充值记录表';

insert into passport(user_name,`password`) values
('zhangsan','123'),
('lisi','123'),
('wangwu','123'),
('zhaoliu','123');

insert into role(role_name,passport_id,grade,create_time) values
('孙悟空',1,10,now()),
('花木兰',2,20,now()),
('西楚霸王',3,30,now()),
('曹操',4,30,now()),
('项羽',4,30,now());

insert into role_login_log(passport_id,role_id,login_time,device_cpu,device_brand) values
(1,1,now(),'高通骁龙630','小米'),
(2,2,now(),'麒麟630','华为'),
(3,3,now(),'高通骁龙630','锤子'),
(4,4,now(),'A11','苹果'),
(4,5,now(),'A11','苹果');
INSERT INTO `efcore_demo`.`role_login_log`(`log_id`, `passport_id`, `role_id`, `login_time`, `device_cpu`, `device_brand`) VALUES (6, 4, 2, '2022-12-03 20:49:44', 'A12', '苹果1');
INSERT INTO `efcore_demo`.`role_login_log`(`log_id`, `passport_id`, `role_id`, `login_time`, `device_cpu`, `device_brand`) VALUES (7, 4, 2, '2022-12-03 20:49:44', 'A12', '苹果2');
INSERT INTO `efcore_demo`.`role_login_log`(`log_id`, `passport_id`, `role_id`, `login_time`, `device_cpu`, `device_brand`) VALUES (8, 4, 2, '2022-12-03 20:49:44', 'A12', '苹果3');
INSERT INTO `efcore_demo`.`role_login_log`(`log_id`, `passport_id`, `role_id`, `login_time`, `device_cpu`, `device_brand`) VALUES (9, 4, 2, '2022-12-03 20:49:44', 'A12', '苹果4');

insert into bill(passport_id,role_id,cash_type,fee,pay_time) values
(1,1,'支付宝',50,now()),
(2,2,'微信',150,now()),
(3,3,'QQ',250,now()),
(4,4,'微信',150,now()),
(4,5,'QQ',250,now()),
(2,2,'微信',150,now()),
(3,3,'QQ',250,now());

个实体都与给定 EntityState 相关联：

Detached 实体未被 DbContext 跟踪。
Added 实体是新实体，并且尚未插入到数据库中。这意味着它们将在调用 SaveChanges 时插入。
Unchanged 实体自从数据库中查询以来尚未进行更改。从查询返回的所有实体最初都处于此状态。
Modified 实体自从数据库中查询以来已进行更改。这意味着它们将在调用 SaveChanges 前。
Deleted 实体存在于数据库中，但标记为在调用 SaveChanges 前。

EF Core 跟踪属性级别的更改。例如，如果只修改单个属性值，则数据库更新将仅更改该值。但是，当实体本身处于“已修改”状态时，只能将属性标记为已修改。（或者，从另一角度来看，“已修改”状态意味着至少有一个属性值已标记为已修改。）

下表汇总了不同的状态：

实体状态	由 DbContext 跟踪	存在于数据库中	属性已修改	SaveChanges 上的操作
`Detached`	否	-	-	-
`Added`	是	否	-	插入
`Unchanged`	是	是	否	-
`Modified`	是	是	是	更新
`Deleted`	是	是	-	删除

备注

为清楚起见，此文本使用了关系数据库术语。 NoSQL 数据库通常支持类似操作，但可能具有不同的名称。有关详细信息，请查阅数据库提供程序文档。

默认情况下，跟踪返回实体类型的查询。这表示可以更改这些实体实例，然后通过 SaveChanges() 持久化这些更改。在以下示例中，将检测到对博客分级所做的更改，并在 SaveChanges() 期间将这些更改永久保存到数据库中。

var blog = context.Blogs.SingleOrDefault(b => b.BlogId == 1);
blog.Rating = 5;  // 修改了某值
context.SaveChanges();

在跟踪查询中返回结果时，EF Core 将检查上下文中是否已存在实体。如果 EF Core 找到现有的实体，则返回同样的实例。 EF Core 不会用数据库值覆盖该实体中实体属性的当前值和原始值。如果未在上下文中找到该实体，EF Core 将创建新的实体实例，并将其附加到上下文。查询结果不会包含任何已添加到上下文但尚未保存到数据库中的实体。

非跟踪查询

在只读方案中使用结果时，非跟踪查询十分有用。可以更快速地执行非跟踪查询，因为无需设置更改跟踪信息。 如果不需要更新从数据库中检索到的实体，则应使用非跟踪查询。可以将单个查询替换为非跟踪查询。非跟踪查询也会根据数据库中的内容提供结果，但不考虑本地更改或已添加的实体。

var blogs = context.Blogs
    .AsNoTracking()
    .ToList();

还可以在上下文实例级别更改默认跟踪行为：

context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

var blogs = context.Blogs.ToList();

配置默认跟踪行为

如果你发现自己更改了许多查询的跟踪行为，则建议改为更改默认值：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFQuerying.Tracking;Trusted_Connection=True")
        .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}

默认情况下，这会使所有查询都不被跟踪。仍可添加 AsTracking() 来进行特定查询跟踪。

跟踪和自定义投影

即使查询的结果类型不是实体类型，默认情况下 EF Core 也会跟踪结果中包含的实体类型。在以下返回匿名类型的查询中，会跟踪结果集中 Blog 的实例。

var blog = context.Blogs
    .Select(
        b =>
            new { Blog = b, PostCount = b.Posts.Count() });

如果结果集包含来自 LINQ 组合的实体类型，EF Core 将跟踪它们。

var blog = context.Blogs
    .Select(
        b =>
            new { Blog = b, Post = b.Posts.OrderBy(p => p.Rating).LastOrDefault() });

如果结果集不包含任何实体类型，则不会执行跟踪。在以下查询中，我们返回匿名类型（具有实体中的某些值，但没有实际实体类型的实例）。查询中没有任何被跟踪的实体。

var blog = context.Blogs
    .Select(
        b =>
            new { Id = b.BlogId, b.Url });

2. 数据加载

Entity Framework Core 允许你在模型中使用导航属性来加载相关实体。有三种常见的 O/RM 模式可用于加载关联数据。

预先加载表示从数据库中加载关联数据，作为初始查询的一部分。
显式加载表示稍后从数据库中显式加载关联数据。
延迟加载表示在访问导航属性时，从数据库中以透明方式加载关联数据。

1. 预先加载

// 博客
public class Blog
{
    public int BlogId { get; set; }
    public string? Url { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime LastUpdated { get; set; }
    public List<Post>? Posts { get; set; }
    
    public int OwnerId { get; set; }
    public Owner? Owner { get; set; }
}

// 海报
public class Post
{
    public int PostId { get; set; }
    public string? Title { get; set; }
    public string? Content { get; set; }

    public int BlogId { get; set; }
    public Blog? Blog { get; set; }

    public int AuthorId { get; set; }
    public Author? Author { get; set; }
    public List<Tag>? Tags { get; set; }
}

public class Tag
{
    public int TagId { get; set; }
    public string? TagName { get; set; }
    public int PostId { get; set; }
}

// 资源拥有者
public class Owner
{
    public int OwnerId { get; set; }
    [Required]
    public string? OwnerName { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime LastUpdated { get; set; }

    public List<Blog>? Blogs { get; set; }

    public int PhotoId { get; set; }
    public Photo? Photo { get; set; }
}

// 作者
public class Author
{
    public int AuthorId { get; set; }
    public string? AuthorName { get; set; }
    
    public int PhotoId { get; set; }
    public Photo? Photo { get; set; }
}

public class Photo
{
    public int PhotoId { get; set; }
    public string? Url { get; set; }
    public string? ResourceName { get; set; }
    
}


// 在你自己的上下文中添加如下种子数据
protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Photo>(b =>
        {
            b.ToTable("photo");
            b.HasData(new Photo() {PhotoId = 1, ResourceName = "帅气的头像", Url = "https://fastdfs.com/1.png"});
        });
        modelBuilder.Entity<Owner>(b =>
        {
            b.ToTable("owner");
            b.HasData(new Owner() {OwnerId = 1, OwnerName = "新浪", PhotoId = 1});
        });
        
        modelBuilder.Entity<Author>(b =>
        {
            b.ToTable("author");
            b.HasData(new Author {AuthorId = 1, AuthorName = "任我行", PhotoId = 1});

        });

        modelBuilder.Entity<Blog>(b =>
        {
            b.ToTable("blog");
            b.HasData(new Blog{ BlogId = 1, Url = "www.baidu.com", OwnerId = 1 });
            b.HasData(new Blog{ BlogId = 2, Url = "www.sina.com", OwnerId = 1 });
        });
    
        
        modelBuilder.Entity<Post>(b =>
        {
            b.ToTable("post");
            b.HasData(new Post{ PostId = 1,AuthorId = 1,BlogId = 1,Title = "国足加油",Content = "希望国足好好踢球"});
            b.HasData(new Post{ PostId = 2,AuthorId = 1,BlogId = 1,Title = "任我行加油",Content = "希望任我行好好出教程"});
        });

        modelBuilder.Entity<Tag>(b =>
        {
            b.ToTable("tag");
            b.HasData(new List<Tag>
            {
                new(){ TagId = 1,PostId = 1,TagName = "体育"},
                new(){ TagId = 2,PostId = 1,TagName = "爱国"},
                new(){ TagId = 3,PostId = 2,TagName = "IT"},
                new(){ TagId = 4,PostId = 2,TagName = "编程"}
            });
        });
    }
 

可以使用 Include 方法来指定要包含在查询结果中的关联数据。 在以下示例中，结果中返回的blogs将使用关联的posts填充其 Posts 属性。

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Include(blog => blog.Posts)
        .ToList();
}
可以在单个查询中包含多个关系的关联数据。

 

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Include(blog => blog.Posts)
        .Include(blog => blog.Owner)
        .ToList();
}

注意

基于在单个查询中加载集合导航可能会导致性能问题。有关详细信息，请参阅单个查询和拆分查询。

包含多个层级

使用 ThenInclude 方法可以依循关系包含多个层级的关联数据。以下示例加载了所有博客、其关联文章及每篇文章的作者。

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Include(blog => blog.Posts)
        .ThenInclude(post => post.Author)
        .ToList();
}

可通过链式调用 ThenInclude，进一步包含更深级别的关联数据。

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Include(blog => blog.Posts)
        .ThenInclude(post => post.Author)
        .ThenInclude(author => author.Photo)
        .ToList();
}

可以将对来自多个级别和多个根的关联数据的所有调用合并到同一查询中。

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Include(blog => blog.Posts)
        .ThenInclude(post => post.Author)
        .ThenInclude(author => author.Photo)
        .Include(blog => blog.Owner)
        .ThenInclude(owner => owner.Photo)
        .ToList();
}

Include 过滤

在应用包含功能来加载相关数据时，可对已包含的集合导航应用某些可枚举的操作，这样就可对结果进行筛选和排序。

支持的操作包括：Where、OrderBy、OrderByDescending、ThenBy、ThenByDescending、Skip 和 Take。

应对传递到 Include 方法的 Lambda 中的集合导航应用这类操作，如下例所示：

using (var context = new BloggingContext())
{
    var filteredBlogs = context.Blogs
        .Include(
            blog => blog.Posts
                .Where(post => post.BlogId == 1)
                .OrderByDescending(post => post.Title)
                .Take(5))
        .ToList();
}

只能对每个包含的导航执行一组唯一的筛选器操作。如果为某个给定的集合导航应用了多个包含操作（下例中为 blog.Posts），则只能对其中一个导航指定筛选器操作：

using (var context = new BloggingContext())
{
    var filteredBlogs = context.Blogs
        .Include(blog => blog.Posts.Where(post => post.BlogId == 1))
        .ThenInclude(post => post.Author)
        .Include(blog => blog.Posts)
        .ThenInclude(post => post.Tags.OrderBy(postTag => postTag.TagId).Skip(3))
        .ToList();
}

可对多次包含的每个导航应用相同的操作：

using (var context = new BloggingContext())
{
    var filteredBlogs = context.Blogs
        .Include(blog => blog.Posts.Where(post => post.BlogId == 1))
        .ThenInclude(post => post.Author)
        .Include(blog => blog.Posts.Where(post => post.BlogId == 1))
        .ThenInclude(post => post.Tags.OrderBy(postTag => postTag.TagId).Skip(3))
        .ToList();
}

派生类型上的包含

可以使用 Include 和 ThenInclude 包含仅在派生类型上定义的导航的关联数据。

给定以下模型：

public class SchoolContext : DbContext
{
    public DbSet<Person> People { get; set; }
    public DbSet<School> Schools { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<School>().HasMany(s => s.Students).WithOne(s => s.School);
    }
}

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Student : Person
{
    public School School { get; set; }
}

public class School
{
    public int Id { get; set; }
    public string Name { get; set; }

    public List<Student> Students { get; set; }
}

对于具有学生身份的所有人员，可使用多种模式来预先加载其 School 导航属性的内容：

使用强制转换

context.People.Include(person => ((Student)person).School).ToList()

使用 as 运算符

context.People.Include(person => (person as Student).School).ToList()

使用 Include 的重载，该方法采用 string 类型的参数
```
context.People.Include("School").ToList()
```

自动包含

备注

EF Core 6.0 中已引入此功能。

可使用 AutoInclude 方法配置每次从数据库加载实体时要包含的模型中的导航。这与在结果中返回实体类型的每个查询中使用导航指定 Include 具有相同的效果。以下示例演示如何配置要自动包含的导航。

modelBuilder.Entity<Theme>().Navigation(e => e.ColorScheme).AutoInclude();

完成上述配置后，运行如下查询将为结果中的所有主题加载 ColorScheme 导航。

using (var context = new BloggingContext())
{
    var themes = context.Themes.ToList();
}

此配置应用于结果中返回的每一个实体（无论它在结果中的显示方式如何）。这意味着，如果一个实体因为使用导航而出现在结果中，则对另一个实体类型或自动包含配置使用 Include 时，它将为其加载所有自动包含的导航。同一规则可扩展到在实体的派生类型上配置为自动包含的导航。

如果对于特定查询，你不想通过导航加载相关数据（该导航在模型级别配置为自动包含），可在查询中使用 IgnoreAutoIncludes 方法。使用此方法将停止加载用户配置为自动包含的所有导航。运行如下所示的查询会从数据库返回所有主题，但不会加载 ColorScheme，即使它被配置为自动包含导航也是如此。

using (var context = new BloggingContext())
{
    var themes = context.Themes.IgnoreAutoIncludes().ToList();
}

备注

对从属类型的导航也按照约定配置为自动包含，并且使用 IgnoreAutoIncludes API 并不会阻止包含它们。它们仍将包含在查询结果中。

2. 显式加载

可以通过 DbContext.Entry(...) API 显式加载导航属性。

using (var context = new BloggingContext())
{
    var blog = context.Blogs
        .Single(b => b.BlogId == 1);

    context.Entry(blog)
        .Collection(b => b.Posts)
        .Load();

    context.Entry(blog)
        .Reference(b => b.Owner)
        .Load();
}

还可以通过执行返回关联实体的单独查询来显式加载导航属性。如果已启用更改跟踪，则在查询具体化实体时，EF Core 将自动设置新加载的实体的导航属性以引用任何已加载的实体，并设置已加载实体的导航属性以引用新加载的实体。

查询相关实体

还可以获得表示导航属性内容的 LINQ 查询。

这使你可对查询应用其他运算符。例如，无需将关联实体加载到内存中，即可对关联实体执行聚合运算。

using (var context = new BloggingContext())
{
    var blog = context.Blogs
        .Single(b => b.BlogId == 1);

    var postCount = context.Entry(blog)
        .Collection(b => b.Posts)
        .Query()
        .Count();
}

还可以筛选要加载到内存中的关联实体。

using (var context = new BloggingContext())
{
    var blog = context.Blogs
        .Single(b => b.BlogId == 1);

    var goodPosts = context.Entry(blog)
        .Collection(b => b.Posts)
        .Query()
        .Where(p => p.Rating > 3)
        .ToList();
}

3. 延迟加载

使用代理的延迟加载

使用延迟加载的最简单方式是通过安装 Microsoft.EntityFrameworkCore.Proxies 包，并通过调用 UseLazyLoadingProxies 来启用该包。例如：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseLazyLoadingProxies()
        .UseSqlServer(myConnectionString);

或在使用 AddDbContext 时：

.AddDbContext<BloggingContext>(
    b => b.UseLazyLoadingProxies()
          .UseSqlServer(myConnectionString));

EF Core 接着会为可重写的任何导航属性（即，必须是 virtual 且在可被继承的类上）启用延迟加载。例如，在以下实体中，Post.Blog 和 Blog.Posts 导航属性将被延迟加载。

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Post> Posts { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public virtual Blog Blog { get; set; }
}

警告

延迟加载可能会导致发生不必要的额外数据库往返（即 N+1 问题），应注意避免此问题。有关详细信息，请参阅性能部分。

延时到什么时候执行？

导航属性：什么时候访问了导航属性，什么时候就会被加载（会执行一次数据库）

[HttpGet]
public IActionResult GetList()
{
    var roles = _context.Roles.ToList();  // 只查询Role表，不进行与RoleLoginLog表关联查询
    //var logs = roles.Logs.ToList(); // 执行查询RoleLoginLog表

    return Ok(roles); // 如果上面既没有对目标集合进行ToList，FirstOrDefault，Count，也没有看到foreach 等操作，那么最后返回的时候必定会执行查询
}

打印的SQL如下(我们发现查询了6次数据库)：

SELECT `r`.`role_id`, `r`.`create_time`, `r`.`grade`, `r`.`passport_id`, `r`.`role_name`
FROM `role` AS `r`



SELECT `r`.`log_id`, `r`.`device_brand`, `r`.`device_cpu`, `r`.`login_time`, `r`.`passport_id`, `r`.`role_id`
FROM `role_login_log` AS `r`
WHERE `r`.`role_id` = @__p_0

SELECT `r`.`log_id`, `r`.`device_brand`, `r`.`device_cpu`, `r`.`login_time`, `r`.`passport_id`, `r`.`role_id`
FROM `role_login_log` AS `r`
WHERE `r`.`role_id` = @__p_0

SELECT `r`.`log_id`, `r`.`device_brand`, `r`.`device_cpu`, `r`.`login_time`, `r`.`passport_id`, `r`.`role_id`
FROM `role_login_log` AS `r`
WHERE `r`.`role_id` = @__p_0

SELECT `r`.`log_id`, `r`.`device_brand`, `r`.`device_cpu`, `r`.`login_time`, `r`.`passport_id`, `r`.`role_id`
FROM `role_login_log` AS `r`
WHERE `r`.`role_id` = @__p_0

SELECT `r`.`log_id`, `r`.`device_brand`, `r`.`device_cpu`, `r`.`login_time`, `r`.`passport_id`, `r`.`role_id`
FROM `role_login_log` AS `r`
WHERE `r`.`role_id` = @__p_0

不使用代理的延迟加载

不使用代理进行延迟加载的工作方式是将 ILazyLoader 注入到实体中。例如：

public class Blog
{
    private ICollection<Post> _posts;

    public Blog()
    {
    }

    private Blog(ILazyLoader lazyLoader)
    {
        LazyLoader = lazyLoader;
    }

    private ILazyLoader LazyLoader { get; set; }

    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<Post> Posts
    {
        get => LazyLoader.Load(this, ref _posts);
        set => _posts = value;
    }
}

public class Post
{
    // 私有字段命名需要注意，除了首字母大小写不一致之外 ，其他的字段名大小写一定要一样
    private Blog _blog;

    public Post()
    {
    }

    private Post(ILazyLoader lazyLoader)
    {
        LazyLoader = lazyLoader;
    }

    private ILazyLoader LazyLoader { get; set; }

    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public Blog Blog
    {
        get => LazyLoader.Load(this, ref _blog);
        set => _blog = value;
    }
}

此方法不要求实体类型为可继承的类型，也不要求导航属性必须是虚拟的，且允许通过 new 创建的实体实例在附加到上下文后可进行延迟加载。但它需要对 Microsoft.EntityFrameworkCore.Abstractions 包中定义的 ILazyLoader 服务的引用。此包包含所允许的最少的一组类型，以便将依赖此包时所产生的影响降至最低。不过，可以将 ILazyLoader.Load 方法以委托的形式注入，这样就可以完全避免依赖于实体类型的任何 EF Core 包。例如：

public class Blog
{
    private ICollection<Post> _posts;

    public Blog()
    {
    }

    private Blog(Action<object, string> lazyLoader)
    {
        LazyLoader = lazyLoader;
    }

    private Action<object, string> LazyLoader { get; set; }

    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<Post> Posts
    {
        get => LazyLoader.Load(this, ref _posts);
        set => _posts = value;
    }
}

public class Post
{
    private Blog _blog;

    public Post()
    {
    }

    private Post(Action<object, string> lazyLoader)
    {
        LazyLoader = lazyLoader;
    }

    private Action<object, string> LazyLoader { get; set; }

    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public Blog Blog
    {
        get => LazyLoader.Load(this, ref _blog);
        set => _blog = value;
    }
}
上述代码使用 Load 扩展方法，以便更干净地使用委托：

public static class PocoLoadingExtensions
{
    public static TRelated Load<TRelated>(
        this Action<object, string> loader,
        object entity,
        ref TRelated navigationField,
        [CallerMemberName] string navigationName = null)
        where TRelated : class
    {
        loader?.Invoke(entity, navigationName);

        return navigationField;
    }
}

备注

延迟加载委托的构造函数参数必须名为“lazyLoader”，以后可能会换另一个名称。

4. 关联数据和序列化

由于 EF Core 会自动修正导航属性，因此在对象图中可能会产生循环引用。例如，加载博客及其关联文章会生成引用文章集合的博客对象。而其中每篇文章又会引用该博客。

某些序列化框架不允许使用循环引用。例如，Json.NET 在发现循环引用的情况下，会引发以下异常。

Newtonsoft.Json.JsonSerializationException：为“MyApplication.Models.Blog”类型的“Blog”属性检测到自引用循环。

如果正在使用 ASP.NET Core，则可以将 Json.NET 配置为忽略在对象图中找到的循环引用。此配置是通过 Startup.cs 中的

ConfigureServices(...) 方法完成的。

public void ConfigureServices(IServiceCollection services)
{
    ...

        services.AddMvc()
        .AddJsonOptions(
        options => options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
    );

    ...
}




// 在。Net 6 中默认具有如下设置（因为是WebAPI，所以使用AddControllers）：
builder.Services.AddControllers()
    .AddJsonOptions(p =>
                    {
                        // 忽略循环引用
                        p.JsonSerializerOptions.ReferenceHandler = ReferenceHandler.IgnoreCycles;
                    })
    ;

另一种方法是使用 [JsonIgnore] 特性修饰其中一个导航属性，该特性指示 Json.NET 在序列化时不遍历该导航属性。

3. 拆分查询

1. 单个查询

在关系数据库中，所有相关实体通过在单个查询中引入 JOIN 来加载。

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [Blogs] AS [b]
LEFT JOIN [Posts] AS [p] ON [b].[BlogId] = [p].[BlogId]
ORDER BY [b].[BlogId], [p].[PostId]

如果典型博客有多篇相关文章，这些文章对应的行会复制博客的信息。这种复制会导致所谓的“笛卡尔爆炸”问题发生。随着加载更多的一对多关系，重复的数据量可能会增长，并对应用程序性能产生负面影响。

2. 拆分查询

备注

此功能是在 EF Core 5.0 中引入的，仅在使用 Include 时才有效。在不使用 Include 加载投影中的相关数据的情况下，EF Core 6.0 添加了对拆分查询的支持。

通过 EF，可以指定应将给定 LINQ 查询拆分为多个 SQL 查询。与 JOIN 不同，拆分查询为包含的每个集合导航生成额外的 SQL 查询：

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Include(blog => blog.Posts)
        .AsSplitQuery() // 拆分查询
        .ToList();
}

这会生成以下 SQL：

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
ORDER BY [b].[BlogId]

SELECT [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title], [b].[BlogId]
FROM [Blogs] AS [b]
INNER JOIN [Post] AS [p] ON [b].[BlogId] = [p].[BlogId]
ORDER BY [b].[BlogId]

还可以将拆分查询配置为应用程序上下文的默认查询：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(
            @"Server=(localdb)\mssqllocaldb;Database=EFQuerying;Trusted_Connection=True",
            o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
}

将拆分查询配置为默认查询后，仍然可以将特定查询配置为以单个查询的形式执行：

using (var context = new SplitQueriesBloggingContext())
{
    var blogs = context.Blogs
        .Include(blog => blog.Posts)
        .AsSingleQuery() // 依然是非拆分查询
        .ToList();
}

如果没有任何配置，默认情况下，EF Core 使用单个查询模式。由于这可能会导致性能问题，因此，只要满足以下条件，EF Core 就会生成警告：

EF Core 检测到查询加载了多个集合。
用户未全局配置查询拆分模式。
用户未在查询上使用 AsSingleQuery/AsSplitQuery 运算符。

若要关闭警告，请全局配置查询拆分模式，或在查询级别将其配置为适当的值。

3. 拆分查询的特征

虽然拆分查询避免了与 JOIN 和笛卡尔爆炸相关的性能问题，但它也有一些缺点：

虽然大多数数据库对单个查询保证数据一致性，但对多个查询不存在这样的保证。如果在执行查询时同时更新数据库，生成的数据可能会不一致。这可以通过将查询包装在可序列化或快照事务中来缓解，尽管这样做本身可能会产生性能问题。当前，每个查询都意味着对数据库进行一次额外的网络往返。多次网络往返会降低性能，尤其是在数据库延迟很高的情况下（例如云服务）。
虽然有些数据库（带有 MARS 的 SQL Server、Sqlite）允许同时使用多个查询的结果，但大多数数据库在任何给定时间点只允许一个查询处于活动状态。因此，在执行以后的查询之前，必须先在应用程序的内存中缓冲先前查询的所有结果，这将增加内存需求。

遗憾的是，没有一种加载相关实体的策略可以适用于所有情况。请仔细考虑单个查询和拆分查询的优缺点，以便选择能够满足你需求的策略。

4. Linq 查询

全称： Language-Integrated Query（语言-集成查询）

EFCore 对标准的Linq语法进行了封装，称之为Linq方法。

// 查询所有的数据
[Test]
public void Test1()
{
    using EfCoreDemoContext context = new EfCoreDemoContext();
    // select * from role
    var roles = context.Roles.ToList();
}

// 查询
[Test]
public void Test2()
{
    using EfCoreDemoContext context = new EfCoreDemoContext();
    // select * from role where roleName like '%管理员%'
    IQueryable<Role> query = context.Roles.Where(p=>p.RoleName.Contains("管理员"));

    // /********************动态拼接查询条件*********************/
    var passportId = 1; // 假设这个参数是由前端传递过来的

    // 判断前端传递过来的是事有效，如果有效，则需要将此条件拼接起来
    if (passportId>0)
    {
        query = query.Where(p => p.PassportId == passportId);
    }

    var roles = query.ToList();// 执行SQL
}

// 排序
[Test]
public void Test3()
{
    using EfCoreDemoContext context = new EfCoreDemoContext();
    // 按roleId 升序
    var list1 = context.Roles.OrderBy(p=>p.RoleId).ToList();

    // 按roleId 降序
    var list2 = context.Roles.OrderByDescending(p=>p.RoleId).ToList();

    // 多字段排序
    var list3 = context.Roles.OrderByDescending(p=>p.RoleName).ThenBy(p=>p.RoleId).ToList();
}

// 投影
[Test]
public void Test4()
{
    using EfCoreDemoContext context = new EfCoreDemoContext();
    // select RoleId as id ,RoleName as name from role
    var list = context.Roles.Select(p =>new
                                    {
                                        id= p.RoleId,
                                        name = p.RoleName
                                    }).ToList();
}


// 内连接
[Test]
public void Test5()
{
    using EfCoreDemoContext context = new EfCoreDemoContext();
    // select role.RoleId,role.roleName,log.LoginTime,log.PassportId from Role role 
    // join RoleLoginLog log on role.RoleId=log.RoleId
    var list = context.Roles.Join(context.RoleLoginLogs,
                                  role => role.RoleId,
                                  log => log.RoleId, (role, log) => new
                                  {
                                      role.RoleId,
                                      role.RoleName,
                                      log.LoginTime,
                                      log.PassportId
                                  }).ToList();
}

// 左连接
[Test]
public void Test6()
{
    using EfCoreDemoContext context = new EfCoreDemoContext();
    // select role.RoleId,role.roleName,log.LoginTime,log.PassportId from Role role 
    // left join RoleLoginLog log on role.RoleId=log.RoleId


    // LeftJoin() 官方未实现此方法
    // var list = context.Roles.LeftJoin(context.RoleLoginLogs, role => role.RoleId, log => log.RoleId, (role, log) => new
    // {
    //     role.RoleId,
    //     role.RoleName,
    //     log.LoginTime,
    //     log.PassportId
    // }).DefaultIfEmpty().ToList();
    // Console.WriteLine(list.Count);

    var query = from role in context.Roles
        join log in context.RoleLoginLogs on role.RoleId equals log.RoleId
        into temp
        from t in temp.DefaultIfEmpty()
        select new
    {
        role.RoleId,
        role.RoleName,
        t.LoginTime,
        t.PassportId
    };


}


// 分页查询（有专门一节讲解）
[Test]
public void Test10()
{
    using EfCoreDemoContext context = new EfCoreDemoContext();

    var query = from role in context.Roles
        join log in context.RoleLoginLogs on role.RoleId equals log.RoleId
        into temp
        from t in temp.DefaultIfEmpty()
        select new
    {
        role.RoleId,
        role.RoleName,
        t.LoginTime,
        t.PassportId
    };

    /**
        * 每页10，查询第二页，
        * 第二页的数据应该从：11-20
        */
    int pageIndex = 2;
    int pageSize = 10;

    // Skip: 跳过多少条数据，
    // Take: 只查询多少条
    // 分页之前一条要排序
    query = query.OrderByDescending(p => p.RoleId).Skip((pageIndex - 1) * pageSize).Take(pageSize);

    var list = query.ToList();
}


// 分组查询
[Test]
public void Test11()
{
    using EfCoreDemoContext context = new EfCoreDemoContext();
    // select role_id,count(*),max(login_time) from role_login_log group by role_id
    var groupBy = context.RoleLoginLogs.GroupBy(key => key.RoleId, (key, g) =>new
                                                {
                                                    RoleId = key,
                                                    Count = g.Count(),
                                                    MaxLoginTime = g.Max(log=>log.LoginTime)
                                                }).ToList();
    foreach (var g in groupBy)
    {
        Console.WriteLine($"key={g.RoleId},count={g.Count},time={g.MaxLoginTime}");
    }
}

AsEnumerable 与 AsQueryable

1.简介

用Linq来操作集合的时候会用到AsQueryable()和AsEnumerable()，何时该用AsQueryable()和何时该用AsEnumerable()，或许存在些疑惑。AsQueryable是在数据库中查询再返回数据，AsEnumerable是从数据库读取全部数据再在程序中查询。

在使用LINQ 进行数据集操作时，LINQ 不能直接从数据集对象中查询，因为数据集对象不支持LINQ 查询，所以需要使用AsEnumerable 方法返回一个泛型的对象以支持LINQ 的查询操作。

例如

string strcon = "Data Source=.\SQLEXPRESS;Initial Catalog=Db_Example;Persist Security Info=True;User ID=sa;Password=sa";
SqlConnection con = new SqlConnection(strcon);
con.Open();
string strsql = "select * from SC,Course where SC.Cno=Course.Cno";
SqlDataAdapter da = new SqlDataAdapter(strsql,con);
DataSet ds = new DataSet();
da.Fill(ds, "mytable");
DataTable tables=ds.Tables["mytable"]; //创建表
var dslp = from d in tables.AsEnumerable() select d;//执行LINQ语句,这里的.AsEnumerable()是延迟发生,不会立即执行，实际上什么都没有发生
foreach(var res in dslp)               
{
     Response.Write(res.Field<string>("Cname").ToString());
}

一、linq中AsEnumerable和AsQueryable的区别

1、AsEnumerable()是延迟执行的，实际上什么都没有发生，当真正使用对象的时候（例如调用：First, Single, ToList....的时候）才执行。 2、AsEnumerable将一个序列向上转换为一个IEnumerable, 强制将Enumerable类下面的查询操作符绑定到后续的子查询当中。 3、AsQueryable将一个序列向下转换为一个IQueryable, 它生成了一个本地查询的IQueryable包装。

4、AsEnumerable()延迟执行，不会立即执行。当你调用.AsEnumerable()的时候，实际上什么都没有发生。 5、ToList()立即执行 6、当你需要操作结果的时候，用.ToList()，否则，如果仅仅是用来查询不需要进一步使用结果集，并可以延迟执行，就用.AsEnumerable()/IEnumerable /IQueryable

7、AsEnumerable()虽然延迟执行，但还是访问数据库，而.ToList()直接取得结果放在内存中。比如我们需要显示两个部门的员工时，部门可以先取出放置在List中，然后再依次取出各个部门的员工，这时访问的效率要高一些，因为不需要每次都访问数据库去取出部门。 8、IQueryable实现了IEnumberable接口。但IEnumerable<T> 换成IQueryable<T>后速度提高很多。

9、IQueryable接口与IEnumberable接口的区别： IEnumerable<T> 泛型类在调用自己的SKip 和 Take 等扩展方法之前数据就已经加载在本地内存里了，而IQueryable<T> 是将Skip ,take 这些方法表达式翻译成T-SQL语句之后再向SQL服务器发送命令，它并不是把所有数据都加载到内存里来才进行条件过滤。 10、IEnumerable跑的是Linq to Object，强制从数据库中读取所有数据到内存先。

二、AsEnumerable和AsQueryable的实例

实例一

using (testContext context = new testContext())
{
    var query = (from item in context.Users.AsQueryable()
                    where item.id > 10
                    select item.id).ToList();
    var query2 = (from item in context.Users.AsEnumerable()
                    where item.id > 10
                    select item.id).ToList();
}

服务器端sql

--AsQueryable

Select 
[Extent1].[id] AS [id]
FROM [dbo].[User] AS [Extent1]
Where [Extent1].[id] > 10


--AsEnumerable

Select 
[Extent1].[id] AS [id], 
[Extent1].[usn] AS [usn], 
[Extent1].[pwd] AS [pwd], 
[Extent1].[created] AS [created]
FROM [dbo].[User] AS [Extent1]
实例二

using (testContext context = new testContext())
{
    var query = (from item in context.Users.AsQueryable()
                    where item.id > 10
                    orderby item.id ascending
                    select item.id).Skip(20).Take(20).ToList();
    var query2 = (from item in context.Users.AsEnumerable()
                    where item.id > 10
                    orderby item.id ascending
                    select item.id).Skip(20).Take(20).ToList();
}
服务器端sql

--AsQueryable

Select TOP (20) 
[Filter1].[id] AS [id]
FROM ( Select [Extent1].[id] AS [id], row_number() OVER (ORDER BY [Extent1].[id] ASC) AS [row_number]
    FROM [dbo].[User] AS [Extent1]
    Where [Extent1].[id] > 10
)  AS [Filter1]
Where [Filter1].[row_number] > 20
orDER BY [Filter1].[id] ASC


--AsEnumerable

Select 
[Extent1].[id] AS [id], 
[Extent1].[usn] AS [usn], 
[Extent1].[pwd] AS [pwd], 
[Extent1].[created] AS [created]
FROM [dbo].[User] AS [Extent1]

5. 分页

分页是指在页面中检索结果，而不是一次性检索结果;这通常针对大型结果集完成，其中显示用户界面，允许用户导航到结果的下一页或上一页。

偏移分页

公司目前用得最多的是这种

使用数据库实现分页的一种常见方法是在 SQL) 中使用 Skip 和 Take (OFFSET``LIMIT 。给定页面大小为 10 个结果，可以使用 EF Core 提取第三页，如下所示：

var position = 20;
var nextPage = context.Posts
    .OrderBy(b => b.PostId)
    .Skip(position)
    .Take(10)
    .ToList();

遗憾的是，虽然这种技术非常直观，但它也有一些严重的缺点：

数据库仍必须处理前 20 个条目，即使它们未返回到应用程序：跳过条目也会计算负载，该负载会随着跳过的行数而增加。
如果同时发生任何更新，则分页最终可能会跳过某些条目或显示两次。例如，如果用户从第 2 页移动到第 3 页时删除了条目，则整个结果集“向上移动”，将跳过一个条目。

Keyset 分页

建议使用基于偏移的分页（有时称为 键集分 页或 基于查找的分页 ）的替代方法是使用 WHERE 子句跳过行，而不是偏移量。这意味着请记住从提取 (的最后一个条目中获取的相关值，而不是其偏移量) ，并要求在该行之后的下一行。例如，假设我们提取的最后一个页面的最后一个条目的 ID 值为 55，我们只需执行以下操作：

var lastId = 55;
var nextPage = context.Posts
    .OrderBy(b => b.PostId)
    .Where(b => b.PostId > lastId)
    .Take(10)
    .ToList();

假设定义了索引 PostId，则此查询非常高效，并且对 ID 值较低时发生的任何并发更改也不敏感。

Keyset 分页适用于用户向前和向后导航但不支持随机访问的分页接口，用户可以跳转到任何特定页面。随机访问分页需要使用偏移量分页，如上所述：由于偏移分页的缺点，请仔细考虑是否确实需要随机访问分页，或者下一页导航是否足够。如果需要随机访问分页，可靠的实现可以在导航到下一页/上一页时使用键集分页，并在跳转到任何其他页面时偏移导航。

Postid 的值不能中断，如果中间删除了数据，则PostID的值取出来肯定会有问题

多个分页键

使用键集分页时，经常需要通过多个属性进行排序。例如，以下查询按日期和 ID 分页：

var lastDate = new DateTime(2020, 1, 1);
var lastId = 55;
var nextPage = context.Posts
    .OrderBy(b => b.Date)
    .ThenBy(b => b.PostId)
    .Where(b => b.Date > lastDate || (b.Date == lastDate && b.PostId > lastId))
    .Take(10)
    .ToList();

这可确保下一页准确选取上一页结束的位置。添加更多的排序键时，可以添加其他子句。

索引

与任何其他查询一样，适当的索引对于良好的性能至关重要：请确保具有与分页排序对应的索引。如果按多个列排序，则可以定义多个列的索引;这称为 复合索引。

6. 执行 SQL

通过 Entity Framework Core 可以在使用关系数据库时下降到 SQL 查询。如果所需查询无法使用 LINQ 表示，或 LINQ 查询导致 EF 生成效率低下的 SQL(某些统计查询会有几百行的SQL)，则可使用 SQL 查询。 SQL 查询可返回一般实体类型或者模型中的无键实体类型（可以理解为ViewModel）。

提示

可在 GitHub 上查看此文章的示例。

基本 SQL 查询

可使用 FromSql 基于 SQL 查询开始 LINQ 查询：

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.Blogs")
    .ToList();

备注

EF Core 7.0 中引入了 FromSql。使用更旧的版本时，请改用 FromSqlInterpolated。

SQL 查询可用于执行返回实体数据的存储过程：

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

备注

FromSql 只能直接在 DbSet 上使用。不能在任意 LINQ 查询的基础上组合使用它。

多表查询

[Keyless] // 无主键的实体（因为这个类并非映射数据库中的表，只是为了做多表查询）
public class RolePassport
{

    [Column("role_id")]
    public int RoleId { get; set; }
    /// <summary>
    /// 角色名称
    /// </summary>
    [Column("role_name")]
    public string? RoleName { get; set; }
    /// <summary>
    /// 账户编号
    /// </summary>
    [Column("passport_id")]
    public int? PassportId { get; set; }
    /// <summary>
    /// 角色等级
    /// </summary>
    [Column("grade")]
    public int? Grade { get; set; }
    /// <summary>
    /// 创建日期
    /// </summary>
    [Column("create_time")]
    public DateTime? CreateTime { get; set; }


    [Column("user_name")]
    public string? UserName { get; set; }
}


/// <summary>
/// 查询多表
/// </summary>
[Test]
public void TestQueryMoreTable()
{
    using var context = new EfCoreDemoContext();
    List<RolePassport> list = context.RolePassports.FromSqlInterpolated
        ($"SELECT a.*, b.user_name  FROM role a JOIN passport b ON a.passport_id = b.passport_id")
        .ToList();
}
 

 

 

调用 mysql 存储过程：

CREATE PROCEDURE p_role_select ( IN role_name VARCHAR ( 30 ) ) BEGIN
DECLARE _sql VARCHAR ( 500 ) DEFAULT 'SELECT a.*, b.user_name  FROM role a JOIN passport b ON a.passport_id = b.passport_id where 1=1 ';

IF role_name IS NOT NULL AND role_name !='' THEN
    SET _sql = CONCAT( _sql, ' and role_name like ',"'%", role_name, "%'");
END IF;


SET @_sql = _sql;
PREPARE pre_sql  FROM @_sql;
EXECUTE pre_sql;
DEALLOCATE PREPARE pre_sql;

END;

快速参考

警告

请在使用 SQL 查询时密切关注参数化

向 SQL 查询引入任何用户提供的值时，必须注意防范 SQL 注入攻击。如果程序将用户提供的字符串值集成到 SQL 查询中，而用户提供的值被创建用来终止字符串并执行另一个恶意 SQL 操作，则表明发生了 SQL 注入。若要详细了解 SQL 注入，请查看此页。

FromSql 和 FromSqlInterpolated 方法可以防止 SQL 注入，始终将参数数据作为单独的 SQL 参数进行集成。但是，如果不当使用，FromSqlRaw 方法可能易受 SQL 注入攻击。

下面的示例通过在 SQL 查询字符串中包含参数占位符并提供额外的自变量，将单个参数传递到存储过程：

var user = "johndoe";

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

虽然此语法可能看上去像常规 C# 字符串内插，但提供的值包装在 DbParameter 中，且生成的参数名称插入到指定了 {0} 占位符的位置。这使得 FromSql 可以防范 SQL 注入攻击，可以将值高效且正确地发送到数据库。

执行存储过程时，在 SQL 查询字符串中使用命名参数很有用，尤其是在存储过程具有可选参数的情况下：

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
    .ToList();

如果需要对要发送的数据库参数进行更多控制，还可以构造 DbParameter 并将其作为参数值提供。这样就可以设置参数的精确数据库类型，或 facet（例如其大小、精度或长度）：

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

备注

传递的参数必须与存储过程定义完全匹配。请特别注意参数的排序，注意不要缺失或错放任何参数，也可以考虑使用命名参数表示法。此外，请确保参数类型对应，并根据需要设置其 facet（大小、精度、规模）。

动态 SQL 和参数

应尽可能使用 FromSql 及其参数化。但在某些情况下，需要将 SQL 动态拼凑在一起，并且无法使用数据库参数。例如，假设 C# 变量包含要通过其进行筛选的属性的名称。你可能会迫不及待地想要使用 SQL 查询，例如：

var propertyName = "User";
var propertyValue = "johndoe";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
    .ToList();

此代码无效，因为数据库不允许将列名（或架构的任何其他部分）参数化。

首先，请务必考虑通过 SQL 或其他方式动态构造查询的影响。接受用户提供的列名时，用户可能会选择一个未编制索引的列，使查询运行起来极慢且让数据库过载；用户也可能选择一个包含你不希望公开的数据的列。除了真正动态的方案外，通常情况下，最好是让两个查询使用两个列名，而不是使用参数化将它们折叠到单个查询中。

如果决定要动态构造 SQL，则必须使用 FromSqlRaw，这样就可以直接将变量数据内插到 SQL 字符串中，而不使用数据库参数：

var columnName = "Url";
var columnValue = new SqlParameter("columnValue", "http://SomeURL");

var blogs = context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToList();

在上面的代码中，我们使用 C# 字符串内插将列名直接插入到 SQL 中。你有责任确保此字符串值是安全的，如果它的来源不安全，请对其进行清理；这意味着检测特殊字符（如分号、注释和其他 SQL 构造），并正确地将这些字符进行转义或拒绝此类输入。

另一方面，列值通过 DbParameter 发送，因此在遇到 SQL 注入时是安全的。

警告

使用 FromSqlRaw 时要非常小心，始终确保值来自安全的源，或者经过正确清理。 SQL 注入攻击可能会为应用程序带来灾难后果。

执行视图

EF Core是不支持存储过程及视图的映射的，那么直接通过 DbContext 是没有办法直接调用（就是不能直接 "点" 出来）到存储过程与视图的。对视图来讲，在数据库中 EF Core 根本不知道分不清数据表跟视图的区别，这也导致了无法直接通过 DbContext 直接进行调用视图。我们来用最简单的一个.net core 提供的映射的方法 -- ToView()

创建视图

create view v_role_passport
as
select  role_id, role_name,user_name,`password` 
from role a inner join passport b on a.passport_id=b.passport_id

视图就准备好了，那么，我们接口手动创建一个，用于映射的实体，里面的字段与视图返回的结果一致，名称就无所谓了，因为可以直接映射到对应的视图：

public class RolePassportView
{
    [Column("role_id")]
    public int RoleId { get; set; }

    [Column("role_name")]
    public string? RoleName { get; set; }

    [Column("user_name")]
    public string? UserName { get; set; }

    [Column("password")]
    public string? Password { get; set; }
}

接下来就是使用 ToView()进行映射了

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // ...
    modelBuilder.Entity<RolePassportView>(p => {
        p.ToView("v_role_passport"); // 映射视图
        p.HasNoKey(); // 无主键视图
    });

    // ...
}

使用视图

[Test]
public void TestView()
{
    using var context = new EfCoreDemoContext();
    var list = context.RolePassportView.ToList();
}

使用 LINQ 编写

可以使用 LINQ 运算符在初始 SQL 查询的基础上进行组合；EF Core 会将 SQL 视为子查询，在数据库中以它为基础进行组合。下面的示例使用 SQL 查询，该查询从表值函数 (TVF) 中进行选择。然后，使用 LINQ 进行筛选和排序，从而对其进行组合。

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToList();
上面的查询生成以下 SQL：

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM (
    SELECT * FROM dbo.SearchBlogs(@p0)
) AS [b]
WHERE [b].[Rating] > 3
ORDER BY [b].[Rating] DESC

包含关联数据

Include 运算符可用于加载相关数据，就像对其他 LINQ 查询那样：

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToList();

使用 LINQ 进行组合要求 SQL 查询是可组合的，因为 EF Core 会将提供的 SQL 视为子查询。可组合 SQL 查询通常以 SELECT 关键字开头，不能包含子查询中无效的 SQL 功能，例如：

结尾分号
在 SQL Server 上，结尾处的查询级提示（例如，OPTION (HASH JOIN)）
在 SQL Server 上，SELECT 子句中不与 OFFSET 0 或 TOP 100 PERCENT 配合使用的 ORDER BY 子句

SQL Server 不允许对存储过程调用进行组合，因此任何尝试向此类调用应用其他查询运算符的操作都将导致无效的 SQL。请在 FromSql 或 FromSqlRaw 之后立即使用 AsEnumerable 或 AsAsyncEnumerable，确保 EF Core 不会尝试对存储过程进行组合。

更改跟踪

使用 FromSql 或 FromSqlRaw 的查询遵循与 EF Core 中所有其他 LINQ 查询完全相同的更改跟踪规则。例如，如果该查询投影实体类型，默认情况下会跟踪结果。

下面的示例使用 SQL 查询，该查询从表值函数 (TVF) 中进行选择，然后禁用通过调用 AsNoTracking 进行的更改跟踪：

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .AsNoTracking()
    .ToList();

查询标量（非实体）类型

备注

EF Core 7.0 中已引入此功能。

虽然可以使用 FromSql 来查询模型中定义的实体，但如果使用 SqlQuery，你就可以通过 SQL 轻松查询非实体标量类型，无需下降到较低级别的数据访问 API。例如，以下查询从 Blogs 表中提取所有 ID：

var ids = context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToList();

还可以在 SQL 查询的基础上组合使用 LINQ 运算符。但是，由于 SQL 成为子查询，其输出列需要由 SQL EF 添加项来引用，因此必须为输出列 Value 命名。例如，以下查询返回的 ID 高于 ID 平均值：

var overAverageIds = context.Database
    .SqlQuery<int>($"SELECT [BlogId] AS [Value] FROM [Blogs]")
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

FromSql 可与数据库提供程序支持的任何标量类型配合使用。如果想要使用数据库提供程序不支持的类型，可以使用约定前配置为其定义值转换。

SqlQueryRaw 允许动态构造 SQL 查询，就像 FromSqlRaw 对实体类型所做的那样。

执行非查询 SQL

在某些情况下，可能需要执行不返回任何数据的 SQL，通常用于修改数据库中的数据或调用不返回任何结果集的存储过程。这可以通过 ExecuteSqlInterpolated来完成：

using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSqlInterpolated($"UPDATE [Blogs] SET [Url] = NULL");
}

EFCore 7.0 中使用的是 ExecuteSql()

它会执行提供的 SQL 并返回修改的行的数目。 ExecuteSql 使用安全的参数化来防止 SQL 注入，就像 FromSql 一样，而 ExecuteSqlRaw 允许动态构造 SQL 查询，就像 FromSqlRaw 对查询所做的那样。

备注

在 EF Core 7.0 之前，有时必须使用 ExecuteSql API 对数据库执行“批量更新”，如上所示；这比在查询所有匹配行后使用 SaveChanges 来修改它们要高效得多。 EF Core 7.0 引入了 ExecuteUpdate 和 ExecuteDelete，因此可以通过 LINQ 表达高效的批量更新操作。建议尽可能使用这些 API 而非 ExecuteSql。

限制

从 SQL 查询返回实体类型时，需注意以下几个限制：

SQL 查询必须返回实体类型的所有属性的数据。
结果集中的列名必须与属性映射到的列名称匹配。请注意，此行为与 EF6 不同；EF6 忽略了 SQL 查询的属性-列映射，只需结果集列名与这些属性名相匹配即可。
SQL 查询不能包含关联数据。但是，在许多情况下你可以在查询后面紧跟着使用 Include 方法以返回关联数据（请参阅包含关联数据）。

7. 查询的工作原理

Entity Framework Core 使用语言集成查询 (LINQ) 来查询数据库中的数据。通过 LINQ 可使用 C#（或你选择的 .NET 语言）基于派生上下文和实体类编写强类型查询。

查询的寿命

下面的描述是每个查询所经历的过程的综合概述。

LINQ 查询由 Entity Framework Core 处理，用于生成已准备好由数据库提供程序处理的表示形式
1. 结果会被缓存，以便每次执行查询时无需进行此处理
结果会传递到数据库提供程序
1. 数据库提供程序确定可以在数据库中评估查询的哪些部分
2. 查询的这些部分会转换为特定数据库的查询语言（例如关系数据库的 SQL）
3. 查询会发送到数据库并返回结果集（返回的是数据库中的值，而不是实体实例中的）
对于结果集中的每一项
1. 如果该查询是跟踪查询，EF 会检查数据是否表示上下文实例内更改跟踪器中的现有实体
  - 如果是，则会返回现有实体
  - 如果不是，则会创建新实体、设置更改跟踪并返回该新实体
2. 如果该查询是非跟踪查询，将始终创建并返回新实体

执行查询时

调用 LINQ 运算符时，只会构建查询在内存中的表示形式(IQueryable<T>)。使用结果时，查询只会发送到数据库。

导致查询发送到数据库的最常见操作如下：

在 for 循环中循环访问结果
使用 ToList、ToArray、Single、Count 等操作或等效的异步重载

警告

始终验证用户输入：虽然 EF Core通过在查询中使用参数和转义文字来防止 SQL 注入攻击，但它不会验证输入。根据应用程序的要求，在将 LINQ 查询中使用的来自不受信任的源的值分配给实体属性或传递给其他 EF Core API 之前，应执行相应的验证。这包括用于动态构造查询的所有用户输入。即使在使用 LINQ 时，如果接受用于生成表达式的用户输入，也会需要确保只能构造预期表达式。

视频配套链接：EFCore 与 WebAPI - 网易云课堂 (163.com)

posted @ 2023-12-14 11:11 誉尚学教育阅读(195) 评论(0) 编辑收藏举报

刷新页面返回顶部

誉尚学教育

海阔凭鱼跃，天高任我行

第五单元 数据查询