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.
Here is what I am getting
(Contract)(Customer) 12345 John Smith 12345 Jane Smith 67890 howard Jones 67890 Mary Jones
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);
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.
Final result
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);
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
);
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With