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

rvpgvaaj  于 2023-01-15  发布在  其他
关注(0)|答案(3)|浏览(120)

我在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,而不是通过存储过程运行它。
任何帮助将不胜感激!

mdfafbf1

mdfafbf11#

尝试以下查询:

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]
xpszyzbs

xpszyzbs2#

对于EF核心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();
bn31dyow

bn31dyow3#

尝试以下EF Core previous 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 }

相关问题