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)"); }