How to use Dapper and return a dictionary from the query?

c# dapper

Question

My sql is

select (select count(*) from TblRMember where sex=1) male,
 (select count(*) from TblRMember where sex=2) female,
 (select count(*) from TblRMember where sex=0) unkown

I want a dictionary from Dapper.Net that looks like this:

Keys:male,female,nukown
Value:10,30,50

I saw How can I use Dapper Dot Net to map database results to a Dictionary object?, but it can't possibly work!

How to do what I want using ToDictionary or another method

var myDictionary = sqlConnection.Query(strSql).ToDictionary(??);

Thanks!

1
-1
5/23/2017 10:27:58 AM

Accepted Answer

First, simplify your inquiry to just one word:

select case when sex = 1 then 'male'
            when sex = 2 then 'female'
            when sex = 0 then 'unknown'
       end as sex, count(*) as cnt
from TblRMember
group by sex

seeing assex is numerical, thus you must either choose by the name (sexname?)-containing column or modify your code. And then:

var myDictionary = sqlConnection.Query(strSql)
                    .ToDictionary(x => x.sex.ToString(), x => (int)x.cnt);
5
7/18/2013 7:48:26 AM

Popular Answer

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

Strong types and aliases are both acceptable.

The key points that correspond to the characteristics of the KeyValuePair type Key and Value are known as aliases.

It functions nicely and types quickly.

Dynamic typography is not my favorite. In certain circumstances, it results in catastrophe. Additionally, packaging and unpacking reduce performance.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow