如何在LINQ / Entity Framework中使用此T-SQL查询？

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

我只处理一个数据库表/实体对象：NodePath。

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

给定一个特定的Node，我想根据这个查询得到它所有NodePath的子集：

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?

如何在我的VB.NET应用程序中执行此操作？

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

Dim ActivePaths = ???

2 个解决方案

#1

You can create a stored procedure, and then add that to your EDMX (model) to be called. Just right click and select "Update model from database", there should be a stored procedures tab. See also here.

您可以创建存储过程，然后将其添加到要调用的EDMX（模型）中。只需右键单击并选择“从数据库更新模型”，就应该有一个存储过程选项卡。另见这里。

Find the stored procedure in the Model Browser.

在模型浏览器中查找存储过程。

Right-click it and select Create Function Import.

右键单击它并选择“创建函数导入”。

Entity Data Model Create Function Import http://img31.imageshack.us/img31/9100/createfunctionimport.gif

实体数据模型创建函数导入http://img31.imageshack.us/img31/9100/createfunctionimport.gif

Choose what type of entities are returned. (in this case: NodePath)

选择返回的实体类型。（在这种情况下：NodePath）

Call the function from within your code:

从代码中调用函数：

Dim ActivePaths = context.ActivePaths(GivenNode.Id)

If you wanted to do this without a stored procedure, you'd have to use LINQ or Entity SQL. Or ADO.NET of course :)

如果您想在没有存储过程的情况下执行此操作，则必须使用LINQ或Entity SQL。或ADO.NET当然:)

#2

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.

我相信我已正确翻译了SQL，但如果有必要，我可以进行更改。这是为每个公共路径选择一个NodePath（基于最大的Created），只要它与NodeId匹配即可。

C# Solution:

C＃解决方案：

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):

VB.NET解决方案（我认为，不是我的主要语言）：

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)

#1