与linq-to-entities在一对多联接中仅获得一个(最后)记录 [英] Get only one (last) record in one-to-many join with linq-to-entities

问题描述

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

I have the following in 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仅获取具有最新update_dt的SAP_Master_ProjectPartners记录.我该怎么办?

I would like change this query so that for each SAP_Master_Project only get the SAP_Master_ProjectPartners record which has the latest update_dt. How can I do this?

编辑

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

There's a project table with a project number and project details including project start and end dates. There's a project partners table with the project partner number, name, project number, update date and other details.

SAP_MASTER_PROJECT

SAP_MASTER_PROJECT

project_no

project_no

开始

完成

SAP_MASTER_PROJECTPARTNERS

SAP_MASTER_PROJECTPARTNERS

partner_no

partner_no

project_no

project_no

合作伙伴名称

城市

区域

update_dt

update_dt

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

When the user enters "ABC" into a text box, the info I want to return is the project number, project start date, project end date plus project partner name, city, and state from the last project partner record for the last 50 projects (based on start date) where the project partner name contains or is like "ABC".

我敢肯定有多种方法可以执行此操作，但是他的SQL可以为我提供所需的结果:

I'm sure there's more than one way to do this, but his SQL gives me the results that I need:

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的项目，因此我将sql修改为以下内容.仍在努力转换为linq.

EDIT #2 That sql actually returns a few items which have the same update_dt, so I modified the sql to below. Still struggling to convert to 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

推荐答案

如果在SAP_Master_Projects和SAP_Master_ProjectPartners之间定义了实体关系，则此查询可能会更简单，因此联接可以是隐式的，而不是显式的.

This query would probably be simpler if you defined an entity relationship between SAP_Master_Projects and SAP_Master_ProjectPartners so the join could be implicit instead of explicit.

编辑由于您无法执行此操作，因此可能会执行以下操作(使用let并在where子句中进行逻辑联接)​​:

Edit Since you can't do that, something like this might work (using let and doing a logical join within a where clause):

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

这篇关于与linq-to-entities在一对多联接中仅获得一个(最后)记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！