public void WhereBool()
{
var q = Builders <MyModel> .Select(x => x.Bool)
.Where(x => x.Bool);
Utils.AssertRawQuery(q, @"SELECT bool FROM model WHERE bool");
}
public async Task TestInsertSubquery()
{
var dr = await CreateDriver();
var subq = Builders <MyModel> .Select(x => x.Name)
.Where(x => x.Id == 1)
.AsSubquery();
var obj = new MyModel2 {
};
await dr.Insert(obj)
.UseField(x => x.Name1, subq)
.ExecuteAsync();
var res = await dr.From <MyModel2>()
.Select(x => x.Name1)
.ToListAsync();
Assert.Collection(res,
x => Assert.Equal("subtest1", x),
x => Assert.Equal("subtest2", x),
x => Assert.Equal("subtest3", x),
x => Assert.Equal("subtest4", x),
x => Assert.Equal("test1", x));
}
public void SelectWithJoinNestedSubquery()
{
var subq1 = Builders <MyModel> .Select(x => x.Id)
.Where(x => x.Id == 1)
.AsSubquery();
var subq = BuildersJoin.FromMany <MyModel, MyModel2>((m, m2) => m.Id == m2.Id)
.Map((m, m2) => new {
M = m,
M2 = m2,
})
.Select(x => x.M.Name)
.Where(x => x.M2.Id == 1 && x.M.Id.PgIn(subq1))
.AsSubquery();
var q = Builders <MyModel2> .Select(x => x.Name1)
.Where(x => x.Name1.PgIn(subq));
Utils.AssertRawQuery(q, @"
SELECT name1
FROM ""public"".model2
WHERE (name1) IN (
SELECT t0.""name""
FROM model t0
LEFT JOIN ""public"".model2 t1 ON ((t0.""id"") = (t1.""id""))
WHERE ((t1.""id"") = (1)) AND ((t0.""id"") IN (SELECT ""id"" FROM model WHERE (""id"") = (1)))
)");
}
public IValidator Build()
{
var result = Validation.Provider.GetService <IValidatorSetter>();
result.SetRules(Builders.Select(i => i.Build()));
return(result);
}
public void SelectDistinct()
{
var q = Builders <MyModel> .Select(x => x.Id)
.Distinct();
Utils.AssertRawQuery(q, @"SELECT DISTINCT ""id"" FROM model");
}
public void FuncExtract()
{
// Extract
var q1 = Builders <MyModel> .Select(x => x.Id).Where(x => Func.Extract(ExtractField.Day, x.DateTime) == 2.0);
Utils.AssertRawQuery(q1, @"SELECT ""id"" FROM model WHERE (EXTRACT('day' FROM datetime)) = (@1::double precision)",
new Param(2.0, NpgsqlDbType.Double));
var q2 = Builders <MyModel> .Select(x => x.Id).Where(x => (int)Func.Extract(ExtractField.Day, x.DateTime) == 2);
Utils.AssertRawQuery(q2, @"SELECT ""id"" FROM model WHERE ((EXTRACT('day' FROM datetime))::int) = (2)");
// DatePart
var q3 = Builders <MyModel> .Select(x => x.Id).Where(x => Func.DatePart(ExtractField.Day, x.DateTime) == 2.0);
Utils.AssertRawQuery(q3, @"SELECT ""id"" FROM model WHERE (date_part('day',datetime)) = (@1::double precision)",
new Param(2.0, NpgsqlDbType.Double));
var q4 = Builders <MyModel> .Select(x => x.Id).Where(x => (int)Func.DatePart(ExtractField.Day, x.DateTime) == 2);
Utils.AssertRawQuery(q4, @"SELECT ""id"" FROM model WHERE ((date_part('day',datetime))::int) = (2)");
// Direct
var q5 = Builders <MyModel> .Select(x => x.Id).Where(x => x.DateTime.Day == 4);
Utils.AssertRawQuery(q5, @"SELECT ""id"" FROM model WHERE ((date_part('day',datetime))::int) = (4)");
}
public void SelectColumns()
{
var q = Builders <MyModel> .Select(x => new {
x.Name
});
Utils.AssertRawQuery(q, @"SELECT ""name"" FROM model");
}
public void WhereAndMultiple()
{
var q = Builders <MyModel> .Select(x => new { x.Id })
.Where(x => x.Id == 1)
.Where(x => x.Name == "test");
Utils.AssertRawQuery(q, @"SELECT ""id"" FROM model WHERE ((""id"") = (1)) AND ((""name"") = ('test'))");
}
public void FuncTimezone()
{
var dt = new DateTime(2000, 1, 2, 3, 4, 5);
var q = Builders <MyModel> .Select(x => x.Id).Where(x => Func.Timezone("UTC", x.DateTime) == dt);
Utils.AssertRawQuery(q, @"SELECT ""id"" FROM model WHERE (timezone('UTC',datetime)) = (@1::timestamp)",
new[] { new Param(dt, NpgsqlDbType.Timestamp), });
}
public void SelectColumnsExpressionString()
{
var q = Builders <MyModel> .Select(x => new {
OutName = x.Name + "A" + "B"
});
Utils.AssertRawQuery(q, @"SELECT ((""name"") || ('A')) || ('B') FROM model");
}
public void SelectEnum()
{
var q = Builders <MyModel> .Select(x => new {
x.Enum
});
Utils.AssertRawQuery(q, @"SELECT (""enum"")::text FROM model");
}
public void SelectOrderBy()
{
var q = Builders <MyModel> .Select(x => x.Id)
.OrderBy(x => x.Id + 2)
.OrderByDescending(x => x.DateTime);
Utils.AssertRawQuery(q, @"SELECT ""id"" FROM model ORDER BY (""id"") + (2),datetime DESC");
}
public void SelectLimit()
{
var q = Builders <MyModel> .Select(x => x.Id)
.Limit(1)
.Offset(2);
Utils.AssertRawQuery(q, @"SELECT ""id"" FROM model LIMIT 1 OFFSET 2");
}
public void SelectEnumArray()
{
var q = Builders <MyModel> .Select(x => new {
x.ListEnum
});
Utils.AssertRawQuery(q, @"SELECT (list_enum)::text[] FROM model");
}
public void WhereInArray()
{
var q = Builders <MyModel> .Select(x => new { x.Id })
.Where(WhereBuilder <MyModel> .In(x => x.Id, new[] { 1, 2, 3 }));
Utils.AssertRawQuery(q, @"SELECT ""id"" FROM model WHERE (""id"") = ANY(@1::int[])",
new Param(new[] { 1, 2, 3 }, NpgsqlDbType.Array | NpgsqlDbType.Integer));
}
public void WhereInSubquery()
{
var sq = Builders <MyModel> .Select(x => x.Id).AsSubquery();
var q = Builders <MyModel> .Select(x => new { x.Id })
.Where(WhereBuilder <MyModel> .In(x => x.Id, sq));
Utils.AssertRawQuery(q, @"SELECT ""id"" FROM model WHERE (""id"") IN (SELECT ""id"" FROM model)");
}
public void FuncGetVariable()
{
var q1 = Builders <MyModel> .Select(x => x.Id).Where(x => x.Id > Func.GetVariableInt("var1"));
Utils.AssertRawQuery(q1, @"SELECT ""id"" FROM model WHERE (""id"") > (current_setting('vars.var1')::int)");
var q2 = Builders <MyModel> .Select(x => x.Id).Where(x => x.Name == Func.GetVariableText("var1"));
Utils.AssertRawQuery(q2, @"SELECT ""id"" FROM model WHERE (""name"") = (current_setting('vars.var1')::text)");
}
public void FuncAggregateMaxMin()
{
var q1 = Builders <MyModel> .Select(x => AggregateFunc.Max(x.Id)).Where(x => x.DateTime > Func.Now());
Utils.AssertRawQuery(q1, @"SELECT MAX(""id"") FROM model WHERE (datetime) > (NOW())");
var q2 = Builders <MyModel> .Select(x => AggregateFunc.Min(x.Id)).Where(x => x.DateTime > Func.Now());
Utils.AssertRawQuery(q2, @"SELECT MIN(""id"") FROM model WHERE (datetime) > (NOW())");
}
public void FuncInterval()
{
var dt = new DateTime(2000, 1, 2, 3, 4, 5);
var ts = TimeSpan.FromHours(1.23);
var q = Builders <MyModel> .Select(x => x.Id).Where(x => x.DateTime + ts > dt);
Utils.AssertRawQuery(q, @"SELECT ""id"" FROM model WHERE ((datetime) + (@1::interval)) > (@2::timestamp)",
new Param(ts, NpgsqlDbType.Interval),
new Param(dt, NpgsqlDbType.Timestamp));
}
public void DeleteSimple()
{
var subq = Builders <MyModel> .Select(x => x.Name)
.Where(x => x.Id == 1)
.AsSubquery();
var q = Builders <MyModel> .Delete()
.Where(x => x.Name.PgIn(subq));
Utils.AssertRawQuery(q, @"DELETE FROM model WHERE (""name"") IN (SELECT ""name"" FROM model WHERE (""id"") = (1))");
}
public void WhereAnd()
{
var q = Builders <MyModel> .Select(x => new { x.Id })
.Where(x => x.Id == 1 && x.Name == "test");
var b = WhereBuilder <MyModel> .And(
WhereBuilder <MyModel> .FromExpression(x => x.Id == 1),
WhereBuilder <MyModel> .Eq(x => x.Name, "test"));
Utils.AssertRawQuery(q, b, @"SELECT ""id"" FROM model WHERE ((""id"") = (1)) AND ((""name"") = ('test'))");
}
public void WhereOr()
{
var q = Builders <MyModel> .Select(x => new { x.Id })
.Where(x => x.Id == 1 || x.Name == "test");
var b = WhereBuilder <MyModel> .Or(
WhereBuilder <MyModel> .Eq(x => x.Id, 1),
WhereBuilder <MyModel> .Eq(x => x.Name, "test"));
Utils.AssertRawQuery(q, b, @"SELECT ""id"" FROM model WHERE ((""id"") = (1)) OR ((""name"") = ('test'))");
}
public void UpdateSimple()
{
var subq = Builders <MyModel> .Select(x => x.Name)
.Where(x => x.Id == 1)
.AsSubquery();
var q = Builders <MyModel> .Update()
.Where(x => x.Name.PgIn(subq))
.SetField(x => x.Name, "A");
Utils.AssertRawQuery(q, @"UPDATE model SET ""name"" = 'A' WHERE (""name"") IN (SELECT ""name"" FROM model WHERE (""id"") = (1))");
}
public async Task TestInsertSubqueryFailMultipleRows()
{
var dr = await CreateDriver();
var subq = Builders <MyModel> .Select(x => x.Name)
.AsSubquery();
var obj = new MyModel2 {
};
await Assert.ThrowsAsync <Npgsql.PostgresException>(() => dr.Insert(obj)
.UseField(x => x.Name1, subq)
.ExecuteAsync());
}
public void SelectSimpleNullable()
{
var subq = Builders <MyModel> .Select(x => (int?)x.Id)
.Where(x => x.Id == 1)
.AsSubquery();
var q = Builders <MyModel2> .Select(x => x.Id)
.Where(x => x.Id.PgIn(subq));
Utils.AssertRawQuery(q, @"
SELECT ""id""
FROM ""public"".model2
WHERE (""id"") IN (
SELECT ""id"" FROM model
WHERE (""id"") = (1)
)");
}
public void InsertWithSubquery()
{
var subq = Builders <MyModel> .Select(x => x.Name)
.Where(x => x.Id == 1)
.AsSubquery();
var obj = new MyModel2 {
};
var q = Builders <MyModel2> .Insert(obj)
.UseField(x => x.Name1, subq);
Utils.AssertRawQuery(q, @"INSERT INTO ""public"".model2(name1)
VALUES ((SELECT ""name"" FROM model WHERE (""id"") = (1)))
RETURNING ""id"";");
}
public void InsertWithSubqueryOptional()
{
var subq = Builders <MyModel> .Select(x => x.Id)
.Where(x => x.Name == "subtest4")
.AsSubquery();
var obj = new MyModel2 {
};
var q = Builders <MyModel2> .Insert(obj)
.UseField(x => x.ModelId, subq);
Utils.AssertRawQuery(q, @"INSERT INTO ""public"".model2(model_id)
VALUES ((SELECT ""id"" FROM model WHERE (""name"") = ('subtest4')))
RETURNING ""id"";");
}
public async Task TestSelectSubquery()
{
var dr = await CreateDriver();
var subq = Builders <MyModel> .Select(x => x.Id)
.Where(x => x.Name.StartsWith("test"))
.AsSubquery();
var res = await dr.From <MyModel2>()
.Select(x => x.Name1)
.Where(x => x.Id.PgIn(subq))
.ToListAsync();
Assert.Collection(res,
item => Assert.Equal("subtest1", item),
item => Assert.Equal("subtest2", item),
item => Assert.Equal("subtest3", item));
}
public void FuncAggregateSum()
{
var q1 = Builders <MyModel> .Select(x => AggregateFunc.Sum(x.ValFloat));
Utils.AssertRawQuery(q1, @"SELECT SUM(val_f32) FROM model");
}
public void FuncRaw()
{
var q = Builders <MyModel> .Select(x => x.Id).Where(x => x.DateTime > Func.Raw <DateTime>("NOW() + INTERVAL 2 SECONDS"));
Utils.AssertRawQuery(q, @"SELECT ""id"" FROM model WHERE (datetime) > (NOW() + INTERVAL 2 SECONDS)");
}
