How can I use this T-SQL query in LINQ / Entity Framework?

Question

I'm only dealing with one database table / entity object: NodePath.

Given a particular Node, I want to get only a subset of all its NodePaths according to this query:

select
    *
from
    NodePath
where
    NodeId = @GivenNodeId and
    Id in
    (
        --active paths
        select
            a.Id
        from
            NodePath a join
        (
                select
                    [Path],
                    max(Created) as Created
                from
                    NodePath
                group by
                    [Path]
        ) b on
        a.[Path] = b.[Path] and
        a.Created = b.Created
    )

How can I execute this in my VB.NET application?

Dim AllPaths = GivenNode.NodePaths.OrderByDescending(Function(p) p.Created)

Dim ActivePaths = ???

Answer 1

I believe I've translated that SQL correctly, but I can make changes if necessary. This is selecting one NodePath for each common Path (based on the greatest Created), so long as it matches the NodeId.

C# Solution:

var nodePaths = (from p in context.NodePaths
                 group p by p.Path into g
                 select g.OrderByDescending(i => i.Created).First()
                 ).Where(p => p.NodeId == givenNodeId);

VB.NET Solution (I think, not my primary language):

Dim nodePaths = (From p In context.NodePaths _
                 Group p By p.Path Into Group _
                 Select Group.OrderByDescending(Function(i) i.Created).First() _
                 ).Where(Function(p) p.NodeId = givenNodeId)