欢迎光临
我的个人博客网站

8、Entity Framework Core 3.1入门教程-查询关系数据


本文章是根据 微软MVP solenovex(杨旭)老师的视频教程编写而来,再加上自己的一些理解。
视频教程地址:https://www.bilibili.com/video/BV1xa4y1v7rR
GitHub源码:https://github.com/hllive/LearnEFCore3.1

预加载(Eager Loading)

把所有俱乐部和俱乐部的联赛全部查询出来,使用Include()方法关联起来

[HttpGet("Eager")] public IActionResult GetEager() {     var clubs = _dbContext.Clubs         .Include(c => c.League)//关联数据         .ToList();     return Ok(clubs); } 

生成的SQL语句
8、Entity Framework Core 3.1入门教程-查询关系数据
执行结果
8、Entity Framework Core 3.1入门教程-查询关系数据

这样查询所有数据是没有意义的,我们可以添加过滤条件,过滤条件在DbSet()后添加Where()方法;

[HttpGet("Eager")] public IActionResult GetEager() {     var clubs = _dbContext.Clubs         .Where(c => c.Name.Contains("足球队"))//过滤条件         .Include(c => c.League)//关联数据         .ToList();     return Ok(clubs); } 

注意!不能将ToList()或FirstDefault()放在Include()的前面,Include()只针对IQueryable返回类型才有Include()方法;另外DbSet()的Find()方法也不支持Include()

现在可以把俱乐部的所有队员加载进来,每个队员也有简历关联,关联简历使用ThenInclude()方法(级联添加关系数据)

Include是针对Clubs的关联,子属性需要关联就使用ThenInclude()方法
如果队员还需要关联GamePlayer的话,再使用ThenInclude()方法是不行的,在继续使用ThenInclude()方法的话是针对Resume的关联。

[HttpGet("Eager")] public IActionResult GetEager() {     var clubs = _dbContext.Clubs         .Where(c => c.Name.Contains("足球队"))   //过滤条件         .Include(c => c.League)                 //关联数据-联赛         .Include(c => c.Players)                //关联数据-队员             .ThenInclude(p => p.Resume)         //关联子属性的简历         .Include(c => c.Players)                //继续关联数据-队员             .ThenInclude(p => p.GamePlayers)    //关联子属性                 .ThenInclude(g => g.Game)       //GamePlayers关联Game子属性         .ToList();     return Ok(clubs); } 

最终生成的SQL语句

SELECT [c].[Id], [c].[City], [c].[DateOfEstablishment], [c].[History], [c].[LeagueId], [c].[Name], [l].[Id], [l].[Country], [l].[Name], [t0].[Id], [t0].[Birth], [t0].[ClubId], [t0].[Name], [t0].[ResumeId], [t0].[Id0], [t0].[Description], [t0].[PlayerId], [t0].[PlayerId0], [t0].[GameId], [t0].[Id1], [t0].[Round], [t0].[StartTime] FROM [Clubs] AS [c] LEFT JOIN [Leagues] AS [l] ON [c].[LeagueId] = [l].[Id] LEFT JOIN (     SELECT [p].[Id], [p].[Birth], [p].[ClubId], [p].[Name], [p].[ResumeId], [r].[Id] AS [Id0], [r].[Description], [r].[PlayerId], [t].[PlayerId] AS [PlayerId0], [t].[GameId], [t].[Id] AS [Id1], [t].[Round], [t].[StartTime]     FROM [Players] AS [p]     LEFT JOIN [Resumes] AS [r] ON [p].[Id] = [r].[PlayerId]     LEFT JOIN (         SELECT [g].[PlayerId], [g].[GameId], [g0].[Id], [g0].[Round], [g0].[StartTime]         FROM [GamePlayers] AS [g]         INNER JOIN [Games] AS [g0] ON [g].[GameId] = [g0].[Id]     ) AS [t] ON [p].[Id] = [t].[PlayerId] ) AS [t0] ON [c].[Id] = [t0].[ClubId] WHERE CHARINDEX(N'足球队', [c].[Name]) > 0 ORDER BY [c].[Id], [t0].[Id], [t0].[PlayerId0], [t0].[GameId], [t0].[Id1] 

最终查询出来的数据

[     {         "id": "21ec89ad-1b66-4b65-03e4-08d845203d1f",         "name": "茅台足球队",         "city": "贵州仁怀",         "dateOfEstablishment": "1999-07-01T00:00:00",         "history": null,         "league": {             "id": "edaaee79-78c9-43b5-a924-08d845203d11",             "name": "遵义仁怀足球联赛",             "country": null         },         "players": []     },     {         "id": "5d711109-2807-472d-6c10-08d847725f25",         "name": "新智联足球队",         "city": "贵州省贵阳市",         "dateOfEstablishment": "2020-08-23T00:00:00",         "history": "参加很多比赛",         "league": {             "id": "4227506d-05e4-47a2-b94f-08d8451d5dc0",             "name": "第一季度足球联赛-",             "country": "中国"         },         "players": [             {                 "id": "fa896d64-e87c-4087-4e18-08d847725f2b",                 "name": "王建国",                 "birth": "1994-08-02T00:00:00",                 "gamePlayers": [],                 "resumeId": "00000000-0000-0000-0000-000000000000",                 "resume": null             },             {                 "id": "916ea175-5aa9-4249-4e19-08d847725f2b",                 "name": "李刚",                 "birth": "1994-09-25T00:00:00",                 "gamePlayers": [],                 "resumeId": "00000000-0000-0000-0000-000000000000",                 "resume": null             },             {                 "id": "3fed8a06-6e9a-461e-2249-08d847cd3d8b",                 "name": "陈浩杰",                 "birth": "2000-05-06T00:00:00",                 "gamePlayers": [],                 "resumeId": "00000000-0000-0000-0000-000000000000",                 "resume": null             }         ]     } ] 

继续看例子,通过Linq表达式,相当于SQL语句的写法,选择不同的字段;使用Select()方法选择一些字段,使用匿名类,匿名类中包括自己的一些属性和关联属性的属性

[HttpGet("Eager2")] public IActionResult GetEager2() {     var clubs = _dbContext.Clubs         .Where(c => c.Name.Contains("足球队"))   //过滤条件         .Select(x => new         {             x.Id,//自己的属性             x.Name,//自己的属性             LeagueName = x.League.Name,//关联属性的属性             Players = x.Players.Where(p => p.Birth > new DateTime(2000, 1, 1))//查询过滤条件的队员         })         .ToList();     return Ok(clubs); } 

生成的SQL语句

SELECT [c].[Id], [c].[Name], [l].[Name], [t].[Id], [t].[Birth], [t].[ClubId], [t].[Name], [t].[ResumeId] FROM [Clubs] AS [c] LEFT JOIN [Leagues] AS [l] ON [c].[LeagueId] = [l].[Id] LEFT JOIN (     SELECT [p].[Id], [p].[Birth], [p].[ClubId], [p].[Name], [p].[ResumeId]     FROM [Players] AS [p]     WHERE [p].[Birth] > '2000-01-01' ) AS [t] ON [c].[Id] = [t].[ClubId] WHERE CHARINDEX(N'足球队', [c].[Name]) > 0 ORDER BY [c].[Id], [t].[Id] 

查询出来的数据

[     {         "id": "21ec89ad-1b66-4b65-03e4-08d845203d1f",         "name": "茅台足球队",         "leagueName": "遵义仁怀足球联赛",         "players": []     },     {         "id": "5d711109-2807-472d-6c10-08d847725f25",         "name": "新智联足球队",         "leagueName": "第一季度足球联赛-",         "players": [             {                 "id": "3fed8a06-6e9a-461e-2249-08d847cd3d8b",                 "name": "陈浩杰",                 "birth": "2000-05-06T00:00:00",                 "gamePlayers": [],                 "resumeId": "00000000-0000-0000-0000-000000000000",                 "resume": null             }         ]     } ] 

这种查询出来的结果是一个匿名类,匿名类Context不能进行变化追踪,只能追踪它识别的类=>DbSet()
但是匿名类中包括Context识别的类,上例子中的Players类是可以被Context识别并追踪的,这种也可以进行变化追踪操作。

显式加载(Explicit Loading)

通过对象逐一查询关联数据

[HttpGet("Explicit")] public IActionResult GetExplicit() {     //1、查询一条俱乐部数据     var club = _dbContext.Clubs.FirstOrDefault();     //2、通过查询出来的对象逐一查询关联数据-队员     _dbContext.Entry(club)         .Collection(x => x.Players)//关联队员集合数据         .Load();     //3、通过查询出来的对象逐一查询关联数据-联赛     _dbContext.Entry(club)         .Reference(x => x.League)//关联单个联赛数据         .Load();     return Ok(club); } 

8、Entity Framework Core 3.1入门教程-查询关系数据
从结果中看出执行了3次SQL语句查询

这种方法只能针对单个数据进行逐一查询关联数据,也就是单个俱乐部,如果针对list集合这种方法就不行。

也可以给查询语句添加过滤条件

[HttpGet("Explicit")] public IActionResult GetExplicit() {     //1、查询一条俱乐部数据     var club = _dbContext.Clubs.FirstOrDefault();     //2、通过查询出来的对象逐一查询关联数据-队员     _dbContext.Entry(club)         .Collection(x => x.Players)//关联队员集合数据         .Query().Where(x => x.Birth > new DateTime(2000, 1, 1))//添加过滤条件         .Load();     //3、通过查询出来的对象逐一查询关联数据-联赛     _dbContext.Entry(club)         .Reference(x => x.League)//关联单个联赛数据         .Load();     return Ok(club); } 

懒加载(Lazy Loading)

这种特性在EFCore中默认是关闭的,懒加载会遇到很多问题

其他查询

1、使用关联对象的一些属性作为查询过滤条件,以下是通过俱乐部关联的联赛按联赛的过滤查询条件获取数据

var data = _dbContext.Clubs.Where(x => x.League.Name.Contains("足球联赛")); 

2、查询多对多关系
由于GamePlayer没在Context的DbSet属性里,但实际是被追踪的,可以使用context.set<GamePlayer>(),如果遇到在Context的DbSet属性没有体现的类,可以使用context.set<class>()追踪

博客文章可以转载,但不可以声明为原创

赞(0) 打赏
未经允许不得转载:张拓的天空 » 8、Entity Framework Core 3.1入门教程-查询关系数据
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

专业的IT技术经验分享 更专业 更方便

联系我们本站主机

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏