Dapper Multi Mapping with QueryMultiple
Here's a version of the solution I used. I side-stepped the issue Ronnie raised in his answer using an inheritance hierachy instead of setting a property to null, but it amounts to much the same thing.
Here's the SQL: users have items and collections, and items can be in collections.
CREATE TABLE Users
(id UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
name NVARCHAR (MAX) NULL,
email NVARCHAR (128) NULL,
PRIMARY KEY (id))
CREATE TABLE Items
(id UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
userId UNIQUEIDENTIFIER NOT NULL,
name NVARCHAR (MAX) NULL,
description NVARCHAR (MAX) NULL,
PRIMARY KEY (id),
FOREIGN KEY (userId) REFERENCES Users (id))
CREATE TABLE Collections
(id UNIQUEIDENTIFIER DEFAULT (NEWID()) NOT NULL,
userId UNIQUEIDENTIFIER NOT NULL,
name NVARCHAR (MAX) NULL,
layoutSettings NVARCHAR (MAX) NULL,
PRIMARY KEY (id),
FOREIGN KEY (userId) REFERENCES Users (id))
CREATE TABLE CollectedItems
(itemId UNIQUEIDENTIFIER NOT NULL,
collectionId UNIQUEIDENTIFIER NOT NULL,
PRIMARY KEY CLUSTERED (itemId, collectionId),
FOREIGN KEY (itemId) REFERENCES Items (id),
FOREIGN KEY (collectionId) REFERENCES Collections (id))
Now the data model classes. Collections are a little more complicated than I would expect in order to deal with Dapper multi mapping with multiple queries.
public class User
{
public Guid Id { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public List<Item> Items { get; set; }
public List<Collection> Collections { get; set; }
}
public class Item
{
public Guid Id { get; set; }
public Guid UserId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
}
public class CoreCollection
{
public Guid Id { get; set; }
public Guid UserId { get; set; }
public string Name { get; set; }
public string LayoutSettings { get; set; }
}
public class PartialDataCollection : CoreCollection
{
public Guid ItemId { get; set; }
}
public class Collection : CoreCollection
{
public List<Guid> ItemIds { get; set; }
}
public class CollectedItem
{
public Guid ItemId { get; set; }
public Guid CollectionId { get; set; }
public DateTime CreatedAt { get; set; }
}
Finally we have the controller method which uses Dapper multi mapping with multiple queries
[Route("GetUser/{id}")]
public User GetUser(Guid id)
{
var sql = @"SELECT * FROM Users WHERE id = @id
SELECT * FROM Items WHERE userId = @id
SELECT * FROM Collections
LEFT OUTER JOIN CollectedItems ON Collections.id = CollectedItems.collectionId
WHERE userId = @id";
using (var connection = new SqlConnection(ConnectionString))
{
var multi = connection.QueryMultiple(sql, new { id = id });
var user = multi.Read<User>().Single();
var items = multi.Read<Item>().ToList();
var partialDataCollections = multi.Read<PartialDataCollection, CollectedItem, PartialDataCollection>(AddCollectedItem, splitOn: "itemId").ToList();
user.Items = items;
user.Collections = partialDataCollections.GroupBy(
pdc => pdc.Id,
(key, group) => new Collection
{
Id = key,
UserId = group.First().UserId,
Name = group.First().Name,
LayoutSettings = group.First().LayoutSettings,
ItemIds = group.Select(groupMember => groupMember.ItemId).ToList()
}).ToList();
return user;
}
}
private PartialDataCollection AddCollectedItem(PartialDataCollection collection, CollectedItem collectedItem)
{
if (collection != null && collectedItem != null)
{
collection.ItemId = collectedItem.ItemId;
}
return collection;
}
Where Ronnie is anxious about setting person.Check = null
in his answer I am anxious about the additional complexity in my answer from adding the class PartialDataCollection
to my model. But I cannot see a simple way around that.
(N.B. I have raised this as an issue on the Dapper GitHub project.)
Here's how I got it working:
var q = _sqlConnection.QueryMultiple("MySproc",
myParams,
commandType: CommandType.StoredProcedure);
var set1 = q.Read<Set1Type>();
var set2Func = new Func<Person, Check, Person>((p, c) => {
p.CheckAlert = c;
return p;
});
var set2 = q.Read(set2Func, "CheckId")
.GroupBy(x => x.PersonId)
.Select(x => {
var person = x.First();
person.Checks = x.Select(p => p.Check).ToArray();
person.Check = null; // i really don't like this
return person;
})
.ToArray();
As the comment says, I don't like the unneeded check property on the Person object.
I'd still love to hear of a better way of doing this.