Trying to figure this out, but I can't get it to work. This query:
select MultiCollections.*, Collections.* from MultiCollections
left join MultiCollectionCollections on MultiCollections.Id = MultiCollectionCollections.MultiCollectionId
left join Collections on MultiCollectionCollections.CollectionId = Collections.Id
where MultiCollections.UserId=5
This will return this data:
As you can see, row 1 and 2 are from the same Title. The data behind them are books.
Row 3 and 4 are also collections but don't have books.
I have two objects in my code: MultiCollection Collection
Both correspond with the data given in the result of the query: Id, UserId and Title are for object MultiCollection. Other data are for the object Collection.
I expect to see three MultiCollections in my C# code: Action Drama Fiction
Action will have 2 collections. Drama and Fiction should be empty.
Instead, I get 4 MultiCollections and none of them contains Collections. My C# code:
public IEnumerable<MultiCollection> GetAll(int userId)
{
string query = @"select MC.*, C.* from MultiCollections MC
left join MultiCollectionCollections MCC on MC.Id = MCC.MultiCollectionId
left join Collections C on MCC.CollectionId = C.Id
where UserId=" + userId;
using (DbConnection connection = ConnectionFactory())
{
connection.Open();
return connection.Query<MultiCollection, List<Collection>, MultiCollection>(query,
(a, s) =>
{
a.Collections = s;
return a;
});
}
}
When running the code I would expect this:
Action
Collections
-> Book 1
-> Book 2
Drama
Collections
Null
Fiction
Collections
Null
I have no idea what I'm doing wrong.
Your c# code should of look like this:
public IEnumerable<MultiCollection> GetAll(int userId)
{
string query = @"select MC.*, C.* from MultiCollections MC
left join MultiCollectionCollections MCC on MC.Id = MCC.MultiCollectionId
left join Collections C on MCC.CollectionId = C.Id
where UserId=" + userId;
using (DbConnection connection = ConnectionFactory())
{
connection.Open();
return connection.Query<MultiCollection, Collection, MultiCollection>(query,
(a, s) =>
{
a.Collections = new List<Collection>();
a.Collections.Add(s);
return a;
}, splitOn: "MultiCollectionId,CollectionId"););
}
}
Notice, that .Query<MultiCollection, Collection, MultiCollection>
is a Collection
not List<Collection>
and it is doing .add()
and not setter.