Linq to Dictionary with Key and List

Question

My data is in two tables

Master Columns ID MyDateTime 1 07 Sept 2 08 Sept 

The MyDatetime column above has a unique index

 Detail Columns ID Data1 Data2 Data3 1 abc 1 xyz 1 fgh 2 abc 

I want to fill it in a dictionary. I tried

 Dictionary<DateTime, List<Detail>> result = ( from master in db.master from details in db.detail where (master.ID == detail.ID) select new { master.MyDateTime, details }).Distinct().ToDictionary(key => key.MyDateTime, value => new List<Detail> { value.details }); 

I expect two lines in a dictionary

 1, List of 3 rows as details 2, List of 1 row as details 

I get an error when it complains about a dictionary key entered twice. The key will be a datetime unique in master

Answer 1

This is exactly what search queries are for - so use ToLookup instead of ToDictionary :

 ILookup<DateTime, Detail> result = (from master in db.master join details in db.detail on master.ID equals detail.ID select new { master.MyDateTime, details }) .ToLookup(pair => pair.MyDateTime, pair => pair.details); 

(You do not need to use Distinct and note the use of the connection instead of the second from and where clause.)