LINQ加入前1名 [英] LINQ Join on top 1

问题描述

我有三个连接在一起的对象(splistitemcollection)，效果很好，但问题是合同对象和客户对象之间存在一对多的关系.在加入期间，我只需要为每个合同对象获取第一个客户对象即可.

I've got three objects (splistitemcollection) that im joining together which is working great but the problem im having is that there are a one to many relationship between the a contract object and a customers object. I need to grab just just the first customers object for each contract object during the join.

这就是我要得到的

(Contract)(Customer)
12345  John Smith
12345  Jane Smith
67890  howard Jones
67890  Mary Jones

这就是我想要的 12345(简·约翰或约翰，只是其中一位顾客)

Here is what I want 12345 (just one of the customers, jane or john)

这是我当前正在使用的代码.

Here is the code im currently using.

  var joinedResults = from SPListItem contracts in _contractList
                      join SPListItem customers in _customerList
                      on contracts["ContractNumber"] equals customers["ContractNumber"]  
                      join SPListItem loans in _loanList
                      on contracts["ContractNumber"] equals loans["Contract_x0020_Number"] 
                      into l from loans in l.DefaultIfEmpty()
                      select new MergedData(contracts, customers, loans);

在SQL中，我将在联接上定义的子查询中定义一个select top子句，但我只是无法将自己的头包裹在新手linq大脑的语法周围.

In SQL i'd define a select top clause in a subquery defined on my join, I just cant wrap my head around the syntax for my newbie linq brain.

最终结果

  var joinedResults = from SPListItem contracts in _contractList
      join SPListItem customers in 
      // Derived subset
        (from SPListItem customers in _customerList
        group customers by customers["ContractNumber"] into groupedCustomers 
        select groupedCustomers.FirstOrDefault()
      )  on contracts["ContractNumber"] equals customers["ContractNumber"]  
      join SPListItem loans in _loanList
      on contracts["ContractNumber"] equals loans["Contract_x0020_Number"] into l
      from loans in l.DefaultIfEmpty()
      select new MergedData(contracts, customers, loans);

推荐答案

我将首先对其进行解释，因为LINQ有时看起来令人困惑.这个想法是让您的客户查询，并按ContractNumber分组，然后采用第一个.如果您希望可以按某个字段进行排序，以使其更具确定性(请始终按字母顺序使用最低的名称，依此类推)，然后您只需加入tempQuery即可，这基本上是与众不同的(ContractNumber)和第一个客户.

I'll explain it first, because the LINQ sometimes looks confusing. The idea is to take your customers query, and group by the ContractNumber, and then take the first. If you want you could order by some field, to have it be more deterministic (always taking the name lowest in alphabetical order, etc.) You then just join on your tempQuery which will be basically the distinct(ContractNumber) and first customer.

var tempQuery =  from SPListItem customers in _customerList
    group customers by customers["ContractNumber"] into gby 
    select gby.First();


var joinedResults =

    from SPListItem contracts in _contractList
    join SPListItem customer in tempQuery
on contract["ContractNumber"] equals customer["ContractNumber"]
    join SPListItem loans in _loanList
on contracts["ContractNumber"] equals loans["Contract_x0020_Number"] 
into l from loans in l.DefaultIfEmpty()
select new MergedData(
     contracts, 
     customer, 
     loans
   );

}

这篇关于LINQ加入前1名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！