public void Update_ImplicitSets_EqualsExpected()
{
var filter = SqlFilter.Construct(new
{
Id = Guid.Parse("b07d1437-597c-47f5-8d13-453a85f0b681"),
Auto = Guid.Parse("b07d1437-597c-47f5-8d13-453a85f0b681"),
DoNotChange = (DateTime?)null
});
var table = new SqlTable <Class1>();
var sqlBuilder = SqlBuilder.Update(table)
.Values(new Dictionary <SqlColumn, IOperable>
{
{ table[t => t.Auto], filter[t => t.Auto].Coalesce(table[f => f.Auto]) },
{ table[t => t.DoNotChange], filter[t => t.DoNotChange].Coalesce(table[f => f.DoNotChange]) }
})
.Where(table[t => t.Id].EqualsOne(filter[f => f.Id]));
var sqlOptions = new SqlOptions {
Dialect = SqlDialect.Postgres95
};
var actual = sqlBuilder.BuildSql(sqlOptions);
var expected = @"
UPDATE foo.class1
SET
auto = COALESCE(@Auto, class1.auto),
do_not_change = COALESCE(@DoNotChange, class1.do_not_change)
WHERE
class1.id = @Id
";
Check(expected, actual);
}
public void Select_EqualsExpected()
{
var table = new SqlTable <Class1>();
var order = new
{
Id = new OrderItem {
Direction = OrderDirection.Asc, Index = 2
},
Value1 = new OrderItem {
Direction = OrderDirection.Desc, Index = 1
},
DoNotChange = (OrderItem)null
};
var orderMap = OrderMap.FromOrder(table, order);
var sqlFilter = SqlFilter.Construct(
new { Ids = new[] { 1, 2 }, Value1 = "foo", Auto = (int?)null }
);
var whereSql = new[]
{
Conditions.Or(sqlFilter[f => f.Ids].IsNull(), table[m => m.Id].EqualsAny(sqlFilter[f => f.Ids])),
Conditions.Or(sqlFilter[f => f.Value1].IsNull(),
table[m => m.Value1].EqualsOne(sqlFilter[f => f.Value1]))
};
var sqlOptions = new SqlOptions {
Dialect = SqlDialect.Postgres95
};
var sqlBuilder = SqlBuilder.Select
.Values(table)
.From(table)
.Where(whereSql)
.Order(orderMap)
.Offset(5)
.LimitBy(10);
var actual = sqlBuilder.BuildSql(sqlOptions);
var expected = @"
SELECT
class1.id AS Id,
class1.auto AS Auto,
class1.value1 AS Value1,
class1.value2 AS Value2,
class1.do_not_change AS DoNotChange
FROM foo.class1
WHERE
(@Ids IS NULL OR class1.id = ANY(@Ids))
AND (@Value1 IS NULL OR class1.value1 = @Value1)
ORDER BY
class1.value1 DESC,
class1.id ASC
OFFSET 5
LIMIT 10
";
Check(expected, actual);
Assert.AreEqual("Id", sqlBuilder.SplitOn);
}
public void SelectMultiple_Generic_EqualsExpected()
{
var sqlFilter = SqlFilter.Construct(new
{
Ids = new[] { 1, 2 },
Value1 = "foo",
Auto = (int?)null,
Key = Guid.Empty,
DbGeneratedKey = 2
});
var sqlBuilder = SqlBuilder <Class1, Class2> .Select()
.CustomSql("/* SELECT */")
.From((table1, table2) => table1)
.CustomSql("/* FROM */")
.InnerJoin((table1, table2) => table2,
(table1, table2) => table2[m => m.Key].EqualsOne(table1[m => m.Id]))
.CustomSql("/* JOIN */")
.Where((table1, table2) => Conditions.And(
Conditions.Or(sqlFilter[f => f.Ids].IsNull(), table1[m => m.Id].EqualsAny(sqlFilter[f => f.Ids])),
Conditions.Or(sqlFilter[f => f.Value1].IsNull(),
table1[m => m.Value1].EqualsOne(sqlFilter[f => f.Value1])),
Conditions.Or(sqlFilter[f => f.Key].IsNull(), table2[m => m.Key].EqualsOne(sqlFilter[f => f.Key])),
sqlFilter[f => f.DbGeneratedKey]
.IsNull()
.Or(table2[m => m.DbGeneratedKey].EqualsOne(sqlFilter[f => f.DbGeneratedKey])),
table2[m => m.Key].ILike(sqlFilter[f => f.Key]), table2[m => m.Key].Like(sqlFilter[f => f.Key]),
table2[m => m.Key].Lower().Like(sqlFilter[f => f.Key].Lower())))
.CustomSql("/* WHERE */")
.OrderBy((table1, table2) => table1[m => m.Id], OrderDirection.Asc)
.CustomSql("/* ORDER */")
.Offset(5)
.CustomSql("/* OFFSET */")
.LimitBy(10)
.CustomSql("/* LIMIT */")
.CustomSql("/* START */", SqlSelectPosition.Start);
var sqlOptions = new SqlOptions {
Dialect = SqlDialect.Postgres95
};
var actual = sqlBuilder.BuildSql(sqlOptions);
var expected = @"
/* START */
SELECT
class1.id AS Id,
class1.auto AS Auto,
class1.value1 AS Value1,
class1.value2 AS Value2,
class1.do_not_change AS DoNotChange,
class2.key AS Key,
class2.db_generated_key AS DbGeneratedKey,
class2.db_generated AS DbGenerated,
class2.value1 AS Value1,
class2.value2 AS Value2,
class2.code_generated AS CodeGenerated,
class2.do_not_change AS DoNotChange
/* SELECT */
FROM foo.class1
/* FROM */
INNER JOIN foo.class2 ON class2.key = class1.id
/* JOIN */
WHERE
(@Ids IS NULL OR class1.id = ANY(@Ids))
AND (@Value1 IS NULL OR class1.value1 = @Value1)
AND (@Key IS NULL OR class2.key = @Key)
AND (@DbGeneratedKey IS NULL OR class2.db_generated_key = @DbGeneratedKey)
AND class2.key ILIKE @Key
AND class2.key LIKE @Key
AND LOWER(class2.key) LIKE LOWER(@Key)
/* WHERE */
ORDER BY
class1.id ASC
/* ORDER */
OFFSET 5
/* OFFSET */
LIMIT 10
/* LIMIT */
";
Check(expected, actual);
Assert.AreEqual("Id,Key", sqlBuilder.SplitOn);
}