CODEWITHSUNDEEP
c#linq-to-sql
Navyseal
Navyseal

Reputation: 901

Building a list in a dictionary (LINQ to SQL)

I am try to build a dictionary with a list of dates as values.

I am not sure what extension method that I need to use inorder to get to the solution. Tried doing a ToList() on the value field but its throwing an exception.

Below is the code that I am using.

    GolfitoDataContext db = new GolfitoDataContext();
    var dic = db.GetTable<History>()
                .Select(p => new { p.Title, p.Date})
                .Where(x => x.Date >= startDateFilter && x.Date <= endDateFilter)
                .DistinctBy(x => x.Title)
                .AsEnumerable()
                .ToDictionary(k => k.Title, k => k.Date);

For example for the below data

Date                    Title
2013-07-18 22:51:45.000 QA
2013-07-18 22:52:30.000 Controller
2013-07-18 22:52:30.000 Controller
2013-07-18 22:58:00.000 Agent
2013-07-18 23:07:00.000 QA
2013-07-18 23:07:45.000 Controller
2013-07-18 23:08:30.000 Planning

I am trying to build a dictionary which will give me all the instances of individual titles(QA,Controller,etc.) and their occurrences (date on which the instances occurred). Basically building a Dictionary<string,List<DateTime>>

Upvotes: 0

Views: 332

Answers (3)

MarcinJuraszek
MarcinJuraszek

Reputation: 125640

You should use GroupBy:

var dic = db.GetTable<History>()
            .Select(p => new { p.Title, p.Date})
            .Where(x => x.Date >= startDateFilter && x.Date <= endDateFilter)
            .GroupBy(x => x.Title)
            .ToDictionary(g => g.Key, g => g.Select(x => x.Date).ToList());

Upvotes: 1

Rune FS
Rune FS

Reputation: 21742

you need to build the list before you try to build the dictionary. You can do this by use of group by

var dic = (from x in db.GetTable<History>()
          where x.Date >= startDateFilter && x.Date <= endDateFilter
          group x.Date by x.Title)
         .ToDictionary(grp => grp.Key, grp.ToList());

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1063198

The ToLookup method encompasses that:

GolfitoDataContext db = new GolfitoDataContext();
var dic = db.GetTable<History>()
            .Select(p => new { p.Title, p.Date})
            .Where(x => x.Date >= startDateFilter && x.Date <= endDateFilter)
            .ToLookup(k => k.Title, k => k.Date);

A lookup is basically the same as a multi-map, and can be used for example as:

foreach(var date in dic[title])
{
    // ...
}

Upvotes: 1

Related Questions