LINQ 加入顶部 1

Question

我有三个对象(splistitemcollection)，我将它们连接在一起，这很好用，但我遇到的问题是契约(Contract)对象和客户对象之间存在一对多的关系。在加入期间，我只需要获取每个契约(Contract)对象的第一个客户对象。

这是我得到的

(Contract)(Customer)12345  John Smith12345  Jane Smith67890  howard Jones67890  Mary Jones

Here is what I want12345 (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 中，我会在我的 join 上定义的子查询中定义一个 select top 子句，我只是无法围绕我的新手 linq 大脑的语法。

最后结果

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

Answer 1

我先解释一下，因为 LINQ 有时看起来很困惑。这个想法是让您的客户查询，并按 ContractNumber 分组，然后取第一个。如果您希望您可以按某个字段排序，使其更具确定性(总是按字母顺序取最低的名称等)然后您只需加入您的 tempQuery这将基本上是 distinct(ContractNumber) 和第一个客户。

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

}