public void InValuesTest()
{
var a = CustomColumnFactory.Int32("a");
Assert.AreEqual("[a] IN(1)", a.In(Literal(1)).ToSql());
Assert.AreEqual("[a] IN(1,2,3)", a.In(Literal(1), Literal(2), Literal(3)).ToSql());
Assert.AreEqual("[a] IN(1,2)", a.In(new [] { Literal(1), Literal(2) }).ToSql());
Assert.AreEqual("[a] IN(1)", a.In(1).ToSql());
Assert.AreEqual("[a] IN(1,2,3)", a.In(1, 2, 3).ToSql());
Assert.AreEqual("[a] IN(1,2)", a.In(new [] { 1, 2 }).ToSql());
Assert.AreEqual("[a] IN('1')", a.In("1").ToSql());
Assert.AreEqual("[a] IN('1','2','3')", a.In("1", "2", "3").ToSql());
Assert.AreEqual("[a] IN('1','2')", a.In(new [] { "1", "2" }).ToSql());
var g1 = Guid.Parse("F46E2EC5-E08F-4CB9-8FD5-62DAC1A90C85");
var g2 = Guid.Parse("FE716966-74D9-4449-83CE-16371698E8D0");
var g3 = Guid.Parse("9614F808-E9EA-4BFE-8432-3711EB7E235C");
Assert.AreEqual("[a] IN('f46e2ec5-e08f-4cb9-8fd5-62dac1a90c85')", a.In(g1).ToSql());
Assert.AreEqual("[a] IN('f46e2ec5-e08f-4cb9-8fd5-62dac1a90c85','fe716966-74d9-4449-83ce-16371698e8d0','9614f808-e9ea-4bfe-8432-3711eb7e235c')", a.In(g1, g2, g3).ToSql());
Assert.AreEqual("[a] IN('f46e2ec5-e08f-4cb9-8fd5-62dac1a90c85','fe716966-74d9-4449-83ce-16371698e8d0')", a.In(new [] { g1, g2 }).ToSql());
}
private static async Task Step11SubQueries(ISqDatabase database)
{
var num = CustomColumnFactory.Int32("3");
//Note: "3" (the first value) is for compatibility with MySql
//which does not properly support values constructors
var sum = CustomColumnFactory.Int32("Sum");
var numbers = Values(3, 1, 1, 7, 3, 7, 3, 7, 7, 8).AsColumns(num);
var numbersSubQuery = TableAlias();
var mostFrequentNum = (int) await
SelectTop(1, numbersSubQuery.Column(num))
.From(
Select(numbers.Column(num), CountOne().As(sum))
.From(numbers)
.GroupBy(numbers.Column(num))
.As(numbersSubQuery)
)
.OrderBy(Desc(numbersSubQuery.Column(sum)))
.QueryScalar(database);
Console.WriteLine("The most frequent number: " + mostFrequentNum);
}
public void TopTest()
{
var actual = SelectDistinct(Literal(2)).Done().ToSql();
Assert.AreEqual("SELECT DISTINCT 2", actual);
actual = SelectTopDistinct(Literal(3), Literal(2)).Done().ToSql();
Assert.AreEqual("SELECT DISTINCT TOP 3 2", actual);
actual = SelectTopDistinct(4, Literal(2)).Done().ToSql();
Assert.AreEqual("SELECT DISTINCT TOP 4 2", actual);
actual = SelectTop(5, Literal(2)).Done().ToSql();
Assert.AreEqual("SELECT TOP 5 2", actual);
actual = SelectTop(Literal(6), Literal(2)).Done().ToSql();
Assert.AreEqual("SELECT TOP 6 2", actual);
actual = SelectDistinct(2).Done().ToSql();
Assert.AreEqual("SELECT DISTINCT 2", actual);
actual = SelectTopDistinct(Literal(3), 2).Done().ToSql();
Assert.AreEqual("SELECT DISTINCT TOP 3 2", actual);
actual = SelectTopDistinct(4, 2).Done().ToSql();
Assert.AreEqual("SELECT DISTINCT TOP 4 2", actual);
actual = SelectTop(5, 2).Done().ToSql();
Assert.AreEqual("SELECT TOP 5 2", actual);
actual = SelectTop(Literal(6), 2, "Hi").Done().ToSql();
Assert.AreEqual("SELECT TOP 6 2,'Hi'", actual);
actual = SelectTop(Literal(9) % 7, 2, AllColumns()).Done().ToSql();
Assert.AreEqual("SELECT TOP 9%7 2,*", actual);
actual = SelectDistinct(2, AllColumns()).Done().ToSql();
Assert.AreEqual("SELECT DISTINCT 2,*", actual);
actual = SelectTopDistinct(Literal(3), 2, AllColumns()).Done().ToSql();
Assert.AreEqual("SELECT DISTINCT TOP 3 2,*", actual);
actual = SelectTopDistinct(4, 2, AllColumns()).Done().ToSql();
Assert.AreEqual("SELECT DISTINCT TOP 4 2,*", actual);
var columns = new [] { CustomColumnFactory.Int32("a"), CustomColumnFactory.Int32("b") };
var columns2 = new [] { CustomColumnFactory.Int32("c"), CustomColumnFactory.Int32("d") };
var columnE = CustomColumnFactory.Int32("e");
var columnF = CustomColumnFactory.Int32("f");
actual = Select(columns).Done().ToSql();
Assert.AreEqual("SELECT [a],[b]", actual);
actual = SelectTop(2, columns).Done().ToPgSql();
Assert.AreEqual("SELECT \"a\",\"b\" LIMIT 2", actual);
actual = SelectTop(Literal(2), columns).Done().ToMySql();
Assert.AreEqual("SELECT `a`,`b` LIMIT 2", actual);
actual = SelectDistinct(columns.Concat(columns2)).Done().ToSql();
Assert.AreEqual("SELECT DISTINCT [a],[b],[c],[d]", actual);
actual = SelectTopDistinct(2, columns.Concat(columns2).Concat(columnE, columnF)).Done().ToSql();
Assert.AreEqual("SELECT DISTINCT TOP 2 [a],[b],[c],[d],[e],[f]", actual);
actual = SelectTopDistinct(Literal(2), columns.Concat(columns2).Concat(columnE)).Done().ToSql();
Assert.AreEqual("SELECT DISTINCT TOP 2 [a],[b],[c],[d],[e]", actual);
}
public void FullTest()
{
const int usersCount = 3;
var data = new List <UserData>(usersCount);
for (int i = 0; i < usersCount; i++)
{
data.Add(new UserData
{
UserId = i % 2 == 0 ? 0 : i,
FirstName = "First" + i,
LastName = "Last" + i,
EMail = $"user{i}@company.com",
RegDate = new DateTime(2020, 01, 02)
});
}
DateTime utcNow = new DateTime(2020, 10, 03, 10, 17, 12, 131);
var recordIndex = CustomColumnFactory.Int32("Index");
var inserted = CustomColumnFactory.Int32("InsertedUserId");
var deleted = CustomColumnFactory.Int32("DeletedUserId");
var action = CustomColumnFactory.String("Action");
var mergeOutput = SqQueryBuilder
.MergeDataInto(Tables.User(), data)
.MapDataKeys(s => s.Set(s.Target.UserId, s.Source.UserId))
.MapData(s => s
.Set(s.Target.FirstName, s.Source.FirstName)
.Set(s.Target.LastName, s.Source.LastName)
.Set(s.Target.Email, s.Source.EMail)
.Set(s.Target.RegDate, s.Source.RegDate))
.MapExtraData(s => s.Set(recordIndex, s.Index))
.AndOn((t, s) => t.UserId.WithSource(s) != 0)
.WhenMatchedThenUpdate()
.AlsoSet(s =>
s.Set(s.Target.Version, s.Target.Version + 1)
.Set(s.Target.Modified, utcNow))
.WhenNotMatchedByTargetThenInsert()
.ExcludeKeys()
.Exclude(t => new[] { t.Email.ColumnName, t.LastName.ColumnName })
.AlsoInsert(s => s
.Set(s.Target.LastName, "Fake")
.Set(s.Target.Created, utcNow)
.Set(s.Target.Modified, utcNow)
.Set(s.Target.Version, 1))
.WhenNotMatchedBySourceThenDelete()
.Output((t, s, m) => m
.Inserted(t.UserId.As(inserted))
.Inserted(t.UserId.As(deleted))
.Column(recordIndex.WithSource(s))
.Action(action))
.Done();
var actual = mergeOutput?.ToSql();
var expected = "MERGE [dbo].[user] [A0] USING (" +
"VALUES (0,'First0','Last0','*****@*****.**','2020-01-02',0)," +
"(1,'First1','Last1','*****@*****.**','2020-01-02',1)," +
"(0,'First2','Last2','*****@*****.**','2020-01-02',2)" +
")[A1]([UserId],[FirstName],[LastName],[Email],[RegDate],[Index]) " +
"ON [A0].[UserId]=[A1].[UserId] AND [A1].[UserId]!=0 " +
"WHEN MATCHED THEN UPDATE SET " +
"[A0].[FirstName]=[A1].[FirstName]," +
"[A0].[LastName]=[A1].[LastName]," +
"[A0].[Email]=[A1].[Email]," +
"[A0].[RegDate]=[A1].[RegDate]," +
"[A0].[Version]=[A0].[Version]+1," +
"[A0].[Modified]='2020-10-03T10:17:12.131' " +
"WHEN NOT MATCHED THEN INSERT" +
"([FirstName],[RegDate],[LastName],[Created],[Modified],[Version]) " +
"VALUES([A1].[FirstName],[A1].[RegDate],'Fake','2020-10-03T10:17:12.131','2020-10-03T10:17:12.131',1) " +
"WHEN NOT MATCHED BY SOURCE THEN DELETE " +
"OUTPUT INSERTED.[UserId] [InsertedUserId],INSERTED.[UserId] [DeletedUserId],[A1].[Index],$ACTION [Action];";
Assert.AreEqual(expected, actual);
}