public void SelectTestWithJoins(SupportedDatabaseTypes databaseTypes, bool useSchema, string schemaPrefixId, string expectedSql)
{
SqlAliasHelper.ClearAliases();
var addDictionary = new Dictionary <string, string> {
{ "database:schemaPrefix:Id", schemaPrefixId }
};
using (var scope = TestServiceProvider.GetDatabaseExampleServiceProvider(databaseTypes, addDictionary).CreateScope())
{
var dbConfig = scope.ServiceProvider.GetService <IDbConfigSchemaTargets>();
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId());
var builder = dbConfig.CreateSqlBuilder();
var select = builder.Select();
var sqls = new List <string>
{
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.From <Entity>()
.InnerJoin <Entity, ChildEntity>()
.LeftOuterJoin <ChildEntity, ChildChildEntity>()
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.From <Entity>()
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.InnerJoin <Entity, ChildEntity>()
.LeftOuterJoin <ChildEntity, ChildChildEntity>()
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.InnerJoin <Entity, ChildEntity>()
.LeftOuterJoin <ChildEntity, ChildChildEntity>()
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
};
foreach (var sql in sqls)
{
sql.Should().Be(expectedSql);
}
}
}
public void SelectTestWithJoins_WithTableNameSet(SupportedDatabaseTypes databaseTypes, string schema, string schemaPrefixId, string expectedSql)
{
SqlAliasHelper.ClearAliases();
const string entityTableName = "EntityTable";
const string childTableName = "ChildEntityTable";
const string childChildTableName = "ChildChildEntityTable";
var dbConfig = SqlBuilderFactory.DbConfigSchemaTargets(schema, schemaPrefixId, databaseTypes);
var useSchema = !string.IsNullOrEmpty(schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId() ?? string.Empty);
var builder = dbConfig.SqlBuilder();
var select = builder.Select();
var sqls = new List <string>
{
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.From <Entity>(entityTableName)
.InnerJoin <Entity, ChildEntity>(fromTableName: entityTableName, toTableName: childTableName)
.LeftOuterJoin <ChildEntity, ChildChildEntity>(fromTableName: childTableName, toTableName: childChildTableName)
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.From <Entity>(entityTableName)
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.InnerJoin <Entity, ChildEntity>(fromTableName: entityTableName, toTableName: childTableName)
.LeftOuterJoin <ChildEntity, ChildChildEntity>(fromTableName: childTableName, toTableName: childChildTableName)
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.InnerJoin <Entity, ChildEntity>(fromTableName: entityTableName, toTableName: childTableName)
.LeftOuterJoin <ChildEntity, ChildChildEntity>(fromTableName: childTableName, toTableName: childChildTableName)
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
};
foreach (var sql in sqls)
{
sql.Should().Be(expectedSql);
}
}
private void SetTableName <T>(Action additionAction = null, string originalTableName = null, string tableAlias = null)
{
if (!string.IsNullOrEmpty(TableName))
{
if (TableAlias.IsEmpty())
{
TableAlias = SqlAliasHelper.GetAliasForType <T>(tableAlias, TableName);
}
additionAction?.Invoke();
return;
}
TableName = SqlBuilderHelper.GetTableName <T>(SchemaNamePrefix, originalTableName);
TableAlias = SqlAliasHelper.GetAliasForType <T>(tableAlias, originalTableName);
additionAction?.Invoke();
}
public void SelectWithIfStatementTest(bool ifStatementResult, string schemaPrefixId, string expectedSql)
{
SqlAliasHelper.ClearAliases();
var dbConfig = SqlBuilderFactory.DbConfigSchemaTargets(null, schemaPrefixId, SupportedDatabaseTypes.Postgres);
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId() ?? string.Empty);
var builder = dbConfig.SqlBuilder();
var sql =
builder.Select()
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.WhereIf <Entity>(x => x.WV(item => item.Name, "Arild"), () => ifStatementResult)
.Build();
sql.Should().Be(expectedSql);
}
public void SelectWithIfStatementTest(bool ifStatementResult, string expectedSql)
{
SqlAliasHelper.ClearAliases();
using (var scope = TestServiceProvider.GetDatabaseExampleServiceProvider().CreateScope())
{
var dbConfig = scope.ServiceProvider.GetService <IDbConfigSchemaTargets>();
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId());
var builder = dbConfig.CreateSqlBuilder();
var sql =
builder.Select()
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.WhereIf <Entity>(x => x.WV(item => item.Name, "Arild"), () => ifStatementResult)
.Build();
sql.Should().Be(expectedSql);
}
}
public void SelectCountTestOneTableOnly(SupportedDatabaseTypes databaseTypes, string schema, string schemaPrefixId, string expectedSql)
{
SqlAliasHelper.ClearAliases();
var dbConfig = SqlBuilderFactory.DbConfigSchemaTargets(schema, schemaPrefixId, databaseTypes);
var useSchema = !string.IsNullOrEmpty(schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId() ?? string.Empty);
var builder = dbConfig.SqlBuilder();
var select = builder.Select();
var sqls = new List <string>
{
select
.Count()
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
select
.Count()
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.From <Entity>()
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build()
};
foreach (var sql in sqls)
{
sql.Should().Be(expectedSql);
}
}
private string GetTypeAlias <T>(string tableAlias, string tableName = null)
{
return(SqlAliasHelper.GetAliasForType <T>(tableAlias, tableName));
}
