public void SelectFromTableWithAlias()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <DeliveryNote>("T100");
var gen = b.GetSqlText();
var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T100]";
Assert.Equal(spec, gen);
}
public void SelectColumnsWithSubString()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <DeliveryNote>();
b.Select <DeliveryNote>(i => i.Number.Substring(0, 3));
var gen = b.GetSqlText();
var spec = "SELECT SUBSTRING([T0].[Number],(@SqlParam0) + 1,@SqlParam1) FROM [WH].[DeliveryNote] AS [T0]";
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 SelectColumnsFromTableWithAlias()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <DeliveryNote>("T100");
b.Select <DeliveryNote>(i => i.Number, i => i.Date);
var gen = b.GetSqlText();
var spec = "SELECT [T100].[Number], [T100].[Date] FROM [WH].[DeliveryNote] AS [T100]";
Assert.Equal(spec, gen);
}
public void SelectWithWhereWithEndsWith()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <Supplier>();
b.Where <Supplier>(i => i.Name.EndsWith("Javier"));
var gen = b.GetSqlText();
var spec = "SELECT * FROM [WH].[Supplier] AS [T0] WHERE [T0].[Name] LIKE '%' + @SqlParam0";
Assert.Equal(spec, gen);
Assert.Equal(b.Parameters["SqlParam0"], "Javier");
}
public void SelectWithOrderBy()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <DeliveryNote>();
b.OrderBy <DeliveryNote>(OrderByDirection.Desc, i => i.Date);
b.OrderBy <DeliveryNote>(i => i.Year, i => i.SupplierId);
var gen = b.GetSqlText();
var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] ORDER BY [T0].[Date] DESC, [T0].[Year] ASC, [T0].[SupplierId] ASC";
Assert.Equal(spec, gen);
}
public void CanSetModelingByBuilder()
{
Modeling.ResetModeling();
Modeling.DefaultSchema = "KKK";
Modeling.Model <Supplier>().SetSchema("CUS").SetTable("CUSTOMER");
var b = new SelectBuilder();
b.Modeling.Model <Supplier>().SetSchema("dbo").SetTable("MyTable");
b.From <Supplier>();
var gen = b.GetSqlText();
var spec = "SELECT * FROM [dbo].[MyTable] AS [T0]";
Assert.Equal(spec, gen);
}
public void SelectWithSubQuery()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
var f = false;
var subQuery = new SelectBuilder().From <Supplier>().Where <Supplier>(c => c.IsDeleted == f).Select <Supplier>(c => c.Id);
b.From <DeliveryNote>();
b.Where <DeliveryNote>(i => subQuery.Contains(i.SupplierId) && i.Number == "hola");
var gen = b.GetSqlText();
var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] WHERE ([T0].[SupplierId] IN (SELECT [T0].[Id] FROM [WH].[Supplier] AS [T0] WHERE ([T0].[IsDeleted] = @SqlParam0)) AND ([T0].[Number] = @SqlParam1))";
Assert.Equal(spec, gen);
}
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 SelectWithWhere()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <DeliveryNote>();
b.Where <DeliveryNote>(i => i.Number == null && i.Year == "B" && i.Id > 2 && i.TotalAmount == 0);
var gen = b.GetSqlText();
var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] WHERE (((([T0].[Number] IS NULL) AND ([T0].[Year] = @SqlParam0)) AND ([T0].[Id] > @SqlParam1)) AND ([T0].[TotalAmount] = @SqlParam2))";
Assert.Equal(spec, gen);
Assert.Equal(b.Parameters["SqlParam0"], "B");
Assert.Equal(b.Parameters["SqlParam1"], Convert.ToInt64(2));
Assert.Equal(b.Parameters["SqlParam2"], (Decimal)0);
}
public void SelectWithWhereWithLikeAndSubString()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <Supplier>();
b.Where <Supplier>(i => i.Name.Substring(0).Contains("Javier"));
var gen = b.GetSqlText();
var spec = "SELECT * FROM [WH].[Supplier] AS [T0] WHERE SUBSTRING([T0].[Name],(@SqlParam0) + 1,@SqlParam1) LIKE '%' + @SqlParam2 + '%'";
Assert.Equal(spec, gen);
Assert.Equal(b.Parameters["SqlParam0"], 0);
Assert.Equal(b.Parameters["SqlParam1"], int.MaxValue);
Assert.Equal(b.Parameters["SqlParam2"], "Javier");
}
public void SelectWithWhereWithInvertedLike()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <Supplier>();
var name = "Javier Ros Moreno";
b.Where <Supplier>(i => name.Contains(i.Name));
var gen = b.GetSqlText();
var spec = "SELECT * FROM [WH].[Supplier] AS [T0] WHERE @SqlParam0 LIKE '%' + [T0].[Name] + '%'";
Assert.Equal(spec, gen);
Assert.Equal(b.Parameters["SqlParam0"], "Javier Ros Moreno");
}
public void SelectWithWhereWithNullScopeConstants()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <DeliveryNote>("T0");
string year = null;
long? id = null;
decimal?total = null;
string number = null;
b.Where <DeliveryNote>(i => i.Number == number && i.Year == year && i.Id == id && i.TotalAmount == total);
var gen = b.GetSqlText();
var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] WHERE (((([T0].[Number] IS NULL) AND ([T0].[Year] IS NULL)) AND ([T0].[Id] IS NULL)) AND ([T0].[TotalAmount] IS NULL))";
Assert.Equal(spec, gen);
Assert.Equal(b.Parameters.Count, 0);
}
public void SelectGroupByHaving()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <DeliveryNote>();
b.Select <DeliveryNote>(dn => dn.SupplierId);
b.Select <DeliveryNote>(dn => DbFunctions.Sum(dn.TotalAmount));
b.Select <DeliveryNote>(dn => DbFunctions.Count(dn.TotalAmount));
b.Select <DeliveryNote>(dn => DbFunctions.Min(dn.TotalAmount));
b.Select <DeliveryNote>(dn => DbFunctions.Max(dn.TotalAmount));
b.GroupBy <DeliveryNote>(dn => dn.SupplierId);
b.Having <DeliveryNote>(dn => DbFunctions.Sum(dn.TotalAmount) > 10);
var gen = b.GetSqlText();
var spec = "SELECT [T0].[SupplierId], SUM([T0].[TotalAmount]), COUNT([T0].[TotalAmount]), MIN([T0].[TotalAmount]), MAX([T0].[TotalAmount]) FROM [WH].[DeliveryNote] AS [T0] GROUP BY [T0].[SupplierId] HAVING (SUM([T0].[TotalAmount]) > @SqlParam0)";
Assert.Equal(spec, gen);
Assert.Equal(b.Parameters["SqlParam0"], (decimal)10);
}
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);
}
public void SelectWithWhereWithScopeConstants()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
b.From <DeliveryNote>();
var year = "B";
var id = 2;
var total = 0;
string number = "23";
b.Where <DeliveryNote>(i => i.Number == number && i.Year == year && i.Id > id && i.TotalAmount == total);
var gen = b.GetSqlText();
var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] WHERE (((([T0].[Number] = @SqlParam0) AND ([T0].[Year] = @SqlParam1)) AND ([T0].[Id] > @SqlParam2)) AND ([T0].[TotalAmount] = @SqlParam3))";
Assert.Equal(spec, gen);
Assert.Equal(b.Parameters["SqlParam0"], "23");
Assert.Equal(b.Parameters["SqlParam1"], "B");
Assert.Equal(b.Parameters["SqlParam2"], Convert.ToInt32(2));
Assert.Equal(b.Parameters["SqlParam3"], (Int32)0);
}
public void SelectWithMultipleSubQueryAndParameters()
{
Configuration.SetDialect(new TestDialect());
SetModeling();
var b = new SelectBuilder();
var const1 = false;
var const2 = false;
var subQuery1 = new SelectBuilder().From <Supplier>().Where <Supplier>(c => c.IsDeleted == const1).Select <Supplier>(c => c.Id);
var subQuery2 = new SelectBuilder().From <Supplier>().Where <Supplier>(c => c.IsDeleted == const2).Where <Supplier>(c => subQuery1.Contains(c.Id)).Select <Supplier>(c => c.Id);
b.From <DeliveryNote>();
b.Where <DeliveryNote>(i => subQuery1.Contains(i.SupplierId) && i.Number == "hola1");
b.Where <DeliveryNote>(i => subQuery2.Contains(i.SupplierId) && i.Number == "hola2");
var gen = b.GetSqlText();
var spec = "SELECT * FROM [WH].[DeliveryNote] AS [T0] WHERE ([T0].[SupplierId] IN (SELECT [T0].[Id] FROM [WH].[Supplier] AS [T0] WHERE ([T0].[IsDeleted] = @SqlParam0)) AND ([T0].[Number] = @SqlParam1)) AND ([T0].[SupplierId] IN (SELECT [T0].[Id] FROM [WH].[Supplier] AS [T0] WHERE ([T0].[IsDeleted] = @SqlParam2) AND [T0].[Id] IN (SELECT [T0].[Id] FROM [WH].[Supplier] AS [T0] WHERE ([T0].[IsDeleted] = @SqlParam3))) AND ([T0].[Number] = @SqlParam4))";
Assert.Equal(spec, gen);
Assert.Equal(b.Parameters["SqlParam0"], const1);
Assert.Equal(b.Parameters["SqlParam1"], "hola1");
Assert.Equal(b.Parameters["SqlParam2"], const2);
Assert.Equal(b.Parameters["SqlParam3"], const1);
Assert.Equal(b.Parameters["SqlParam4"], "hola2");
}
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");
}