本文章记录的内容,衔接上一篇文章的相关代码
1.仅需要查询需要的字段,提升查询效率
查询文章ID为7的文章记录及对应的评论记录
EFCore对应的c#代码:
Article article = ctx.Articles.Include(a => a.Comments).Single(a => a.Id == 7);
EFCore生成的SQL为:
SELECT [t].[Id], [t].[Message], [t].[Title], [c].[Id], [c].[C_ArticleId],[c].[Message]
FROM (
SELECT TOP(2) [a].[Id], [a].[Message], [a].[Title]
FROM [Articles] AS [a]
WHERE [a].[Id] = CAST(7 AS bigint)
) AS [t]
LEFT JOIN [Comments] AS [c] ON [t].[Id] = [c].[C_ArticleId]
ORDER BY [t].[Id], [c].[Id]
根据生成的SQL语句可以看出,是查询出了文章表和评论表所有的字段。如果实际的应用场景中不需要出两张表的所有字段,那么可以换种写法,只查询出我们需要的字段,比如只查询文章表的文章内容及评论表的评论信息。
EFCore对应的c#代码:
var listComm = ctx.Comments.Include(a => a.C_Article).Select(s => new { CommentMsg = s.Message, ArticleMsg = s.C_Article.Message }).ToList();
EFCore生成的SQL为:
SELECT [c].[Message] AS [CommentMsg], [a].[Message] AS [ArticleMsg]
FROM [Comments] AS [c]
LEFT JOIN [Articles] AS [a] ON [c].[C_ArticleId] = [a].[Id]
2.添加额外的外键字段,提高查询效率
现在有一个需求,查询出评论表中ID为1的记录,所对应的文章ID,首先想到的写法是下面这样的:
var cmt = ctx.Comments.Select(c => new { CID = c.Id, AID = c.C_Article.Id }).Single(c => c.CID == 1);
EFCore生成的SQL为:
SELECT TOP(2) [c].[Id] AS [CID], [a].[Id] AS [AID]
FROM [Comments] AS [c]
LEFT JOIN [Articles] AS [a] ON [c].[C_ArticleId] = [a].[Id]
WHERE [c].[Id] = CAST(1 AS bigint)
这样查询的结果虽然满足了要求,但是在Comments(评论)表中本身就有C_ArticleId(文章ID)的字段,正常的SQL应该直接从评论表中取文章ID字段即可。
但因为在代码的评论实体类中并没有对应到C_ArticleId的属性,所以EFCore会自动关联文章表查询文章ID。
针对类似这样仅获取外键ID,不需要获取外键的其他字段信息,避免join另外一张表的情况。
解决方法是为Comment显式声明一个外键属性(数据库的表不需要改动),并在实体的配置文件中添加外键的配置说明。
①声明一个外键属性
②添加外键的配置说明
通过以上两步操作后,查询语句就变为下面这种写法,直接从评论表中取文章ID,不需要从文章表取ID。
var cmt = ctx.Comments.Select(c => new { CID = c.Id, AID = c.C_ArticleID }).Single(c => c.CID == 1);
EFCore生成的SQL也只是从评论表查询,不再关联文章表。
SELECT TOP(2) [c].[Id] AS [CID], [c].[C_ArticleID] AS [AID]
FROM [Comments] AS [c]
WHERE [c].[Id] = CAST(1 AS bigint)
以上的这些操作,其实是为了满足在不关联文章表的情况下,直接从评论表中查询出文章ID,如果没有这样的实际需求,则不需要该操作。