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 | |
---|---|---|---|---|
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
。