ASP.NET Core 3.1系列（17）——EFCore中的查询操作

HerryDong

已于 2022-12-06 14:36:00 修改

阅读量4.5k

点赞数 7

分类专栏： ASP.NET Core C# 文章标签： ASP.NET Core

于 2022-12-06 09:34:02 首次发布

本文链接：https://blog.csdn.net/HerryDong/article/details/128181215

版权

C# 同时被 2 个专栏收录

72 篇文章 17 订阅

订阅专栏

ASP.NET Core

32 篇文章 42 订阅

订阅专栏

1、前言

在之前的ADO.NET时代，很多应用系统都会使用SqlConnection、SqlCommand、SqlDataAdapter等类来实现数据库的CRUD操作，这种方法主要通过编写原生SQL与数据库进行交互。而在ASP.NET Core中，微软则更推荐开发人员使用EFCore。虽然EFCore本质上仍是基于ADO.NET，但对于开发人员来说只需要使用实体类+Linq的方式即可实现CRUD操作，因此从操作便捷性和开发效率上来说，EFCore无疑更具优势，下面就来介绍一些EFCore中关于查询的相关操作。

2、构建测试数据库

在SQL Server中新建一个数据库Dao，创建作者表Author、书表Book，代码如下：

USE [Dao]
GO

/****** Object:  Table [dbo].[Author]    Script Date: 2022/12/5 15:07:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Author](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](20) NULL,
	[Gender] [nvarchar](2) NULL,
	[Age] [int] NULL,
	[Email] [nvarchar](30) NULL,
 CONSTRAINT [PK_Author] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

USE [Dao]
GO

/****** Object:  Table [dbo].[Book]    Script Date: 2022/12/5 15:07:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Book](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](20) NULL,
	[Press] [nvarchar](20) NULL,
	[PublicationTime] [datetime] NULL,
	[Price] [money] NULL,
	[AuthorId] [int] NULL,
 CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Book]  WITH CHECK ADD  CONSTRAINT [FK_Book_Author] FOREIGN KEY([AuthorId])
REFERENCES [dbo].[Author] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Book] CHECK CONSTRAINT [FK_Book_Author]
GO

这里的Author与Book为一对多的关系，AuthorId为Book表中的外键。

Author表数据如下所示：

Id	Name	Gender	Age	Email
1	张三	男	35	11111111@qq.com
2	李四	女	40	22222222@qq.com
3	王五	男	37	33333333@qq.com

Book表数据如下所示：

Id	Title	Press	PublicationTime	Price	AuthorId
1	《C程序设计》	A出版社	2022-01-01	30	1
2	《C++程序设计》	B出版社	2022-02-02	45	1
3	《Java程序设计》	C出版社	2022-03-03	60	2
4	《C#程序设计》	D出版社	2022-04-04	55	2

3、DB First生成实体类

使用NuGet引入EFCore组件，如下所示：

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.Tools

在NuGet控制台输入如下命令生成实体类与数据库上下文：

Scaffold-DbContext -Connection "Data Source=DSF-PC;Initial Catalog=Dao;User ID=sa;Password=123456;" -Provider Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -ContextDir Context -Context DaoDbContext -DataAnnotations -Force

Author代码如下：

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

// Code scaffolded by EF Core assumes nullable reference types (NRTs) are not used or disabled.
// If you have enabled NRTs for your project, then un-comment the following line:
// #nullable disable

namespace App.Models
{
    public partial class Author
    {
        public Author()
        {
            Book = new HashSet<Book>();
        }

        /// <summary>
        /// 主键
        /// </summary>
        [Key]
        public int Id { get; set; }

        /// <summary>
        /// 姓名
        /// </summary>
        [StringLength(20)]
        public string Name { get; set; }

        /// <summary>
        /// 性别
        /// </summary>
        [StringLength(2)]
        public string Gender { get; set; }

        /// <summary>
        /// 年龄
        /// </summary>
        public int? Age { get; set; }

        /// <summary>
        /// 邮箱
        /// </summary>
        [StringLength(30)]
        public string Email { get; set; }

        /// <summary>
        /// 导航属性
        /// </summary>
        [InverseProperty("Author")]
        public virtual ICollection<Book> Book { get; set; }
    }
}

Book代码如下：

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

// Code scaffolded by EF Core assumes nullable reference types (NRTs) are not used or disabled.
// If you have enabled NRTs for your project, then un-comment the following line:
// #nullable disable

namespace App.Models
{
    public partial class Book
    {
        /// <summary>
        /// 主键
        /// </summary>
        [Key]
        public int Id { get; set; }

        /// <summary>
        /// 书名
        /// </summary>
        [StringLength(20)]
        public string Title { get; set; }

        /// <summary>
        /// 出版社
        /// </summary>
        [StringLength(20)]
        public string Press { get; set; }

        /// <summary>
        /// 出版时间
        /// </summary>
        [Column(TypeName = "datetime")]
        public DateTime? PublicationTime { get; set; }

        /// <summary>
        /// 价格
        /// </summary>
        [Column(TypeName = "money")]
        public decimal? Price { get; set; }

        /// <summary>
        /// 外键：AuthorId
        /// </summary>
        public int? AuthorId { get; set; }

        /// <summary>
        /// 导航属性
        /// </summary>
        [ForeignKey(nameof(AuthorId))]
        [InverseProperty("Book")]
        public virtual Author Author { get; set; }
    }
}

DaoDbContext代码如下：

using App.Models;
using Microsoft.EntityFrameworkCore;

// Code scaffolded by EF Core assumes nullable reference types (NRTs) are not used or disabled.
// If you have enabled NRTs for your project, then un-comment the following line:
// #nullable disable

namespace App.Context
{
    public partial class DaoDbContext : DbContext
    {
        public DaoDbContext()
        {
        }

        public DaoDbContext(DbContextOptions<DaoDbContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Author> Author { get; set; }
        public virtual DbSet<Book> Book { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Data Source=DSF-PC;Initial Catalog=Dao;User ID=sa;Password=123456;");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Book>(entity =>
            {
                entity.HasOne(d => d.Author)
                    .WithMany(p => p.Book)
                    .HasForeignKey(d => d.AuthorId)
                    .OnDelete(DeleteBehavior.Cascade)
                    .HasConstraintName("FK_Book_Author");
            });

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

最后在Startup.cs中添加一下数据库上下文即可，代码如下：

using App.Context;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;

namespace App
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            // 添加控制器
            services.AddControllers();

            // 添加数据库上下文
            services.AddDbContext<DaoDbContext>(options =>
            {
                options.UseSqlServer("Data Source=DSF-PC;Initial Catalog=Dao;User ID=sa;Password=123456;");
            });
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            app.UseHttpsRedirection();
            app.UseRouting();
            app.UseAuthorization();
            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

4、EFCore中的基础查询

4.1、条件——Where

使用Where可以实现条件过滤，下面将查询Name='张三'且Age>30的记录，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Author>> Get()
        {
            return _dbContext.Set<Author>().Where(p => p.Name == "张三" && p.Age > 30).ToList();
        }
    }
}

打开SQL Server Profiler，可以发现后台执行的SQL语句如下：

SELECT [a].[Id], [a].[Age], [a].[Email], [a].[Gender], [a].[Name]
FROM [Author] AS [a]
WHERE ([a].[Name] = N'张三') AND ([a].[Age] > 30)

运行结果如下：

[{"id":1,"name":"张三","gender":"男","age":35,"email":"11111111@qq.com","book":[]}]

4.2、排序——OrderBy

使用OrderBy可以实现排序，下面将Author表中的记录按照Age倒序排列，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Author>> Get()
        {
            return _dbContext.Set<Author>().OrderByDescending(p => p.Age).ToList();
        }
    }
}

打开SQL Server Profiler，可以发现后台执行的SQL语句如下：

SELECT [a].[Id], [a].[Age], [a].[Email], [a].[Gender], [a].[Name]
FROM [Author] AS [a]
ORDER BY [a].[Age] DESC

运行结果如下：

[
 {"id":2,"name":"李四","gender":"女","age":40,"email":"22222222@qq.com","book":[]},
 {"id":3,"name":"王五","gender":"男","age":37,"email":"33333333@qq.com","book":[]},
 {"id":1,"name":"张三","gender":"男","age":35,"email":"11111111@qq.com","book":[]}
]

4.3、查询部分字段——Select

在上面例子中，数据库每次都会查询全部字段，如果希望只查询部分字段，则可以使用Select方法，下面的查询只返回Id、Name、Age字段，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Author>> Get()
        {
            return _dbContext.Set<Author>().Select(p => new Author 
            {
                Id = p.Id,
                Name = p.Name, 
                Age = p.Age 
            }).ToList();
        }
    }
}

打开SQL Server Profiler，可以发现后台执行的SQL语句如下：

SELECT [a].[Id], [a].[Name], [a].[Age]
FROM [Author] AS [a]

运行结果如下，可以发现Enail字段并未查询到数据：

[
 {"id":1,"name":"张三","gender":null,"age":35,"email":null,"book":[]},
 {"id":2,"name":"李四","gender":null,"age":40,"email":null,"book":[]},
 {"id":3,"name":"王五","gender":null,"age":37,"email":null,"book":[]}
]

4.4、数量——Count、LongCount

使用Count或LongCount可以获取数据表中的记录条数，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<string> Get()
        {
            int count = _dbContext.Set<Author>().Count();
            return $"Author表记录条数为：{count}";
        }
    }
}

打开SQL Server Profiler，可以发现后台执行的SQL语句如下：

SELECT COUNT(*)
FROM [Author] AS [a]

运行结果如下：

Author表记录条数为：3

如果数据表的记录条数超过了int类型最大值，也可以使用LongCount方法，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<string> Get()
        {
            long count = _dbContext.Set<Author>().LongCount();
            return $"Author表记录条数为：{count}";
        }
    }
}

打开SQL Server Profiler，可以发现后台执行的SQL语句如下：

SELECT COUNT_BIG(*)
FROM [Author] AS [a]

运行结果如下：

Author表记录条数为：3

4.5、记录是否存在——Any

使用Any方法可判断记录是否存在，下面将查询Author表中是否存在Name='张三'的记录，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<string> Get()
        {
            bool exist = _dbContext.Set<Author>().Any(p => p.Name == "张三");
            return exist ? "张三记录存在" : "张三记录不存在";
        }
    }
}

打开SQL Server Profiler，可以发现后台执行的SQL语句如下：

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Author] AS [a]
        WHERE [a].[Name] = N'张三') THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END

运行结果如下：

张三记录存在

4.6、获取唯一值——Distinct

使用Distinct方法可获取单列唯一值，下面将查询Author表中Gender列的唯一值，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<string> Get()
        {
            List<string> list = _dbContext.Set<Author>().Select(p => p.Gender).Distinct().ToList();
            string msg = "";
            foreach (string item in list)
            {
                msg += item + "\n";
            }
            return msg;
        }
    }
}

打开SQL Server Profiler，可以发现后台执行的SQL语句如下：

SELECT DISTINCT [a].[Gender]
FROM [Author] AS [a]

运行结果如下：

女
男

4.7、分组——GroupBy

使用GroupBy可对数据表记录进行分组，下面将使用Author表中的Gender字段进行分组，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Info>> Get()
        {
            List<Info> list = _dbContext.Set<Author>().Select(p => new Author
            {
                Gender = p.Gender
            })
            .GroupBy(p => p.Gender, (k, g) => new Info
            {
                Gender = k,
                Message = $"{k}共有{g.Count()}人"
            })
            .ToList();
            return list;
        }
    }

    public class Info
    {
        public string Gender { get; set; }
        public string Message { get; set; }
    }
}

打开SQL Server Profiler，可以发现后台执行的SQL语句如下：

SELECT [a].[Gender], COUNT(*)
FROM [Author] AS [a]
GROUP BY [a].[Gender]

运行结果如下：

[
 {"gender":"男","message":"男共有2人"},
 {"gender":"女","message":"女共有1人"}
]

4.8、分页——Skip、Take

使用Skip+Take的组合可以轻松实现分页查询，当前Author表中包含3条记录，如果按照每页2条记录划分，则会分成2页数据，下面查询第1页记录，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Author>> Get()
        {
            return _dbContext.Set<Author>().OrderBy(p => p.Id).Skip(0).Take(2).ToList();
        }
    }
}

打开SQL Server Profiler，可以发现后台执行的SQL语句如下：

exec sp_executesql N'SELECT [a].[Id], [a].[Age], [a].[Email], [a].[Gender], [a].[Name]
FROM [Author] AS [a]
ORDER BY [a].[Id]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=2

运行结果如下：

[
 {"id":1,"name":"张三","gender":"男","age":35,"email":"11111111@qq.com","book":[]},
 {"id":2,"name":"李四","gender":"女","age":40,"email":"22222222@qq.com","book":[]}
]

这里有一点要注意：目前EFCore生成的分页语句是offset+fetch格式，该命令从SQL Server 2012开始支持，如果项目中的数据库是SQL Server 2008则会报错，因为最新的EFCore已经不支持传统的select row_numbers()格式的分页语句。

4.9、根据主键查询——Find

使用Find方法可实现根据主键查询记录，与Where有所不同，Find每次会优先到缓存中进行查询，因此效率较高。下面将查询Author表中Id=3的记录，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<Author> Get()
        {
            return _dbContext.Set<Author>().Find(3);
        }
    }
}

打开SQL Server Profiler，可以发现后台执行的SQL语句如下：

exec sp_executesql N'SELECT TOP(1) [a].[Id], [a].[Age], [a].[Email], [a].[Gender], [a].[Name]
FROM [Author] AS [a]
WHERE [a].[Id] = @__p_0',N'@__p_0 int',@__p_0=3

运行结果如下：

{"id":3,"name":"王五","gender":"男","age":37,"email":"33333333@qq.com","book":[]}

5、EFCore中的连接查询

上面的代码主要演示了一些基础的查询操作，而在实际开发过程中，多表之间的连接查询也很常见，下面开始介绍EFCore中的连接查询。

5.1、Include导航属性

前面已经说过，Author与Book是一对多的关系，如果想查询Name='张三'的人一共写了多少本书该怎么做呢？有些同志可能会这么写：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Author>> Get()
        {
            return _dbContext.Set<Author>().Where(p => p.Name == "张三").ToList();
        }
    }
}

但运行结果显示"book":[]：

[{"id":1,"name":"张三","gender":"男","age":35,"email":"11111111@qq.com","book":[]}]

正确的打开方式应该是使用Include方法，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Author>> Get()
        {
            return _dbContext.Set<Author>().Include(p => p.Book).Where(p => p.Name == "张三").ToList();
        }
    }
}

运行一下代码，发现报错：

在这里插入图片描述
报出这个错误的主要原因就是：Author类中包含Book类产生的循环引用问题，解决方法很简单，使用NuGet引入如下组件，版本选择3.1.31：

Microsoft.AspNetCore.Mvc.NewtonsoftJson

在这里插入图片描述
在Startup.cs文件中添加忽略循环引用的代码：

using App.Context;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Newtonsoft.Json;

namespace App
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            // 添加控制器
            services.AddControllers().AddNewtonsoftJson(option =>
            {
                // 忽略循环引用
                option.SerializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
            });

            // 添加数据库上下文
            services.AddDbContext<DaoDbContext>(options =>
            {
                options.UseSqlServer(Configuration.GetConnectionString("ConnectionString"));
            });
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseHttpsRedirection();
            app.UseRouting();
            app.UseAuthorization();
            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

运行代码，输出结果如下所示：

[
    {
        "id":1,
        "name":"张三",
        "gender":"男",
        "age":35,
        "email":"11111111@qq.com",
        "book":[
                   {
                       "id":1,
                       "title":"《C程序设计》",
                       "press":"A出版社",
                       "publicationTime":"2022-01-01T00:00:00",
                       "price":30.0000,
                       "authorId":1
                   },
                   {
                       "id":2,
                       "title":"《C++程序设计》",
                       "press":"B出版社",
                       "publicationTime":"2022-02-02T00:00:00",
                       "price":45.0000,
                       "authorId":1
                   }
               ]
    }
]

5.2、Join内连接查询

Include方法在实体类中存在导航属性时非常方便，但更具普适性的方法是使用Join连接两张数据表，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Info>> Get()
        {
            var authors = _dbContext.Set<Author>().Where(p => p.Name == "张三");
            var books = _dbContext.Set<Book>();
            return authors.Join(books, a => a.Id, b => b.AuthorId, (a, b) => new Info
            {
                Id = a.Id,
                Name = a.Name,
                Title = b.Title
            }).ToList();
        }
    }

    public class Info
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Title { get; set; }
    }
}

打开SQL Server Profiler，可以发现后台执行的SQL语句如下：

SELECT [a].[Id], [a].[Name], [b].[Title]
FROM [Author] AS [a]
INNER JOIN [Book] AS [b] ON [a].[Id] = [b].[AuthorId]
WHERE [a].[Name] = N'张三'

运行结果如下：

[
 {"id":1,"name":"张三","title":"《C程序设计》"},
 {"id":1,"name":"张三","title":"《C++程序设计》"}
]

5.3、Join左连接查询

上面的代码生成的是一个Inner Join，也就是内连接查询。现在把需求改为：查询每个作者各写了多少本书，此时就得使用左连接查询了，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Info>> Get()
        {
            var authors = _dbContext.Set<Author>();
            var books = _dbContext.Set<Book>();
            return authors.SelectMany(a => books.Where(b => a.Id == b.AuthorId).DefaultIfEmpty(), (a, b) => new Info
            {
                Id = a.Id,
                Name = a.Name,
                Title = b.Title
            }).ToList();
        }
    }

    public class Info
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Title { get; set; }
    }
}

打开SQL Server Profiler，可以发现后台执行的SQL语句如下：

SELECT [a].[Id], [a].[Name], [b].[Title]
FROM [Author] AS [a]
LEFT JOIN [Book] AS [b] ON [a].[Id] = [b].[AuthorId]

运行结果如下：

[
 {"id":1,"name":"张三","title":"《C程序设计》"},
 {"id":1,"name":"张三","title":"《C++程序设计》"},
 {"id":2,"name":"李四","title":"《Java程序设计》"},
 {"id":2,"name":"李四","title":"《C#程序设计》"},
 {"id":3,"name":"王五","title":null}
]

5.4、GroupJoin左连接查询

上面使用了Join+SelectMany的方式实现左连接查询，其实EFCore也支持GroupJoin+SelectMany的方式实现左连接，代码如下

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Info>> Get()
        {
            var authors = _dbContext.Set<Author>();
            var books = _dbContext.Set<Book>();
            return authors.GroupJoin(books, a => a.Id, b => b.AuthorId, (a, b) => new 
                          { 
                              a, 
                              b 
                          })
                          .SelectMany(n => n.b.DefaultIfEmpty(), (n, b) => new Info
                          {
                              Id = n.a.Id,
                              Name = n.a.Name,
                              Title = b.Title
                          }).ToList();
        }
    }

    public class Info
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Title { get; set; }
    }
}

打开SQL Server Profiler，可以发现后台执行的SQL语句如下：

SELECT [a].[Id], [a].[Name], [b].[Title]
FROM [Author] AS [a]
LEFT JOIN [Book] AS [b] ON [a].[Id] = [b].[AuthorId]

运行结果如下：

[
 {"id":1,"name":"张三","title":"《C程序设计》"},
 {"id":1,"name":"张三","title":"《C++程序设计》"},
 {"id":2,"name":"李四","title":"《Java程序设计》"},
 {"id":2,"name":"李四","title":"《C#程序设计》"},
 {"id":3,"name":"王五","title":null}
]

5.5、Linq左连接查询

如果你不喜欢Lambda表达式的写法，觉得它太难读懂，那么也可以使用Linq的方式，使用Linq其实也是基于Join方法，但看起来可能更加通俗易懂，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Info>> Get()
        {
            var query = from a in _dbContext.Set<Author>()
                        join b in _dbContext.Set<Book>() on a.Id equals b.AuthorId into g
                        from r in g.DefaultIfEmpty()
                        select new Info
                        {
                            Id = r.Author.Id,
                            Name = r.Author.Name,
                            Title = r.Title
                        };
            return query.ToList();
        }
    }

    public class Info
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Title { get; set; }
    }
}

打开SQL Server Profiler，可以发现后台执行的SQL语句如下：

SELECT [a0].[Id], [a0].[Name], [b].[Title]
FROM [Author] AS [a]
LEFT JOIN [Book] AS [b] ON [a].[Id] = [b].[AuthorId]
LEFT JOIN [Author] AS [a0] ON [b].[AuthorId] = [a0].[Id]

运行结果如下：

[
 {"id":1,"name":"张三","title":"《C程序设计》"},
 {"id":1,"name":"张三","title":"《C++程序设计》"},
 {"id":2,"name":"李四","title":"《Java程序设计》"},
 {"id":2,"name":"李四","title":"《C#程序设计》"},
 {"id":0,"name":null,"title":null}
]

6、结语

本文主要介绍了EFCore中一些常见的查询操作。相较于编写原生SQL与数据库交互，EFCore使用实体类的方式更加快速简便。但在某些特殊情况下，我们可能仍旧需要执行原生SQL来获取数据，后续博客也会介绍如何在EFCore中执行原生SQL。

HerryDong

关注

7
点赞
踩
27

收藏

觉得还不错? 一键收藏
0
评论
ASP.NET Core 3.1系列（17）——EFCore中的查询操作

在之前的时代，很多应用系统都会使用、、等类来实现数据库的操作，这种方法主要通过编写原生与数据库进行交互。而在中，微软则更推荐开发人员使用。虽然本质上仍是基于，但对于开发人员来说只需要使用的方式即可实现操作，因此从操作便捷性和开发效率上来说，无疑更具优势，下面就来介绍一些中关于查询的相关操作。在中新建一个数据库，创建作者表、书表，代码如下：这里的与为一对多的关系，为表中的外键。表数据如下所示：表数据如下所示：使用引入组件，如下所示：在控制台输入如下命令生成实体类与数据库上下文：代码如下：代码如下：
复制链接

扫一扫

专栏目录