public void SelectJoinTablesWithTripleType()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <DeliveryNote>();
b.Join <DeliveryNote, DeliveryNoteDetail>((i, id) => i.Id == id.DeliveryNoteId);
b.Join <DeliveryNote, DeliveryNoteDetail, User>(
(dn, dnd, u) => dn.CreatedBy == u.Id && dnd.CreatedBy == u.Id);
var gen = b.GetSqlText();
var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] INNER JOIN [WH].[DeliveryNoteDetail] AS [T1] ON ([T0].[Id] = [T1].[DeliveryNote_Id]) INNER JOIN [LoB].[SecurityProfile] AS [T2] ON (([T0].[CreatedBy] = [T2].[Id]) AND ([T1].[CreatedBy] = [T2].[Id]))";
Assert.Equal(spec, gen);
}
public void SelectSelfTableJoin()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <User>("T11");
b.Join <User, User>("T22", (c1, c2) => c1.CreatedBy == c2.Id);
var gen = b.GetSqlText();
var spec = "SELECT * FROM [LoB].[SecurityProfile] AS [T11] INNER JOIN [LoB].[SecurityProfile] AS [T22] ON ([T11].[CreatedBy_Id] = [T22].[Id])";
Assert.Equal(spec, gen);
}
public void SelectJoinTwoTablesWithAliases()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <DeliveryNote>("T11");
b.Join <DeliveryNote, DeliveryNoteDetail>("T22", (i, id) => i.Id == id.DeliveryNoteId || i.TotalAmount >= id.UnitPrice);
var gen = b.GetSqlText();
var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T11] INNER JOIN [WH].[DeliveryNoteDetail] AS [T22] ON (([T11].[Id] = [T22].[DeliveryNote_Id]) OR ([T11].[TotalAmount] >= [T22].[UnitPrice]))";
Assert.Equal(spec, gen);
}
public void SelectLeftJoinTwoTables()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <DeliveryNote>();
b.Join <DeliveryNote, DeliveryNoteDetail>(JoinType.Left, (i, id) => i.Id == id.DeliveryNoteId);
var gen = b.GetSqlText();
var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] LEFT JOIN [WH].[DeliveryNoteDetail] AS [T1] ON ([T0].[Id] = [T1].[DeliveryNote_Id])";
Assert.Equal(spec, gen);
}
public void SelectColumnsJoinsAndWhere()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <DeliveryNote>();
b.Join <DeliveryNote, DeliveryNoteDetail>((i, id) => i.Id == id.DeliveryNoteId);
b.Join <DeliveryNote, Supplier>((i, c) => i.SupplierId == c.Id);
b.Select <DeliveryNote, Supplier>((i, c) => i.Number, (i, c) => c.Name, (i, c) => i.Date, (i, c) => i.TotalAmount);
b.Select <DeliveryNoteDetail>((id) => id.Size);
b.Where <Supplier>(c => c.IsDeleted);
b.Where <DeliveryNote, DeliveryNoteDetail>((i, id) => i.Year == "B" && id.IsDeleted == false);
b.Where <DeliveryNote, DeliveryNoteDetail>(WhereOperator.Or, (i, id) => i.Year == "Z");
var gen = b.GetSqlText();
var spec = "SELECT [T0].[Number], [T2].[Name], [T0].[Date], [T0].[TotalAmount], [T1].[Size] FROM [WH].[DeliveryNote] AS [T0] INNER JOIN [WH].[DeliveryNoteDetail] AS [T1] ON ([T0].[Id] = [T1].[DeliveryNote_Id]) INNER JOIN [WH].[Supplier] AS [T2] ON ([T0].[SupplierId] = [T2].[Id]) WHERE ([T2].[IsDeleted] = @SqlParam0) AND (([T0].[Year] = @SqlParam1) AND ([T1].[IsDeleted] = @SqlParam2)) OR ([T0].[Year] = @SqlParam3)";
Assert.Equal(spec, gen);
Assert.Equal(b.Parameters["SqlParam0"], true);
Assert.Equal(b.Parameters["SqlParam1"], "B");
Assert.Equal(b.Parameters["SqlParam2"], false);
Assert.Equal(b.Parameters["SqlParam3"], "Z");
}
public void SelectProjection()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <DeliveryNote>();
b.Join <DeliveryNote, Supplier>((dn, s) => dn.SupplierId == s.Id);
b.Select <DeliveryNote, Supplier>((dn, s) => new DeliveryNoteDto
{
Number = dn.Number,
TotalAmount = dn.TotalAmount,
Date = dn.Date,
SupplierName = s.Name
});
var gen = b.GetSqlText();
var spec = "SELECT [T0].[Number] AS Number, [T0].[TotalAmount] AS TotalAmount, [T0].[Date] AS Date, [T1].[Name] AS SupplierName FROM [WH].[DeliveryNote] AS [T0] INNER JOIN [WH].[Supplier] AS [T1] ON ([T0].[SupplierId] = [T1].[Id])";
Assert.Equal(spec, gen);
}