How to map to a Dictionary object from database results using Dapper Dot Net?

asp.net c# dapper

Question

If I have a simple query such as:

string sql = "SELECT UniqueString, ID  FROM Table";

and I want to map it to a dictionary object such as:

Dictionary<string, int> myDictionary = new Dictionary<string, int>();      

How would I do this with Dapper?

I assume it is something like:

myDictionary = conn.Query<string, int>(sql, new {  }).ToDictionary();

But can't figure out the proper syntax.

Accepted Answer

There's various ways already shown; personally I'd just use the non-generic api:

var dict = conn.Query(sql, args).ToDictionary(
    row => (string)row.UniqueString,
    row => (int)row.Id);

Popular Answer

Works also without an additional class:

var myDictionary = conn.Query<string, int, KeyValuePair<string,int>>(sql, (s,i) => new KeyValuePair<string, int>(s,i))
    .ToDictionary(kv => kv.Key, kv => kv.Value);

NOTE: When using Dapper.NET 3.5 version, the Query method that takes the first, second and return types requires you specify more parameters, as the .NET 4.0 and .NET 4.5 versions take advantage of optional arguments.

In this case, the following code should work:

string splitOn = "TheNameOfTheValueColumn";
var myDictionary = conn.Query<string, int, KeyValuePair<string,int>>(sql, (s,i) => new KeyValuePair<string, int>(s,i), null, null, false, splitOn, null, null)
        .ToDictionary(kv => kv.Key, kv => kv.Value);

Most of the arguments will revert to a default, but splitOn is required, as it will otherwise default to a value of 'id'.

For a query that returns two columns, 'ID' and 'Description', splitOn should be set to 'Description'.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why