代码之家 › 专栏 › 技术社区 › brian brinley

前1名的LINQ Join

object join linq

brian brinley · 技术社区 · 14 年前

我有三个对象(splistitemcollection)我将它们连接在一起,这很好,但我遇到的问题是a contract对象和customers对象之间存在一对多的关系。在连接期间,我只需要为每个契约对象获取第一个customers对象。

这是我得到的

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

12345(只有一个客户,简或约翰)

这是我目前使用的代码。

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

最终结果

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

3 回复 | 直到 14 年前

Nix 14 年前

我先解释一下,因为林肯有时看起来很困惑。我们的想法是接受客户的查询,并按合同号分组,然后执行第一个查询。如果你想按某个字段排序,让它更具确定性(总是按字母顺序取最低的名称,等等),那么你只需加入 tempQuery 基本上是不同的(合同号)和第一个客户。

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

}

as-cii 14 年前

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

只需调用FirstOrDefault方法来选择找到的第一个项。

Sorax 14 年前

var joinedResults = from SPListItem contracts in _contractList
                      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 = _customerList.Where( c => c["ContractNumber"] == contracts["ContractNumber"].FirstOrDefault()
                      , loans);