使用Linq to Entities在一对多连接中仅获取一个（最后一个）记录

Question

我在linq-to-entities中有以下内容。

clientprojects = (from p in this.SAPMappingEntities.SAP_Master_Projects 
join c in this.SAPMappingEntities.SAP_Master_ProjectPartners on c.project_no equals p.project_no
where c.partner_name.Contains(clientstring)
orderby p.start descending 
select new ClientProjects { client = c.partner_name, location = c.city +", "+c.region, project_no = c.project_no, start_dt = p.start, end_dt = p.finish }).Take(50).ToList();

我想修改此查询，仅获取每个SAP_Master_Project中最新更新日期的SAP_Master_ProjectPartners记录。如何实现此操作？

编辑：

有一个项目表，其中包含项目编号和项目详细信息，包括项目开始和结束日期。有一个项目合作伙伴表，其中包括项目合作伙伴编号、名称、项目编号、更新日期和其他详细信息。

SAP_MASTER_PROJECT

项目编号

开始时间

结束时间

SAP_MASTER_PROJECTPARTNERS

合作伙伴编号

项目编号

合作伙伴名称

城市

地区

更新日期

当用户在文本框中输入“ABC”时，我要返回的信息是最近50个项目（基于开始日期），其中包含或类似于“ABC”的项目合作伙伴名称的项目编号、项目开始日期、项目结束日期以及来自最后一个项目合作伙伴记录的项目合作伙伴名称、城市和州。

我相信有多种方法可以做到这一点，但是这个SQL语句给我提供了所需的结果：

SELECT TOP 50 p.project_no, p.start, p.finish, c.partner_name, c.city, c.region
FROM 
(select pp.project_no, pp.partner_name, pp.city, pp.region
from SAP_Master_ProjectPartners pp
where pp.partner_name LIKE @clientstring AND pp.update_dt = (select max(pp1.update_dt)
                       from SAP_Master_ProjectPartners pp1
                       where pp1.project_no = pp.project_no)) c
join SAP_Master_Projects p
on (p.project_no = c.project_no)
ORDER BY p.start DESC

编辑 #2 实际上，这个SQL语句返回了一些具有相同update_dt的项，因此我将其修改为以下内容。仍在努力将其转换为linq。

SELECT TOP 50 p.project_no, p.start, p.finish, c.partner_name, c.city, c.region, c.update_dt, c.row_id
FROM SAP_Master_Projects p
join
(select pp.project_no, pp.partner_name, pp.city, pp.region, pp.update_dt, pp.row_id
from SAP_Master_ProjectPartners pp
where pp.partner_name LIKE @clientstring AND pp.row_id = (select TOP 1 row_id
                       from SAP_Master_ProjectPartners pp1
                       where pp1.project_no = pp.project_no order by update_dt DESC)) c
on (p.project_no = c.project_no) where p.active_flag = 1
ORDER BY p.start DESC

- RememberME

---

你能提供一些关于实体结构的细节吗？你提到了update_dt，但很难确定它的确切位置。此外，你能澄清一下是否需要在这个查询中包含ClientProjects吗？ - chemicalNova

每个项目都有一个SAP_Master_Project条目。每个项目都有多个SAP_Master_ProjectPartners条目。我想将SAP_Master_Project与最后一个SAP_Master_ProjectPartners.update_dt的SAP_Master_ProjectPartners连接起来。对于每个项目/项目合作伙伴组合，我想返回一个条目（最后一个）。 - RememberME

你的模式让我感到困惑的一件事是，它似乎像是在提供历史记录，但实际上你创建的数据库却像是一个多对一的关系。这似乎是一个糟糕的数据库设计，因为如果你真的想保留历史记录，应该将其保存在一个单独的表格中（甚至是一个单独的模型）。如果你在这里做出改变，就永远无法为单个项目建模多个合作伙伴。而且每个合作伙伴只能有一个项目。看起来你真的应该有第三个表格来处理这些关系，使其成为多对多的关系。 - Merlyn Morgan-Graham

这可以让您保留历史记录，因为您只需更新映射以指向最新的记录。 - Merlyn Morgan-Graham

这些表已经存在很长时间，被多个应用程序使用。我只想查询这些数据并将其显示在屏幕上供用户查看。 - RememberME

Answer 1

如果您在SAP_Master_Projects和SAP_Master_ProjectPartners之间定义了一个实体关系，那么这个查询可能会更简单，因为连接可以是隐式的而不是显式的。

编辑 由于您无法这样做，因此类似这样的内容可能有效（使用let并在where子句中执行逻辑连接）：

var clientProjects =
    (
        from p in entities.SAP_Master_Projects
        let c = entities.SAP_Master_ProjectPartners
            .Where(cl => cl.partner_name.Contains(clientstring)
                && cl.project_no == p.project_no
                )
            .OrderBy(cl => cl.update_dt) // Todo: Might need to be descending?
            .FirstOrDefault()
        where c != null
        orderby p.start descending
        select new ClientProjects
        {
            client = c.partner_name,
            location = c.city + ", " + c.region,
            project_no = c.project_no,
            start_dt = p.start,
            end_dt = p.finish
        }
        )
    .Take(50)
    .ToList()
    ;

- Merlyn Morgan-Graham

---

我无法设置关系，因为它在数据库中没有设置，而且我不能触碰它。如果我在模型中设置它，那么当模型被更新时，它将被覆盖，其他开发人员也不会知道/记得每次都要添加回来。 - RememberME

@RememberME：那你遇到的问题是哪一部分呢？如果你编辑查询语句以解决该问题，并执行显式连接，这个查询仍然应该能够工作。 - Merlyn Morgan-Graham

@RememberME：你可能可以隐式地通过 where 子句添加连接。编辑答案以显示此内容。如果它有效，则我不知道性能如何。您可能需要尝试使用 LinqPad 对您的数据库进行查询并查看它生成的 SQL，然后与您的 DBA 联系以确保查询性能不会太差。 - Merlyn Morgan-Graham

需要我将 .First() 更改为 .FirstOrDefault()。然后选择所有 c 为空的项目。 - RememberME

现在添加了一个 where c!=null，它返回了正确的结果。不确定这是否是最好的方法，但它有效。 - RememberME

Answer 2

听起来你正在尝试想出以下查询：

SELECT *
  FROM MasterProjects p
       INNER JOIN (SELECT project_no,
                          partner_name
                     FROM ProjectPartners o
                    WHERE o.update_dt = (SELECT MAX(update_dt)
                                           FROM ProjectPartners i
                                          WHERE i.project_no = o.project_no)) c
               ON p.project_no = c.project_no
              AND p.partner_name = c.partner_name

我不确定如何将此转换为LINQ，但这是我最好的尝试：

var clientprojects =
    from p in MasterProjects
    join c in ProjectPartners on p.project_no == c.project_no
   where c.partner_name == (from o in ProjectPartners
                           where o.project_no == c.project_no
                             and o.update_dt == (from i in ProjectParters
                                                where o.project_no = i.project_no
                                               select i.update_dt).Max()
                          select o.partner_name).First();

上述的LINQ代码可能甚至无法编译，但希望它能指引您朝着正确的方向前进。

- Joshua

---

你需要使用 == 而不是 = 在你的 LINQ 中。 - cjk

哈哈哈哈，糟糕了，这很尴尬。我经常写C#和SQL - 昨晚可能是在SQL模式下。 :) 谢谢！ - Joshua

Answer 3

我不会说你的语言，抱歉。但是例如在 MySql 中，您可以添加 sort by update_dt DESC LIMIT 1 这样的语句，您能做到类似这样的操作吗？

- Mawg says reinstate Monica