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.

Answer 1

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)