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

32 篇文章 43 订阅

1、前言

在之前的ADO.NET时代,很多应用系统都会使用SqlConnectionSqlCommandSqlDataAdapter等类来实现数据库的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

这里的AuthorBook为一对多的关系,AuthorIdBook表中的外键。

Author表数据如下所示:

IdNameGenderAgeEmail
1张三3511111111@qq.com
2李四4022222222@qq.com
3王五3733333333@qq.com

Book表数据如下所示:

IdTitlePressPublicationTimePriceAuthorId
1《C程序设计》A出版社2022-01-01301
2《C++程序设计》B出版社2022-02-02451
3《Java程序设计》C出版社2022-03-03602
4《C#程序设计》D出版社2022-04-04552

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方法,下面的查询只返回IdNameAge字段,代码如下:

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

使用CountLongCount可以获取数据表中的记录条数,代码如下:

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导航属性

前面已经说过,AuthorBook是一对多的关系,如果想查询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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值