public async Task QueryTest()
{
var services = new ServiceCollection()
.AddRoscoePostgres("Host=localhost;Database=test");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var organisations = new OrganisationTable("o");
var subquery = db.Query(Guid.Parse("c0cac2b4-4821-4135-900b-4a36b46f0122").DbValue()).Value;
var subquery2 = db.Query(() => Guid.Parse("c0cac2b4-4821-4135-900b-4a36b46f0122").DbValue().Value()).Value;
var from = db.Query(() => new { Id = Guid.Parse("c0cac2b4-4821-4135-900b-4a36b46f0122").DbValue().Value() }).Value;
var query = db.Query(() => new
{
Id = organisations.Id.NullValue(),
Name = organisations.Name.ToLower().Value(),
//IdName = (organisations.Id + organisations.Name).Value(),
})
.From(organisations)
//.InnerJoin(from, from.Value().Id == organisations.Id)
.Where(organisations.Id == subquery.DbValue());
//.InnerJoin(organisations, organisations.Id == organisations.Id)
//.Where(organisations.Name == "")
//.GroupBy(organisations.Id);
var sql = query.ToString();
var result = await db.ExecuteAsync(query);
}
}
public IValueAccessor <GraphQLNotNull <GraphQLList <GraphQLNotNull <UserType> > > > Users()
{
var users = new UserTable("u");
return(queryBuilder.SelectList(
db.Query()
.From(users)
.Where(users.OrganisationId == organisations.Id),
x => new UserType(batch, db, users, x)));
}
public IValueAccessor <GraphQLNotNull <OrganisationType> > Organisation(Input <GraphQLNotNull <GraphQLID> > organisationId)
{
var organisations = new OrganisationTable("o");
return(provider
.QueryJson()
.Select(
db.Query()
.From(organisations)
.Where(organisations.Id == Guid.Parse(organisationId.Value())),
x => new OrganisationType(batch, db, organisations, x)));
}
public IValueAccessor <GraphQLNotNull <OrganisationType> > Organisation(Input <GraphQLNotNull <GraphQLID> > organisationId)
{
var organisations = new OrganisationTable("o");
return(provider
.QueryJson(
x => x.Query().ForJsonPathWithoutArrayWrapper(),
x => new SqlServerJsonQueryBuilderAdapter(db, x))
.Select(
db.Query()
.From(organisations)
.Where(organisations.Id == Guid.Parse(organisationId.Value())),
x => new OrganisationType(batch, db, organisations, x)));
}
public async Task UpdateTest()
{
var services = new ServiceCollection()
.AddRoscoePostgres("Host=localhost;Database=test");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var organisations = new OrganisationTable("o");
var subquery = db.Query(() => Guid.Parse("c0cac2b4-4821-4135-900b-4a36b46f0122").DbValue().Value()).Value;
var query = db.Update(organisations)
.Set(organisations.Name, "new name".DbValue())
//.From(organisations)
//.InnerJoin(organisations, organisations.Id == organisations.Id)
.Where(organisations.Id == subquery.DbValue())
.Returning(() => new
{
Id = organisations.Id.Value(),
Name = organisations.Name.Value(),
});
var sql = query.ToString();
var result = await db.ExecuteAsync(query);
}
}
public async Task QueryTest()
{
var services = new ServiceCollection()
.AddRoscoeSqlServer("Server=localhost;Database=test;");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var organisations = new OrganisationTable("o");
var query = db.Query(() => new
{
Id = organisations.Id.Value(),
Name = Elvis(organisations.Name.Value(), x => x.ToUpper()),
NameIsFoo = db.Functions
.Case()
.When(organisations.Name == new Foo().Value(), 1.DbValue())
.Else(0)
.EndCase().Value(),
})
.From(organisations)
.ForJsonPath();
var result = await db.ExecuteAsync(query);
}
}
public IValueAccessor <GraphQLNotNull <OrganisationType> > Organisation()
{
var organisations = new OrganisationTable("uo");
return(queryBuilder.Select(
db.Query()
.From(organisations)
.Where(users.OrganisationId == organisations.Id),
x => new OrganisationType(batch, db, organisations, x)));
}
public void InsertTest()
{
var services = new ServiceCollection()
.AddRoscoePostgres("");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var organisations = new OrganisationTable("o");
var selectQuery = db.Query(() => new
{
Id = organisations.Id.NullValue(),
Name = organisations.Name.ToLower().Value(),
//IdName = (organisations.Id + organisations.Name).Value(),
})
.From(organisations);
var updateQuery = db.Update(organisations)
.Set(organisations.Name, "new name".DbValue())
//.From(organisations)
//.InnerJoin(organisations, organisations.Id == organisations.Id)
.Where(organisations.Name == "")
;
var query = db.InsertValues(organisations, new { organisations.Id, organisations.Name })
.Values(new { Id = Guid.NewGuid().DbValue(), Name = "org 1".DbValue() })
.OnConflict(organisations.Id, organisations.Name)
//.DoNothing()
.Update(x => x.Set(organisations.Name, "org 1".DbValue()))
.Returning(() => new
{
Id = organisations.Id.Value(),
});
var selectSql = selectQuery.ToString();
var updateSql = updateQuery.ToString();
var sql = query.ToString();
}
}