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

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.

Answer 1

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); 

Answer 2

string strSql = "SELECT DISTINCT TableID AS [Key],TableName AS [Value] FROM dbo.TS_TStuctMaster"; Dictionary<string,string> dicts = sqlConnection.Query<KeyValuePair<string,string>>(strSql).ToDictionary(pair => pair.Key, pair => pair.Value); 

You can use aliases and strong types.

Aliases are the key points, which match the attributes of KeyValuePair type Key and Value.

It works under strong typing and runs well.

I don't like dynamic type. It brings disaster in certain situations. Moreover, the boxing and unboxing brings performance loss.