There are classes:

public class Group { public int Group_id { get; set; } public string GroupName { get; set; } public List<Field> FieldList { get; set; } } public class Field { public int Field_id { get; set; } public string FieldName { get; set; } }

There is a request

 SELECT gr.id, gr.Name, f.id, f.Name FROM groups gr JOIN dbo.fields f ON gr.id = f.grId

Is it possible to fill in a List for 1 request so that there are N fields for each group?

Now I solve this problem by choosing groups, and then in the Foreach cycle I fill the groups with a separate request.

    2 answers 2

     public List<Group> GetList() { using (var cnn = _unitOfWork.GetConnection()) { var lookup = new Dictionary<int, Group>(); cnn.Query<Group, Field, Group>(@" SELECT gr.id as Group_id , gr.Name as GroupName , f.id as Field_id , f.Name as FieldName FROM groups gr JOIN dbo.fields f ON gr.id = f.grId ", (g, f) => { Group group; if (!lookup.TryGetValue(g.Group_id, out group)) { lookup.Add(g.Group_id, group = g); } if (group.FieldList == null) group.FieldList = new List<Field>(); group.FieldList.Add(f); return group; }, transaction: _unitOfWork.GetTransaction(), splitOn: "Field_id" ).AsQueryable(); var result = lookup.Values.ToList(); return result; } }

    UPDATE

    1. Added aliases to the query so that the field names with the property names match

    2. Added parameter splitOn: "Field_id"

    • unitOfWork do you have it? Is there any self-made or something to work with Dapper? - vitidev
    • Samopal, inserted into the project to check the code - Ruslan_K
    • and do not share such samopalom in order to self-education? - vitidev
    • unitOfWork did this for this manual , also used its Griffin.Framework library instead of Dappera - Ruslan_K
    • @Ruslan_K in that manual is not enough transaction.Dispose() - Pavel Mayorov

    Dapper has a built-in ability to share the results of a query - multi mapping. By default, it does this in column Id .

    Try this (if you don’t want to work, specify the splitOn: "id" parameter, perhaps it is case-sensitive):

     connection.Query<Group, Field, Group>( "SELECT gr.id, gr.Name, f.id, f.Name FROM groups gr JOIN dbo.fields f ON gr.id=f.grId", (field, group) => { group.FieldList.Add(field); return group; });

    Remember to initialize the list in the DTO:

     public class Group { public int Group_id { get; set; } public string GroupName { get; set; } public List<Field> FieldList { get; set; } public Group() { FieldList = new List<Field>(); } }