Linq-to-SQL ToDictionary()

Question

How do I properly convert two columns from SQL (2008) using Linq into a Dictionary (for caching)?

I currently loop through the IQueryable b/c I can't get the ToDictionary method to work. Any ideas? This works:

var query = from p in db.Table
            select p;

Dictionary<string, string> dic = new Dictionary<string, string>();

foreach (var p in query)
{
    dic.Add(sub.Key, sub.Value);
}

What I'd really like to do is something like this, which doesn't seem to work:

var dic = (from p in db.Table
             select new {p.Key, p.Value })
            .ToDictionary<string, string>(p => p.Key);

But I get this error:

Cannot convert from 'System.Linq.IQueryable<AnonymousType#1>' to 'System.Collections.Generic.IEnumerable'

score 131 · Accepted Answer · answered Oct 28 '08 at 22:53

131

var dictionary = db
    .Table
    .Select(p => new { p.Key, p.Value })
    .AsEnumerable()
    .ToDictionary(kvp => kvp.Key, kvp => kvp.Value)
;

answered Oct 28 '08 at 22:53

yfeldblum

65,165
12
129
169

Sorry, I may have not been clear, I've tried thie before, but this creates a Dictionary, not Dictionary – Codewerks Oct 28 '08 at 22:57
Try `kvp => kvp.Value as string`. The point of the answer was `.AsEnumerable()`. – yfeldblum Oct 28 '08 at 23:01
Thanks, yeah I figured AsEnumerable was needed, but the ToDictionary call still doesn't work. Both columns are varchars in SQL, so they come back as strings, but I can't figure out how to get it to stuff into a Dic.... – Codewerks Oct 28 '08 at 23:04
I don't know if this is still relevant, but why is the `AsEnumerable` needed? For me it works without that too, but probably Linq has already changed (7 years have passed) – Alexander Derck Nov 20 '15 at 10:22
1

@AlexanderDerck - At the time, the `AsEnumerable` was necessary. I don't remember precisely why, after all this time, but a possible explanation is that `ToDictionary` was an extension method on `IEnumerable`, but the interface for Linq-to-SQL was not `IEnumerable`. – yfeldblum Nov 22 '15 at 23:12

score 19 · Answer 2 · edited Mar 27 '13 at 12:54

19

You are only defining the key, but you need to include the value also:

var dic = (from p in db.Table
             select new {p.Key, p.Value })
            .ToDictionary(p => p.Key, p=> p.Value);

edited Mar 27 '13 at 12:54

Indy9000

8,651
2
32
37

answered Oct 28 '08 at 22:57

Christian C. Salvadó

807,428
183
922
838

3

-1 The error is that the `` part makes it use the `public static Dictionary ToDictionary(this IEnumerable source, Func keySelector, IEqualityComparer comparer);` overload instead of the `public static Dictionary ToDictionary(this IEnumerable source, Func keySelector, Func elementSelector);` overload. – user247702 Nov 27 '12 at 11:05
I think it still needs `.AsEnumerable()` before `.ToDictionary()` – AceMark Jan 28 '14 at 07:58
worked perfectly ... finally the one I was looking for – Brian Jun 17 '15 at 15:15

score 9 · Answer 3 · answered Oct 28 '08 at 23:07

9

Thanks guys, your answers helped me fix this, should be:

var dic = db
        .Table
        .Select(p => new { p.Key, p.Value })
        .AsEnumerable()
        .ToDictionary(k=> k.Key, v => v.Value);

answered Oct 28 '08 at 23:07

Codewerks

5,884
5
29
33

5

The reason you need AsEnumerable() is because LINQ to SQL doesn't mix local and remote (SQL) processing so this causes the first part to execute on the SQL server and then the final subsequent part to execute locally using LINQ to Objects which can do Dictionarys :) – DamienG Oct 29 '08 at 00:57
Makes sense. Also, the second parameter in ToDictionary needs its own Func arguments, which was tripping me up earlier. – Codewerks Oct 29 '08 at 01:33

score 2 · Answer 4 · answered Jan 18 '10 at 19:46

2

Why would you create an anonymous object for every item in the table just to convert it?

You could simply use something like: IDictionary<string, string> dic = db.Table.ToDictionary(row => row.Key, row => row.Value); You may need to include an AsEnumerable() call between Table and ToDictionary(). I don't know the exact type of db.Table.

Also correct the first sample, your second loop variable is mismatching at declaration and usage.

answered Jan 18 '10 at 19:46

TWiStErRob

44,762
26
170
254

2

Because `ToDictionary` is in-memory. Not creating an anonymous object means all columns are retrieved from the database instead of just the ones you need. – user247702 Nov 27 '12 at 12:11
`ToDictionary` doesn't know about the underlying structure, it just calls the two callbacks for key and value (in this case simple property selecting lambdas), so this `ToDictionary` call should be functionally equivalent to the foreach in the example he provided. As far as I know all of LINQ methods are deferred, meaning callbacks are only called when necessary. – TWiStErRob Nov 28 '12 at 12:47
3

If you look with SQL Server Profiler, you'll see that with an anonymous object only two columns are selected, and without it all columns are selected. – user247702 Nov 28 '12 at 13:15

Linq-to-SQL ToDictionary()

4 Answers4

Linked