Entity Framework Core 6 expression with correlated subquery for latest items-CodePudding

I have a data model in SQL Server where there is a content table, a version table and meta data table that holds properties for the content. The model keeps track of content versions and each version will have a new set of meta data for each piece of content.

I can't figure out a good way to pull the latest set of meta data for all content at one time. I don't want to have to grab the content and then loop through each content item and then run an additional x queries for each content item.

Here's a simplified representation of the data model with some test data:

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

I would like to find the EF linq equivalent to this:

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

This query should return the following data:

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

I tried things like:

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();

as well as some other GroupBy attempts but nothing quite worked. Ideally I'd like to use Linq instead of running it via a stored procedure.

Any help would be greatly appreciated!

CodePudding user response：

Try this query:

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();

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

CodePudding user response：

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();

CodePudding user response：

Try the following query for 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 }