public void TestOneToManyOnMany()
{
// Fetching a POCO that has a list of other POCOs,
// and fetching these POCOs at the same time,
// with a pk/fk relationship
// for several POCOs.
//
// The ORDER BY clause (matching x => x.Id) is required
// for proper aggregation to take place.
using (IScope scope = ScopeProvider.CreateScope())
{
// This is the raw SQL, but it's better to use expressions and no magic strings!
// var sql = @"
// SELECT zbThing1.id AS Id, zbThing1.name AS Name,
// zbThing2.id AS Things__Id, zbThing2.name AS Things__Name, zbThing2.thingId AS Things__ThingId
// FROM zbThing1
// JOIN zbThing2 ON zbThing1.id=zbThing2.thingId
// ORDER BY zbThing1.id";
Sql <ISqlContext> sql = ScopeAccessor.AmbientScope.SqlContext.Sql()
.Select <Thing3Dto>(r => r.Select(x => x.Things)) // select Thing3Dto, and Thing2Dto for Things
.From <Thing3Dto>()
.InnerJoin <Thing2Dto>().On <Thing3Dto, Thing2Dto>(left => left.Id, right => right.ThingId)
.OrderBy <Thing3Dto>(x => x.Id);
List <Thing3Dto> dtos = ScopeAccessor.AmbientScope.Database.FetchOneToMany <Thing3Dto>(x => x.Things, /*x => x.Id,*/ sql);
Assert.AreEqual(2, dtos.Count);
Thing3Dto dto1 = dtos.FirstOrDefault(x => x.Id == 1);
Assert.IsNotNull(dto1);
Assert.AreEqual("one", dto1.Name);
Assert.IsNotNull(dto1.Things);
Assert.AreEqual(2, dto1.Things.Count);
Thing2Dto dto2 = dto1.Things.FirstOrDefault(x => x.Id == 1);
Assert.IsNotNull(dto2);
Assert.AreEqual("uno", dto2.Name);
}
}