How to do a query from an object with a list of objects in Dapper C#

Question

Suppose I have the following tables:

tb_1: |user_id|user_name|email|age|

tb_2: |item_id|item_name|value|

tb_3: |user_id|item_id|

And I have the models below:

Item:

public class Item {
    public string Name {get; set;}
    public int Value {get; set;}
}

User:

public class User {
    public Guid UserId {get; set;}
    public List<Item> Itens {get; set;}
}

I am using the following Query to do the search:

using(var connection = ...)
{
    var query1 = "SELECT ... FROM tb_1";
    var query2 = "SELECT ... FROM tb_2 JOIN tb_3 ON ... WHERE tb_3.user_id = @UserId";
    var users = await connection.QueryAsync<User>(query1);
    foreach(var user in users)
    {
        user.Itens = await connection.QueryAsync<Item>(query2, user.UserId);
    }
    return users;
}

Is it possible to remove the foreach and use only one query?

PS: The tables are N to N.

Possible duplicate of [Correct use of Multimapping in Dapper](https://stackoverflow.com/questions/7472088/correct-use-of-multimapping-in-dapper) — Richard Hubley, Mar 14 '19 at 12:21
The example you put on the link is different, in case the link is 1-N and mine is N-N. — Vinicius Castro, Mar 14 '19 at 12:24
Would be the same question? Link: [Many to Many Dapper](https://stackoverflow.com/questions/51956443/multilevels-mapping-dapper-using-spliton) — Mario Guadagnin, Mar 15 '19 at 13:34
No, they're different questions. I'd like a way that does not use the foreach. In the question you submitted the solution uses the foreach. — Vinicius Castro, Mar 15 '19 at 13:53

score 0 · Accepted Answer · answered Mar 15 '19 at 13:29

I was able to solve the problem. I researched and I found a solution in Dapper documentation from the "one to many" query.

string sql = "SELECT TOP 10 * FROM Orders AS A INNER JOIN OrderDetails AS B ON A.OrderID = B.OrderID;";

using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{           
    var orderDictionary = new Dictionary<int, Order>();


    var list = connection.Query<Order, OrderDetail, Order>(
    sql,
    (order, orderDetail) =>
    {
        Order orderEntry;

        if (!orderDictionary.TryGetValue(order.OrderID, out orderEntry))
        {
        orderEntry = order;
        orderEntry.OrderDetails = new List<OrderDetail>();
        orderDictionary.Add(orderEntry.OrderID, orderEntry);
        }

        orderEntry.OrderDetails.Add(orderDetail);
        return orderEntry;
    },
    splitOn: "OrderID")
    .Distinct()
    .ToList();

    Console.WriteLine(list.Count);

    FiddleHelper.WriteTable(list);
    FiddleHelper.WriteTable(list.First().OrderDetails);
}

Reference: Query Multi-Mapping (One to Many)

score -1 · Answer 2 · answered Mar 14 '19 at 12:28

I would say "yes, but: rewrite the query to use a join", i.e.

var query = @"
SELECT ... FROM tb_1

SELECT ... FROM tb_2 JOIN tb_3 ON ... 
INNER JOIN (SELECT UserId FROM tb_1) x -- your original query here
    on tb_3.user_id = x.UserId   -- but as a sub-query
";

then use QueryMultiple. This returns two grids, so you'll need to read twice - then group and partition at the call-site.

using (var multi = connection.QueryMultiple(sql, ...))
{
    var users = multi.Read<User>().AsList();
    var allItems = multi.Read<Item>().AsList();

    var usersById = users.ToDictionary(x => x.Id);
    foreach(var item in allItems) {
        usersById[item.UserId].Items.Add(item);
    }
    return users;
}

I would like to know if is remove thhe foreach. I will edit my question. — Vinicius Castro, Mar 14 '19 at 12:34
@ViniciusCastro the foreach there is just the fixup - it is only one query — Marc Gravell, Mar 14 '19 at 14:08

How to do a query from an object with a list of objects in Dapper C#

2 Answers2