How to select related records in Dapper ASP.NET Core table?

Question

ASP.NET

I have three tables:
1) applicants (primary)
2) applicants_detail (linked table with the applicants table by ID->applicants_id)
3) applicants_document (linked table with the applicants table by ID->applicants_id)

That is, you need to select one record in the applicants table and select all related records in the applicants_detail and applicants_document tables


Postgresql, Npgsql, Dapper database. I am working on C# ASP.NET Core.

I select by the ID of the organization and by the status of the application the record.
Found 1 record in the applicants table, by applicants_id found 2 records in the applicants_detail table,
and 1 record in the applicants_document table. BUT, when choosing dapper, it gives me 2 identical records in the applicants_document table.
For example:

[
{"applicants_document_id":1,"applicants_id":2,"document":"Nalkadnoi-App_id-2_Req_id-6_Resp_id-4-23-02-2021.pdf","applicants_orgid":6,"warehous_orgid":4,"token":"5f5ef0f8-0e38-4276-a9ff-2bf61d7cebea","applicants":null},
{"applicants_document_id":1,"applicants_id":2,"document":"Nalkadnoi-App_id-2_Req_id-6_Resp_id-4-23-02-2021.pdf","applicants_orgid":6,"warehous_orgid":4,"token":"5f5ef0f8-0e38-4276-a9ff-2bf61d7cebea","applicants":null}
]

That is, splitOn does not work. Where am I doing wrong?

In the food repository:

using (IDbConnection dbConnection = Connection)
            {
                string sql = "SELECT a.applicants_id,a.orgid,a.applicant_date,a.applicant_time," +
                    "o.orgname_kz,o.orgname_ru,o.adres_kz,o.adres_ru,o.ip,o.active,o.tel,o.email," +
                    "d.applicants_id as applicants_id2,d.applicants_detail_id,d.productscountid," +
                    "d.quantity,p_count.price*d.quantity as sum,d.status,d.token," +
                    "doc.applicants_id as applicants_id3,doc.applicants_document_id," +
                    "doc.document,doc.applicants_orgid,doc.warehous_orgid FROM applicants as a " +
                    "LEFT OUTER JOIN applicants_detail as d ON d.applicants_id=a.applicants_id " +
                    "LEFT OUTER JOIN applicants_document as doc ON doc.applicants_id=a.applicants_id " +
                    "LEFT OUTER JOIN params.orgs as o ON o.orgid=a.orgid " +
                    "LEFT OUTER JOIN products_count as p_count ON p_count.productscountid=d.productscountid " +
                    "WHERE [email protected] AND [email protected]";
                var applicantsDic = new Dictionary<int, ApplicantsWarehousView>();
                var list = (await dbConnection.QueryAsync<ApplicantsWarehousView, ApplicantsWarehousDetailView,ApplicantsDocuments, ApplicantsWarehousView>(
                    sql, (applicants, applicantsDetail,applicantsDoc) =>
                    {
                        ApplicantsWarehousView applicantsEntry;
                        if (!applicantsDic.TryGetValue(applicants.applicants_id, out applicantsEntry))
                        {
                            applicantsEntry = applicants;
                            applicantsEntry.detailsView = new List<ApplicantsWarehousDetailView>();
                            applicantsEntry.documents = new List<ApplicantsDocuments>();
                            applicantsDic.Add(applicantsEntry.applicants_id, applicantsEntry);
                            
                        }

                        applicantsEntry.detailsView.Add(applicantsDetail);
                        applicantsEntry.documents.Add(applicantsDoc);
                        return applicantsEntry;
                    }, new { orgid = orgid,status=status }, splitOn: "applicants_id"
                    )).Distinct().ToList();
                return list;
            }

Answer 1

Use the syntax
@"";
This will get rid of the ugly pluses.
And here is this construction, in my opinion it does not apply to the request and does something in the
splitOn code: "applicants_id"
Look at its documentation for more details

Answer 2

Offhand - "o" is present in joins, but it does not have "applicants_id" for split.
And parameters -

<ApplicantsWarehousView, ApplicantsWarehousDetailView,ApplicantsDocuments, ApplicantsWarehousView>

you have 4 and should have 5