title: LINQ Join on top 1
body: <p>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.</p>
  
  <p>Here is what I am getting</p>
  
  <pre>
  (Contract)(Customer)
  12345  John Smith
  12345  Jane Smith
  67890  howard Jones
  67890  Mary Jones
  </pre>
  
  <p>Here is what I want
  12345 (just one of the customers, jane or john)</p>
  
  <p>Here is the code im currently using. </p>
  
  <pre><code>  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);
  </code></pre>
  
  <p>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. </p>
  
  <p>Final result</p>
  
  <pre><code>  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);
  </code></pre>