Dapper.net use query into the Dictionary

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 Dapper.Net to return a Dictinary like this:

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

I have see How to map to a Dictionary object from database results using Dapper Dot Net? ,but that can not work!

How to use ToDictionary or other way to implement I want

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

Thanks!

@shahkalpesh in mysql,the column is male,femail,unkown ,like this... — zt9788, Jul 18 '13 at 07:37

gzaxx · Accepted Answer · 2013-07-18T07:48:26.617

5

First change your query to be single one:

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

as I see sex is numerical, so you either have to select by coumn with name (sexname?) or change it in your code. After that:

var myDictionary = sqlConnection.Query(strSql)
                    .ToDictionary(x => x.sex.ToString(), x => (int)x.cnt);

edited Jul 18 '13 at 07:48

answered Jul 18 '13 at 07:35

gzaxx

17,312
2
36
54

,thanks~~ I'm stupid ~ ~ this solution is very easy!!!I also want to know, my SQL can be implemented? – zt9788 Jul 18 '13 at 07:40
@gzaxx: Please add the clause that makes the numeric value of sex, into key such as Male/Female/Unknown. +1 – shahkalpesh Jul 18 '13 at 07:42

Allen.Cai · Answer 2 · 2018-08-13T05:13:48.240

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.

Dapper.net use query into the Dictionary

2 Answers2