Entity Framework Core 6表达式，包含最新项的相关子查询

Question

我在SQL Server中有一个数据模型，其中有一个内容表，一个版本表和元数据表，其中包含内容的属性。该模型跟踪内容版本，每个版本将为每个内容片断提供一组新的元数据。

我想不出一个好方法来一次为所有内容提取最新的元数据集。我不想抓取内容，然后循环遍历每个内容项，然后为每个内容项运行额外的x个查询。

下面是带有一些测试数据的数据模型的简化表示:

DROP TABLE content
DROP TABLE [version]
DROP TABLE meta
CREATE TABLE dbo.[Content] 
( 
[Id] [int] NOT NULL PRIMARY KEY, 
[Name] VARCHAR(250) NOT NULL 
) ON [PRIMARY] 
CREATE TABLE dbo.[Version] 
(
[Id] [int] NOT NULL PRIMARY KEY, 
[ContentId] int NOT NULL,
[CreatedOn] DATETIME NOT NULL 
) ON [PRIMARY] 
CREATE TABLE dbo.[Meta] 
( 
[Id] [int] NOT NULL PRIMARY KEY, 
[ContentId] int NOT NULL,
[Value] nvarchar(100) NULL,
[VersionId] int NOT NULL
) ON [PRIMARY] 
INSERT dbo.Content (Id, [Name]) VALUES (1, N'Article 1') 
INSERT dbo.Content (Id, [Name]) VALUES (2, N'Article 2') 
INSERT dbo.Content (Id, [Name]) VALUES (3, N'Article 3') 
INSERT dbo.[Version] (Id, ContentId, CreatedOn) VALUES (1, 1, '1/1/2023')
INSERT dbo.[Version] (Id, ContentId, CreatedOn) VALUES (2, 1, '1/2/2023')
INSERT dbo.[Version] (Id, ContentId, CreatedOn) VALUES (3, 2, '1/3/2023')
INSERT dbo.[Version] (Id, ContentId, CreatedOn) VALUES (4, 2, '1/4/2023')
INSERT dbo.[Version] (Id, ContentId, CreatedOn) VALUES (5, 3, '1/5/2023')
INSERT dbo.[Version] (Id, ContentId, CreatedOn) VALUES (6, 3, '1/6/2023')
INSERT dbo.Meta(Id, ContentId, Value, VersionId) VALUES (1, 1, 'test title 1', 1)
INSERT dbo.Meta(Id, ContentId, Value, VersionId) VALUES (2, 1, 'test body 1', 1)
INSERT dbo.Meta(Id, ContentId, Value, VersionId) VALUES (3, 1, 'final title 1', 2)
INSERT dbo.Meta(Id, ContentId, Value, VersionId) VALUES (4, 1, 'final body 1', 2)
INSERT dbo.Meta(Id, ContentId, Value, VersionId) VALUES (5, 2, 'test title 2', 3)
INSERT dbo.Meta(Id, ContentId, Value, VersionId) VALUES (6, 2, 'test body 2', 3)
INSERT dbo.Meta(Id, ContentId, Value, VersionId) VALUES (7, 2, 'final title 2', 4)
INSERT dbo.Meta(Id, ContentId, Value, VersionId) VALUES (8, 2, 'final body 2', 4)
INSERT dbo.Meta(Id, ContentId, Value, VersionId) VALUES (9, 3, 'test title 3', 5)
INSERT dbo.Meta(Id, ContentId, Value, VersionId) VALUES (10, 3, 'test body 3', 5)
INSERT dbo.Meta(Id, ContentId, Value, VersionId) VALUES (11, 3, 'final title 3', 6)
INSERT dbo.Meta(Id, ContentId, Value, VersionId) VALUES (12, 3, 'final body 3', 6)
SELECT * FROM dbo.Content
SELECT * FROM dbo.[Version]
SELECT * FROM dbo.Meta

我想找到等效的EF linq:

SELECT 
m.Id, m.ContentId, m.[Value], m.VersionId
FROM 
dbo.Meta AS m
INNER JOIN 
(SELECT t.Id
FROM 
(SELECT 
v.Id, 
ROW_NUMBER() OVER (PARTITION BY v.ContentId ORDER BY v.CreatedOn DESC) AS [row]
FROM 
dbo.[Version] AS v) AS t
WHERE 
t.[row] <= 1) AS z ON m.VersionId = z.Id

这个查询应该返回以下数据:

3   1   final title 1   2
4   1   final body 1    2
7   2   final title 2   4
8   2   final body 2    4
11  3   final title 3   6
12  3   final body 3    6

我试过这样做:

var list = (from p in db.Meta
from latestVersion in db.Version
.Where(o => o.ContentId == p.ContentId)
.OrderByDescending(o => o.CreatedOn) // get only most recent
.Take(1)).ToList();

以及其他一些GroupBy尝试，但都没有成功。理想情况下，我想使用Linq而不是通过存储过程运行它。

任何帮助都将非常感激!

Answer 1

试试这个查询:

var list = (
from content in db.Contents
join ver in db.Versions on content.Id equals ver.ContentId into versions
from lastVersion in versions.OrderByDescending(v => v.CreatedOn).Take(1)

join meta in db.Meta on new { contentId = content.Id, versionId = lastVersion.Id } 
equals new { contentId = meta.ContentId, versionId = meta.VersionId }
select new { /* content, */ meta, lastVersion }
).ToList();

产生SQL:

SELECT [m].[Id], [m].[ContentId], [m].[Value], [m].[VersionId], [t].[Id], [t].[ContentId], [t].[CreatedOn]
FROM [Content] AS [c]
CROSS APPLY (
SELECT TOP(1) [v].[Id], [v].[ContentId], [v].[CreatedOn]
FROM [Version] AS [v]
WHERE [c].[Id] = [v].[ContentId]
ORDER BY [v].[CreatedOn] DESC
) AS [t]
INNER JOIN [Meta] AS [m] ON [c].[Id] = [m].[ContentId] AND [t].[Id] = [m].[VersionId]

Answer 2

For EFCore 6:

var list1 = (
from ver in (
from v in db.Versions 
group v by v.ContentId into versions
select new { ContentId = versions.Key, CreatedOn = versions.Max(v => v.CreatedOn) }
)
join v2 in db.Versions on ver equals new { v2.ContentId, v2.CreatedOn }
join meta in db.Meta on v2.Id equals meta.VersionId
select meta
).ToList();

Answer 3

尝试以下查询EF Core prior 7:

var query =
from content in db.Contents
from lastVersion in db.Versions
.Where(ver => content.Id == ver.ContentId)
.OrderByDescending(ver => ver.CreatedOn)
.Take(1)  
join meta in db.Meta on new { contentId = content.Id, versionId = lastVersion.Id } 
equals new { contentId = meta.ContentId, versionId = meta.VersionId }
select new { /* content, */ meta, lastVersion }