How to Map to a Dictionary Object from Database Results Using Dapper Dot Net

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

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

How to turn dapper result into a dictionary using result mapping

Would this meet your needs?

var dict = connection.Query<int, Order, ValueTuple<int, Order>>(sql,
        (s, i) => ValueTuple.Create(s, i), null, null, true, "OrderID")
        .GroupBy(t => t.Item1, t => t.Item2, (k, v) => new {Key = k, List = v})
        .ToDictionary(kv => kv.Key, kv => kv.List);

Fiddle

Dapper.net use query into the Dictionary

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

How to map to a Dictionary from database using Dapper?

It looks like you're trying to group by the code. To do that, I would do something like:

public class TestClass
{
    public int Id {get;set;}
    public string Code {get;set;}
    public string Value1 {get; set;}
    public string Value2 {get; set;}
    public string Value3 {get; set;}
    public string Value4 {get; set;}
}
/// ...
testDic = conn.Query<TestClass>("SELECT * FROM Test").ToLookup(x=> x.Code);

The ToLookup here is standard LINQ which works a lot like a Dictionary<TKey, List<TValue>>, but inbuilt (it is essentially a lot like GroupBy, but intended to be accessed multiple times).

If you actually need a dictionary, it is more complex; ToDictionary won't really help you, so the most practical approach is probably:

var data = new Dictionary<string, List<TestClass>>();
foreach (var row in conn.Query<TestClass>("SELECT * FROM Test"))
{
    if (!data.TryGetValue(row.Code, out var list))
    {
        data.Add(row.Code, list = new List<TestClass>());
    }
    list.Add(row);
}

How to map to a Lookup<string, string> object from database results using Dapper?

ILookup<string, string> lookup = connection.Query<Table>(sql)
    .ToLookup(x => x.NonUniqueString, x => x.StringValue);

How do I map lists of nested objects with Dapper

Dapper is not a full blown ORM it does not handle magic generation of queries and such.

For your particular example the following would probably work:

Grab the courses:

var courses = cnn.Query<Course>("select * from Courses where Category = 1 Order by CreationDate");

Grab the relevant mapping:

var mappings = cnn.Query<CourseLocation>(
   "select * from CourseLocations where CourseId in @Ids", 
    new {Ids = courses.Select(c => c.Id).Distinct()});

Grab the relevant locations

var locations = cnn.Query<Location>(
   "select * from Locations where Id in @Ids",
   new {Ids = mappings.Select(m => m.LocationId).Distinct()}
);

Map it all up

Leaving this to the reader, you create a few maps and iterate through your courses populating with the locations.

Caveat the in trick will work if you have less than 2100 lookups (Sql Server), if you have more you probably want to amend the query to select * from CourseLocations where CourseId in (select Id from Courses ... ) if that is the case you may as well yank all the results in one go using QueryMultiple

Dapper maps object using second ID column rather than first

Try something like this:

var sql = @"SELECT e.id, e.FirstName, e.LastName, e.Nickname, 
                                em.id as em_id, em.Address as em_Address, em.Type as em_Type, 
                                jt.id as jt_id, jt.Name as jt_Name, 
                                p.id as p_id, p.Number as p_Number, p.Type as p_Type,
                                d.id as d_id, d.Name as d_Name,
                                es.id as es_id, es.Name as es_Name
                               

                      FROM dbo.Employees e 
                      LEFT JOIN dbo.Emails em
                      ON em.EmployeeID = e.id
                      LEFT JOIN dbo.JobTitles jt                           
                      ON e.JobTitleID = jt.id
                      LEFT JOIN Phones p
                      ON p.EmployeeID = e.id
                      LEFT JOIN dbo.Departments d
                      ON e.DepartmentID = d.id
                      LEFT JOIN dbo.EmployeeStatus es  
                      ON e.StatusID = es.id";

            var employees = await connection.QueryAsync<EmployeeModel,
                                                        EmailModel,
                                                        TitleModel,
                                                        PhoneModel,
                                                        DepartmentModel,
                                                        EmployeeModel>
                                                        (sql, (e, em, t, p, d) =>
            {
                e.EmailList.Add(em);
                e.JobTitle = t;
                e.PhoneList.Add(p);
                e.Department = d;
                return e;
            },
            splitOn: "em_id, jt_id, p_id, d_id");

Aggregating Dapper Multi Mapping Results To Object with List Within a List

Firstly, your Dapper query is not quite right: when using multi-mapping, the objects are split vertically in the resultset (some columns for the parent object, some for the child), and you need to provide the split points i.e. the starting columns for each object.

Then you map each object into its nested location, depending on whether it exists or not. For another level of nesting, you simply take the previous level and lookup again.

var historyDictionary = new Dictionary<string, Employee>();

var results = await connection.QueryAsync<Employee, Employment, JobInfo, Employee>(
    sqlQuery,
    (e, em, ji) => {
        if (!lookup.TryGetValue(e.Id, out var emp))
            historyDictionary.Add(e.Id, emp = e);

        if (emp.Employments == null)
            emp.Employments = new List<Employment> { em };
        else
        {
            em2 = emp.Employments.Find(ec => ec.CompanyId == em.CompanyId);
            if(em2 == null)
                emp.Employments.Add(em);
            else
                em = em2;
        }

        em.JobInfo = em.JobInfo ?? new List<JobInfo>();
        em.JobInfo.Add(ji);

        return emp;          
    }, SplitOn: nameof(Employment.CompanyId) + "," + nameof(JobInfo.JobTitle)
);

You can simplify the logic a bit by assigning default List objects in the constructors. Normally I would use Dictionary instead of List, and that is also simpler for doing lookups:

var historyDictionary = new Dictionary<string, Employee>();

var results = await connection.QueryAsync<Employee, Employment, JobInfo, Employee>(
    sqlQuery,
    (e, em, ji) => {
        if (!lookup.TryGetValue(e.Id, out var em2))
            historyDictionary.Add(e.Id, em2 = e);

        if (!emp.Employments.TryGetValue(em.CompanyId, out var em2))
            emp.Employments.Add(em.CompanyId, em2 = em);

        em2.JobInfo.Add(ji);

        return emp;          
    }, SplitOn: nameof(Employment.CompanyId) + "," + nameof(JobInfo.JobTitle)
);

You should also look into QueryMultiple, where you have multiple SELECT queries, and you map them in yourself. This prevents a lot of duplicate rows in some circumstances.

Is there a way retrieve each row as IDictionary<string, string> using Dapper?

No, Dapper doesn't do it for you (nor should it!). First of all - would the conversion be done in the database, or in code? And how are things formatted? (How do we format 2,000.20? Not all cultures use . as a decimal place, nor , as thousands separators. Gets even worse for dates).

Nevertheless, you can write something like this:

var data = Connection.Query("SELECT TOP 100 * FROM People") 
                      as IEnumerable<IDictionary<string, object>>;

var outData = data.Select(r => r.ToDictionary(d => d.Key, d => d.Value?.ToString()));

Or you can write your own extension:

public static class DapperExtensions
{
    public static IEnumerable<IDictionary<string, string>> QueryDictionary(this IDbConnection connection, string query)
    {
        var data = Dapper.SqlMapper.Query(connection, query) as IEnumerable<IDictionary<string, object>>;
        return data.Select(r => r.ToDictionary(d => d.Key, d => d.Value?.ToString()));
    }
}

And use it as so:

var data = Connection.QueryDictionary("SELECT TOP 100 * FROM People");

---

Related Topics