Имею три таблицы:
1) applicants (первичная)
2) applicants_detail (связанная таблица с таблицей applicants по ID->applicants_id)
3) applicants_document (связанная таблица с таблицей applicants по ID->applicants_id)
То есть, надо выбрать один запись в таблице applicants и выбрать все связанные записи в таблицах applicants_detail и applicants_document
База Postgresql, Npgsql, Dapper. работаю на C# ASP.NET Core.
Выбираю по ID организации и по статусу заявки запись.
Найдено 1 запись в таблице applicants, по applicants_id найдено 2 записи в таблице applicants_detail,
и в таблице applicants_document 1 запись. НО при выборе dapper мне выдает 2 одинаковой записи в таблице applicants_document.
Например:
[
{"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}
]
То есть не отрабатывает splitOn. Где я делаю не правильно?
В репозитории пищу:
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 d.orgid=@orgid AND d.status=@status";
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;
}
