I have a query to get a single document:-
SELECT doc.Id
,doc.Type
,doc.Name
,doc.Description
,doc.File
,doc.CreatedAt
,doc.UpdatedAt
,dl.LabelId
,l.Name
,l.Description
,dc.DocumentId
,dc.ProfileId
,dc.Role
FROM Documents as doc
LEFT JOIN DocumentCollaborators as dc
ON doc.Id = dc.DocumentId
LEFT JOIN DocumentLabels as dl
ON doc.Id = dl.DocumentId
LEFT JOIN Labels as l
ON dl.LabelId = l.Id
WHERE doc.Id = @Id
I query this with Dapper as follows:
var documents = await connection.QueryAsync<DocumentDto, LabelDto, CollaboratorDto, DocumentDto>(
GetDocumentSql,
(doc, label, collaborator) => {
if (label.LabelId != Guid.Empty) // empty guid means no label
{
doc.Labels.Add(label);
}
if (collaborator.ProfileId != Guid.Empty) // empty guid means no collaborator
{
doc.Collaborators.Add(collaborator);
}
return doc;
},
new
{
Id = documentId.ToString()
},
splitOn: "LabelId,DocumentId");
My DTOs are as follows:
public abstract class BaseDocumentDto
{
public Guid Id { get; set; }
public int Type { get; set; }
public string Name { get; set; } = string.Empty;
public string Description { get; set; } = string.Empty;
public string File { get; set; } = string.Empty;
public DateTime CreatedAt { get; set; }
public DateTime UpdatedAt { get; set; }
}
public class DocumentDto : BaseDocumentDto
{
public IList<LabelDto> Labels { get; set; } = new List<LabelDto>();
public IList<CollaboratorDto> Collaborators { get; set; } = new List<CollaboratorDto>();
}
public class LabelDto
{
public Guid LabelId { get; set; }
public string Name { get; set; } = string.Empty;
public string Description { get; set; } = string.Empty;
public DateTime CreatedAt { get; set; }
public DateTime UpdatedAt { get; set; }
}
public class CollaboratorDto
{
public Guid ProfileId { get; set; }
public int Role { get; set; }
}
If I try and run this where the database has more than one record for either labels or collaborators then I only get the first one in the returned DocumentDTO.
What am I doing wrong?
UPDATE Here is some example data I get back
| Id | Type | Name | Description | File | CreatedAt | UpdatedAt | LabelId | Name | Description | DocumentId | ProfileId | Role |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| c255293f-2461-4013-a2a5-ee8827927db5 | 1 | Test Document for debugging | This document is used for debugging purposes | https://test.document.com | 2021-07-01 00:00:00 | 2021-07-01 00:00:00 | 023b5c62-0f4d-4937-a7b7-4699b74c1380 | Test Label for debugging 001 | This label is used for debugging purposes | c255293f-2461-4013-a2a5-ee8827927db5 | 16c20d76-53dc-460f-918a-1d8fedf0e4bb | 1 |
| c255293f-2461-4013-a2a5-ee8827927db5 | 1 | Test Document for debugging | This document is used for debugging purposes | https://test.document.com | 2021-07-01 00:00:00 | 2021-07-01 00:00:00 | 023b5c62-0f4d-4937-a7b7-4699b74c1380 | Test Label for debugging 001 | This label is used for debugging purposes | c255293f-2461-4013-a2a5-ee8827927db5 | 925b6d87-3113-42f7-924a-6da0b8495b44 | 2 |
| c255293f-2461-4013-a2a5-ee8827927db5 | 1 | Test Document for debugging | This document is used for debugging purposes | https://test.document.com | 2021-07-01 00:00:00 | 2021-07-01 00:00:00 | 8500eeae-0f27-44ba-97c1-7f2b03f0e273 | Test Label for debugging 002 | This label is used for debugging purposes | c255293f-2461-4013-a2a5-ee8827927db5 | 16c20d76-53dc-460f-918a-1d8fedf0e4bb | 1 |
| c255293f-2461-4013-a2a5-ee8827927db5 | 1 | Test Document for debugging | This document is used for debugging purposes | https://test.document.com | 2021-07-01 00:00:00 | 2021-07-01 00:00:00 | 8500eeae-0f27-44ba-97c1-7f2b03f0e273 | Test Label for debugging 002 | This label is used for debugging purposes | c255293f-2461-4013-a2a5-ee8827927db5 | 925b6d87-3113-42f7-924a-6da0b8495b44 | 2 |
| c255293f-2461-4013-a2a5-ee8827927db5 | 1 | Test Document for debugging | This document is used for debugging purposes | https://test.document.com | 2021-07-01 00:00:00 | 2021-07-01 00:00:00 | a51eab3d-c23e-4cf0-81a8-4d958069d73f | Test Label for debugging 003 | This label is used for debugging purposes | c255293f-2461-4013-a2a5-ee8827927db5 | 16c20d76-53dc-460f-918a-1d8fedf0e4bb | 1 |
| c255293f-2461-4013-a2a5-ee8827927db5 | 1 | Test Document for debugging | This document is used for debugging purposes | https://test.document.com | 2021-07-01 00:00:00 | 2021-07-01 00:00:00 | a51eab3d-c23e-4cf0-81a8-4d958069d73f | Test Label for debugging 003 | This label is used for debugging purposes | c255293f-2461-4013-a2a5-ee8827927db5 | 925b6d87-3113-42f7-924a-6da0b8495b44 | 2 |
When I debug the map function I see that it gets called for each row. The doc, label, and collaborator objects have their properties mapped properly. However, even though when the function returns the doc has been updated when the map function gets called again the doc object has none of the updates from previous calls.
So in the end I worked it out. I did the following based on https://www.learndapper.com/relationships
var documents = await connection.QueryAsync<DocumentDto, LabelDto, CollaboratorDto, DocumentDto>(
GetDocumentSql,
(doc, label, collaborator) => {
if (label is not null && label.LabelId != Guid.Empty) // empty guid means no label
{
doc.Labels.Add(label);
}
if (collaborator.ProfileId != Guid.Empty) // empty guid means no collaborator
{
doc.Collaborators.Add(collaborator);
}
return doc;
},
new
{
Id = documentId.ToString()
},
splitOn: "LabelId,DocumentId");
var result = documents
.GroupBy(d => d.Id)
.Select(g =>
{
var groupedDocument = g.First();
groupedDocument.Labels = g.Select(d => d.Labels.Single())
.GroupBy(l => l.LabelId)
.Select(l=> l.First())
.ToList();
groupedDocument.Collaborators = g.Select(d => d.Collaborators.Single())
.GroupBy(c => c.ProfileId)
.Select(c => c.First())
.ToList();
return groupedDocument;
});
I am not in love with the catalogue of LINQ, but it does exactly what I need.