public async Task Exec(IScenarioContext context)
{
var data = await SqModelSelectBuilder
.Select(Customer.GetReader())
.InnerJoin(
OrderDateCreated.GetReader(),
on: t => t.Table.CustomerId == t.JoinedTable1.CustomerId)
.LeftJoin(
UserEmail.GetReader(),
on: t => t.Table.UserId == t.JoinedTable2.UserId)
.LeftJoin(
CompanyName.GetReader(),
on: t => t.Table.CompanyId == t.JoinedTable3.CompanyId)
.Find(0,
10,
null,
t => SqQueryBuilder.Desc(SqQueryBuilder.IsNull(t.JoinedTable2.FirstName, t.JoinedTable3.CompanyName)).ThenBy(t.JoinedTable1.DateCreated),
d =>
new
{
Client = d.JoinedModel2?.Email ?? d.JoinedModel3?.Name ?? "Unknown",
Date = d.JoinedModel1.DateCreated.ToString("s")
})
.QueryPage(context.Database);
if (data.Total != 8397 && data.Total != 8438 /*MySQL*/)
{
throw new Exception($"8397 is expected but was {data.Total}");
}
}
private static async Task Step17ModelsSelectBuilder(ISqDatabase database)
{
var page = await SqModelSelectBuilder
.Select(ModelEmptyReader.Get <TableCustomer>())
.LeftJoin(UserName.GetReader(), on: t => t.Table.UserId == t.JoinedTable1.UserId)
.LeftJoin(CompanyName.GetReader(), on: t => t.Table.CompanyId == t.JoinedTable2.CompanyId)
.Find(0,
10,
filter: null,
order: t => Asc(IsNull(t.JoinedTable1.FirstName + t.JoinedTable1.LastName,
t.JoinedTable2.CompanyName)),
r => (r.JoinedModel1 != null ? r.JoinedModel1.FirstName + " " + r.JoinedModel1.LastName : null) ??
r.JoinedModel2?.Name ?? "Unknown")
.QueryPage(database);
foreach (var name in page.Items)
{
Console.WriteLine(name);
}
}
