/// <summary>
///
/// </summary>
/// <param name="q"></param>
/// <param name="orderBy"></param>
/// <param name="pageSize"></param>
/// <param name="pageToken"></param>
/// <param name="log"></param>
/// <returns></returns>
internal IDbQuery QuerySelectCommunities(
string q, string orderBy, int?pageSize = null, string pageToken = null,
IBdoLog log = null)
{
return(this.UseQuery("GetCommunityWithCode", p =>
DbFluent.SelectQuery(Table <DbCommunity>("community"))
.From(
DbFluent.TableAsJoin(DbQueryJoinKind.Left, Table("RegionalDirectorate"),
JoinCondition("Community_RegionalDirectorate")))
.WithFields(Tuple("SelectCommunity"))
.Filter(
q,
DbApiFluent.CreateFilterDefinition(
DbApiFluent.CreateFilterClause("startDate", Field <DbCommunity>(p => p.CreationDate), DataOperators.GreaterOrEqual),
DbApiFluent.CreateFilterClause("endDate", Field <DbCommunity>(p => p.LastModificationDate), DataOperators.LesserOrEqual),
DbApiFluent.CreateFilterClause("code", Field <DbCommunity>(p => p.Code), DataOperators.Equal)),
log)
.Sort(
orderBy,
DbApiFluent.CreateSortDefinition(
DbApiFluent.CreateSortClause("startDate", Field <DbCommunity>(p => p.CreationDate)),
DbApiFluent.CreateSortClause("endDate", Field <DbCommunity>(p => p.LastModificationDate)),
DbApiFluent.CreateSortClause("code", Field <DbCommunity>(p => p.Code))),
log)));
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="name"></param>
/// <param name="table"></param>
/// <param name="expressions"></param>
/// <returns></returns>
public IBdoDbModelBuilder AddTable <T>(DbTable table, params Expression <Func <T, object> >[] expressions) where T : class
{
Type type = typeof(T);
var tableName = type.Name;
if (table == null)
{
table = new DbTable
{
Name = tableName
};
if (type.GetCustomAttribute(typeof(BdoDbTableAttribute)) is BdoDbTableAttribute tableAttribute)
{
table.Name = tableAttribute.Name;
table.Schema = tableAttribute.Schema;
table.DataModule = tableAttribute.DataModule;
}
}
List <DbField> fields = new List <DbField>();
foreach (var expression in expressions)
{
fields.Add(DbFluent.Field(expression.GetProperty().Name));
}
AddTable(tableName, table, fields.ToArray());
return(this);
}
/// <summary>
///
/// </summary>
/// <param name="employee"></param>
/// <returns></returns>
internal IDbQuery InsertEmployee2(EmployeeDto employee)
{
return(DbFluent.InsertQuery(Table <DbEmployee>())
.WithFields(q => new[]
{
DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("newCode", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.ByteArrayField), q.UseParameter("ByteArrayField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.DoubleField), q.UseParameter("DoubleField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.DateTimeField), q.UseParameter("DateTimeField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.LongField), q.UseParameter("LongField", DataValueTypes.Date))
})
.WithIdFields(q => new[]
{
DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("oldCode", DataValueTypes.Text))
})
.WithReturnedIdFields(new[]
{
Field <DbEmployee>(p => p.EmployeeId)
})
.WithParameters(
ElementFactory.CreateScalar("newCode", employee.Code),
ElementFactory.CreateScalar("oldCode", "oldCode"),
ElementFactory.CreateScalar("ByteArrayField", employee.ByteArrayField),
ElementFactory.CreateScalar("DoubleField", employee.DoubleField),
ElementFactory.CreateScalar("DateTimeField", employee.DateTimeField),
ElementFactory.CreateScalar("LongField", employee.LongField)));
}
/// <summary>
/// Delete the specified employee.
/// </summary>
/// <param name="code">The code to consider.</param>
/// <returns>Returns the generated query.</returns>
internal IDbQuery DeleteEmployee5(string code)
{
var query = DbFluent.DeleteQuery(Table <DbEmployee>())
.Where(q => DbFluent.Eq(DbFluent.Field("code"), code));
return(query);
}
/// <summary>
/// Delete the specified employee.
/// </summary>
/// <param name="code">The code to consider.</param>
/// <returns>Returns the generated query.</returns>
internal IDbQuery DeleteEmployee1(string code)
{
var query = DbFluent.DeleteQuery(Table <DbEmployee>())
.AddIdField(DbFluent.FieldAsLiteral <DbEmployee>(p => p.Code, code));
return(query);
}
// Join conditions ---------------------------------------
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <param name="table1Alias"></param>
/// <param name="table2Alias"></param>
/// <returns></returns>
public DataExpression JoinCondition(
string name,
string table1Alias = null,
string table2Alias = null)
{
DbTableRelationship relationship = Relationship(name);
if (!string.IsNullOrEmpty(table1Alias))
{
relationship.Table1.Alias = table1Alias;
}
if (!string.IsNullOrEmpty(table2Alias))
{
relationship.Table2.Alias = table2Alias;
}
List <object> queryConditions = new List <object>();
foreach (var mapping in relationship.FieldMappingDictionary.Values)
{
queryConditions.Add(
DbFluent.Eq(
DbFluent.Field(mapping.Key, relationship.Table1),
DbFluent.Field(mapping.Content, relationship.Table2)));
}
return(DbFluent.And(queryConditions.ToArray()));
}
public void TestSqlLike()
{
var expression = DbFluent.Like(
DbFluent.Table("MyTable"),
DbFluent.Concat("%", DbFluent.Parameter("myText").AsExp(), "%"));
var log = new BdoLog();
string expectedResult = @"$sqlLike($sqlTable('MyTable'), $sqlConcat($sqlText('%'), $sqlParameter('myText'), $sqlText('%')))";
string result = (string)expression;
string xml = "";
if (log.HasErrorsOrExceptions())
{
xml = ". Result was '" + log.ToXml();
}
Assert.That(result.Trim().Equals(expectedResult.Trim(), StringComparison.OrdinalIgnoreCase), "Bad script interpretation" + xml);
// scripted
result = _appHost.Interpreter.Evaluate <string>(
expression,
new ScriptVariableSet().SetDbBuilder(new DbQueryBuilder_PostgreSql()),
log: log);
expectedResult = @"(""MyTable"" like concat('%', |*|p:myText|*|, '%'))";
xml = "";
if (log.HasErrorsOrExceptions())
{
xml = ". Result was '" + log.ToXml();
}
Assert.That(result.Trim().Equals(expectedResult.Trim(), StringComparison.OrdinalIgnoreCase), "Bad script interpretation" + xml);
}
public void TestInterprete_Fun_SqlEq()
{
var log = new BdoLog();
string value = null;
string fluentScript = DbFluent.Eq(
DbFluent.Field("RegionalDirectorateId"), DbFluent.IfNull(value, DbFluent.Field("RegionalDirectorateId")));
string expectedScript = "$sqlEq($sqlField('RegionalDirectorateId'), $sqlIfNull($sqlNull(), $sqlField('RegionalDirectorateId')))";
string xml = "";
if (log.HasErrorsOrExceptions())
{
xml = ". Result was '" + log.ToXml();
}
Assert.That(expectedScript.Equals(fluentScript, StringComparison.OrdinalIgnoreCase), "Bad fluent interpretation. Result was '" + xml);
var scriptVariableSet = new ScriptVariableSet();
scriptVariableSet.SetValue(VarSetDb.__DbBuilder,
DbQueryFactory.CreateQueryBuilder <DbQueryBuilder_PostgreSql>(GlobalVariables.AppHost));
string result = GlobalVariables.AppHost.Scope.Interpreter.Evaluate(fluentScript, DataExpressionKind.Script, scriptVariableSet, log: log)?.ToString();
string expectedResult = @"""RegionalDirectorateId""=COALESCE(NULL, ""RegionalDirectorateId"")";
if (log.HasErrorsOrExceptions())
{
xml = ". Result was '" + log.ToXml();
}
Assert.That(result.Trim().Equals(expectedResult.Trim(), StringComparison.OrdinalIgnoreCase), "Bad script interpretation" + xml);
}
/// <summary>
///
/// </summary>
/// <param name="community"></param>
/// <returns></returns>
internal IDbQuery QueryInsertCommunity(CommunityDto community)
{
return(DbFluent.InsertQuery(Table("Community"), true)
.From(
DbFluent.TableAsJoin(DbQueryJoinKind.Left, Table("RegionalDirectorate"),
JoinCondition <DbCommunity, DbRegionalDirectorate>("community", null))));
//.WithFields(p => Fields_InsertCommunity(p, community));
}
/// <summary>
///
/// </summary>
/// <param name="country"></param>
/// <returns></returns>
internal IDbQuery QueryInsertCountry(CountryDto country)
{
return(DbFluent.InsertQuery(Table("Country"), true)
.From(
DbFluent.TableAsJoin(DbQueryJoinKind.Left, Table("RegionalDirectorate"),
JoinCondition("Country_RegionalDirectorate"))));
//.WithFields(p => Fields_InsertCountry(p, country));
}
/// <summary>
/// Creates a new joined table.
/// </summary>
/// <param name="kind">The kind to consider.</param>
/// <param name="table1Alias">The alias of the table 1 to consider.</param>
/// <param name="table2Alias">The alias of the table 2 to consider.</param>
/// <returns>Returns a new From statement.</returns>
public DbJoinedTable TableAsJoin <T, T1, T2>(
DbQueryJoinKind kind,
string table1Alias = null, string table2Alias = null)
{
var conditionScript = JoinCondition <T1, T2>(table1Alias, table2Alias);
return(DbFluent.TableAsJoin(kind, Table <T>(), conditionScript));
}
/// <summary>
/// Delete the specified country.
/// </summary>
/// <param name="code">The code to consider.</param>
/// <returns>Returns the generated query.</returns>
internal IDbQuery QueryDeleteCountry(string code)
{
return(this.UseQuery("DeleteCountry", p =>
DbFluent.DeleteQuery(Table("Country"))
.AddIdField(q => DbFluent.FieldAsParameter(nameof(DbCountry.Code), q.UseParameter("code", DataValueTypes.Text))))
.WithParameters(
ElementFactory.CreateScalar("code", code)));
}
/// <summary>
/// Delete the specified employee.
/// </summary>
/// <param name="code">The code to consider.</param>
/// <returns>Returns the generated query.</returns>
internal IDbQuery DeleteEmployee4(string code)
{
var query = DbFluent.DeleteQuery(Table <DbEmployee>())
.Where(q => DbFluent.Eq(DbFluent.Field("code"), q.UseParameter("code", DataValueTypes.Text)))
.WithParameters(
ElementFactory.CreateScalar("code", code));
return(query);
}
/// <summary>
/// Delete the specified employee.
/// </summary>
/// <param name="code">The code to consider.</param>
/// <returns>Returns the generated query.</returns>
internal IDbQuery DeleteEmployee3(string code)
{
var query = DbFluent.DeleteQuery(Table <DbEmployee>())
.Where(q => DataExpressionFactory.CreateExpAsAuto("{{" + string.Format("$sqlEq($sqlField('Code'), {0})", q.UseParameter("code", DataValueTypes.Text).AsExp()) + "}}"))
.WithParameters(
ElementFactory.CreateScalar("code", code));
return(query);
}
/// <summary>
///
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
internal IDbQuery SelectEmployeeWithCode7(string code)
{
return(this.UseQuery("SelectEmployeeWithCode7", p =>
p.SelectQuery <DbEmployee>()
.WithFields(Tuple("Fields_SelectEmployee2"))
.AddField(DbFluent.FieldAsScript <DbEmployee>(p => p.DateTimeField, "$sqlGetCurrentDate()"))
)
.WithParameters(
ElementFactory.CreateScalar("code", code)));
}
/// <summary>
/// Delete the specified employee.
/// </summary>
/// <param name="code">The code to consider.</param>
/// <returns>Returns the generated query.</returns>
internal IDbQuery DeleteEmployee6(string code)
{
var query = DbFluent.DeleteQuery(Table <DbEmployee>())
.From(
DbFluent.TableAsJoin(
DbQueryJoinKind.Left,
Table("RegionalDirectorate").WithAlias("directorate"),
JoinCondition("Employee_RegionalDirectorate", null, "directorate")))
.Where(q => DbFluent.Eq(DbFluent.Field("code"), code));
return(query);
}
/// <summary>
///
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
internal IDbQuery QuerySelectCommunityWithCode(string code)
{
return(this.UseQuery("GetCommunityWithCode", p =>
DbFluent.SelectQuery(Table <DbCommunity>("community"))
.From(
DbFluent.TableAsJoin(DbQueryJoinKind.Left, Table("RegionalDirectorate"),
JoinCondition("Community_RegionalDirectorate")))
.WithFields(Tuple("SelectCommunity"))
.AddIdField(q => DbFluent.FieldAsParameter(nameof(DbCommunity.Code), q.UseParameter("code", DataValueTypes.Text))))
.WithParameters(
ElementFactory.CreateScalar("code", code)));
}
/// <summary>
///
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
internal IDbQuery SelectEmployeeWithCode5(string code)
{
return(this.UseQuery("SelectEmployeeWithCode5", p =>
p.SelectQuery <DbEmployee>()
.WithFields(Tuple("Fields_SelectEmployee"))
.Where(q => DbFluent.Exists(
q.UseSubQuery(
DbFluent.SelectQuery(DbFluent.Table("Employee", "Mdm"))
.AddIdField(q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text))))))
)
.WithParameters(
ElementFactory.CreateScalar("code", code)));
}
/// <summary>
///
/// </summary>
/// <param name="code"></param>
/// <param name="isPartialUpdate"></param>
/// <param name="employee"></param>
/// <returns></returns>
internal IDbQuery UpdateEmployee1(string code, bool isPartialUpdate, EmployeeDto employee)
{
return(this.UseQuery("UpdateEmployee1",
p =>
{
var query = DbFluent.UpdateQuery(Table <DbEmployee>())
.From(
DbFluent.TableAsJoin(
DbQueryJoinKind.Left,
Table("RegionalDirectorate"),
JoinCondition("Employee_RegionalDirectorate")))
.WithReturnedIdFields(Field("Code", "DbEmployee"));
query.AddField(
!isPartialUpdate || employee?.Code?.Length > 0,
q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text)));
query.AddField(
!isPartialUpdate || employee?.ByteArrayField?.Length > 0,
q => DbFluent.FieldAsParameter(nameof(DbEmployee.ByteArrayField), q.UseParameter("byteArrayField", DataValueTypes.ByteArray)));
query.AddField(
!isPartialUpdate || employee?.DateTimeField != null,
q => DbFluent.FieldAsParameter(nameof(DbEmployee.DoubleField), q.UseParameter("doubleField", DataValueTypes.Number)));
query.AddField(
!isPartialUpdate || employee?.DoubleField != null,
q => DbFluent.FieldAsParameter(nameof(DbEmployee.DateTimeField), q.UseParameter("dateTimeField", DataValueTypes.Date)));
query.AddField(
true,
q => DbFluent.FieldAsParameter(nameof(DbEmployee.LongField), q.UseParameter("longField", DataValueTypes.Long)));
query.AddField(
!isPartialUpdate || employee?.RegionalDirectorateCode?.Length > 0,
q => DbFluent.FieldAsQuery(nameof(DbEmployee.RegionalDirectorateId),
DbFluent.SelectQuery(Table("RegionalDirectorate"))
.AddField(DbFluent.Field(nameof(DbRegionalDirectorate.RegionalDirectorateId)))
.WithIdFields(
DbFluent.FieldAsParameter(nameof(DbRegionalDirectorate.Code), q.UseParameter("directorateCode", DataValueTypes.Text)))));
return query;
})
.WithParameters(
ElementFactory.CreateScalar("code", code),
ElementFactory.CreateScalar("byteArrayField", employee.ByteArrayField),
ElementFactory.CreateScalar("doubleField", employee.DoubleField),
ElementFactory.CreateScalar("dateTimeField", employee.DateTimeField),
ElementFactory.CreateScalar("directorateCode", null),
ElementFactory.CreateScalar("longField", employee.LongField)));
}
/// <summary>
///
/// </summary>
/// <param name="code"></param>
/// <param name="isPartialUpdate"></param>
/// <param name="country"></param>
/// <returns></returns>
internal IDbQuery QueryUpdateCountry(string code, bool isPartialUpdate, CountryDto country)
{
var query = DbFluent.UpdateQuery(Table("Country"))
.From(
DbFluent.TableAsJoin(DbQueryJoinKind.Left, Table("RegionalDirectorate"),
JoinCondition("Country_RegionalDirectorate")));
if (!isPartialUpdate || country?.Code?.Length > 0)
{
query.AddField(p => DbFluent.FieldAsParameter(nameof(DbCountry.Code), p.UseParameter("code", nameof(DbCountry.Code))));
}
return(query);
}
public void AddFields()
{
var log = new BdoLog();
var tuple = DbFluent.Tuple(
DbFluent.Field("field1", DbFluent.Table("Table1")),
DbFluent.Field("field2", DbFluent.Table("Table1")),
DbFluent.Field("field3", DbFluent.Table("Table1")).WithAlias("f3"))
.AddFields(
DbFluent.FieldAsLiteral("field1", 1),
DbFluent.FieldAsLiteral("field2", DbFluent.Table("Table2"), 2).WithAlias("f3"));
Assert.That(tuple.Fields.Count == 3, "Bad script interpretation");
}
/// <summary>
/// Delete the specified employee.
/// </summary>
/// <param name="code">The code to consider.</param>
/// <returns>Returns the generated query.</returns>
internal IDbQuery DeleteEmployee9(string code)
{
var query = DbFluent.DeleteQuery(Table <DbEmployee>())
.From(
DbFluent.TableAsJoin(DbQueryJoinKind.Left,
Table("RegionalDirectorate").WithAlias("directorate"),
JoinCondition("Employee_RegionalDirectorate")))
.Where(q => DbFluent.Eq(DbFluent.Field("code"), q.UseParameter("code", DataValueTypes.Text)))
.AddIdField(q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text)))
.WithParameters(
ElementFactory.CreateScalar("code", code));
return(query);
}
/// <summary>
/// Adds the specified query.
/// </summary>
/// <param name="name"></param>
/// <param name="query">The query to consider.</param>
/// <returns></returns>
public IBdoDbModelBuilder AddQuery(string name, IDbQuery query)
{
if (query != null)
{
if (string.IsNullOrEmpty(name))
{
name = query.GetName();
}
QueryDictionary.Remove(name);
QueryDictionary.Add(name, DbFluent.StoredQuery(name, query));
}
return(this);
}
/// <summary>
/// Delete the specified employee.
/// </summary>
/// <param name="code">The code to consider.</param>
/// <returns>Returns the generated query.</returns>
internal IDbQuery DeleteEmployee7(string code)
{
var query = DbFluent.DeleteQuery(Table <DbEmployee>())
.From(
TableAsJoin <DbContact>(
DbQueryJoinKind.Left,
JoinCondition <DbEmployee, DbContact>())
.WithAlias("mainCountry"))
.Where(q => DataExpressionFactory.CreateExpAsAuto("{{" + string.Format("$sqlEq($sqlField('Code'), {0})", q.UseParameter("code", DataValueTypes.Text).AsExp()) + "}}"))
.WithParameters(
ElementFactory.CreateScalar("code", code));
return(query);
}
/// <summary>
///
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
internal IDbQuery SelectEmployeeWithCode2(string code)
{
return(this.UseQuery("SelectEmployeeWithCode2", p =>
DbFluent.SelectQuery(Table <DbEmployee>())
.From(
DbFluent.TableAsJoin(
DbQueryJoinKind.Left,
Table("RegionalDirectorate"),
JoinCondition("Employee_RegionalDirectorate")))
.WithFields(Tuple("Fields_SelectEmployee"))
.WithLimit(100)
.AddIdField(q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text)))
.Where(DbFluent.Eq(DbFluent.Field("field1"), DbFluent.Field("field2"))))
.WithParameters(
ElementFactory.CreateScalar("code", code)));
}
public void TestSqlValue()
{
var expression = DbFluent.Value(_value_datetime);
var log = new BdoLog();
string expectedResult = @"$sqlValue('2020-12-20T00:00:00')";
string result = (string)expression;
string xml = "";
if (log.HasErrorsOrExceptions())
{
xml = ". Result was '" + log.ToXml();
}
Assert.That(result.Trim().Equals(expectedResult.Trim(), StringComparison.OrdinalIgnoreCase), "Bad script interpretation" + xml);
}
/// <summary>
///
/// </summary>
/// <param name="employee"></param>
/// <returns></returns>
internal IDbQuery InsertEmployee3(EmployeeDto employee)
{
return(DbFluent.InsertQuery(Table <DbEmployee>())
.WithFields(q => new[]
{
DbFluent.Field(nameof(DbEmployee.Code)),
DbFluent.Field(nameof(DbEmployee.ByteArrayField)),
DbFluent.Field(nameof(DbEmployee.DoubleField)),
DbFluent.Field(nameof(DbEmployee.DateTimeField)),
DbFluent.Field(nameof(DbEmployee.LongField))
})
.WithReturnedIdFields(new[]
{
Field <DbEmployee>(p => p.EmployeeId)
})
.From(p => p.UseSubQuery(
DbFluent.SelectQuery(Table <DbEmployee>())
.WithFields(q => new[]
{
DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("newCode", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.ByteArrayField), q.UseParameter("ByteArrayField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.DoubleField), q.UseParameter("DoubleField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.DateTimeField), q.UseParameter("DateTimeField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.LongField), q.UseParameter("LongField", DataValueTypes.Date)),
DbFluent.FieldAsQuery <DbContact>(p => p.ContactId,
DbFluent.SelectQuery(Table <DbContact>())
.WithLimit(1)
.AddField(Field <DbContact>(f => f.ContactId))
.AddIdField(
DbFluent.FieldAsParameter <DbContact>(f => f.Code, q.UseParameter("contactCode", DataValueTypes.Text)))),
})
.WithIdFields(q => new[]
{
DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("oldCode", DataValueTypes.Text))
})
.WithParameters(
ElementFactory.CreateScalar("newCode", employee.Code),
ElementFactory.CreateScalar("contactCode", "contactCodeA"),
ElementFactory.CreateScalar("oldCode", "oldCode"),
ElementFactory.CreateScalar("ByteArrayField", employee.ByteArrayField),
ElementFactory.CreateScalar("DoubleField", employee.DoubleField),
ElementFactory.CreateScalar("DateTimeField", employee.DateTimeField),
ElementFactory.CreateScalar("LongField", employee.LongField)))));
}
/// <summary>
///
/// </summary>
/// <param name="employee"></param>
/// <returns></returns>
internal IDbQuery UpsertEmployee(EmployeeDto employee)
{
return(DbFluent.Upsert(Table <DbEmployee>())
.WithQueries(
DbFluent.UpdateQuery(Table <DbEmployee>())
.WithFields(q => new[]
{
DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.ByteArrayField), q.UseParameter("ByteArrayField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.DoubleField), q.UseParameter("DoubleField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.DoubleField), q.UseParameter("DoubleField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.DateTimeField), q.UseParameter("DateTimeField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.LongField), q.UseParameter("LongField", DataValueTypes.Date))
}),
DbFluent.InsertQuery(Table <DbEmployee>())
.WithFields(q => new[]
{
DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.ByteArrayField), q.UseParameter("ByteArrayField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.DoubleField), q.UseParameter("DoubleField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.DateTimeField), q.UseParameter("DateTimeField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.LongField), q.UseParameter("LongField", DataValueTypes.Date))
})
.WithParameters(
ElementFactory.CreateScalar("code", employee.Code),
ElementFactory.CreateScalar("ByteArrayField", employee.ByteArrayField),
ElementFactory.CreateScalar("DoubleField", employee.DoubleField),
ElementFactory.CreateScalar("DateTimeField", employee.DateTimeField),
ElementFactory.CreateScalar("LongField", employee.LongField)))
.WithCTE(
DbFluent.TableAsQuery(
DbFluent.SelectQuery(null)
.WithFields(q => new[]
{
DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text))
}))
.WithAlias("T"))
.WithParameters(
ElementFactory.CreateScalar("code", employee.Code),
ElementFactory.CreateScalar("ByteArrayField", employee.ByteArrayField),
ElementFactory.CreateScalar("DoubleField", employee.DoubleField),
ElementFactory.CreateScalar("DateTimeField", employee.DateTimeField),
ElementFactory.CreateScalar("LongField", employee.LongField)));
}
/// <summary>
///
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
internal IDbQuery SelectEmployeeWithCode3(string code)
{
return(this.UseQuery("SelectEmployeeWithCode3", p =>
DbFluent.SelectQuery(null)
.From(
Table <DbEmployee>(),
DbFluent.TableAsJoin(
DbQueryJoinKind.Left,
Table("RegionalDirectorate"),
JoinCondition("Employee_RegionalDirectorate")))
.WithFields(Tuple("Fields_SelectEmployee"))
.WithLimit(100)
.OrderBy(
DbFluent.OrderBy(Field <DbEmployee>(p => p.Code, "employee")),
DbFluent.OrderBy(Field <DbEmployee>(p => p.DateTimeField, "regionalDirectorate"), DataSortingModes.Descending))
.AddIdField(q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text))))
.WithParameters(
ElementFactory.CreateScalar("code", code)));
}
/// <summary>
///
/// </summary>
/// <param name="q"></param>
/// <param name="orderBy"></param>
/// <param name="pageSize"></param>
/// <param name="pageToken"></param>
/// <param name="log"></param>
/// <returns></returns>
internal IDbQuery ListEmployees1(
string q, string orderBy, int?pageSize = null, string pageToken = null,
IBdoLog log = null)
=> DbFluent.SelectQuery("GetMyTables", DbFluent.Table())
.WithLimit(100)
.AsDistinct()
.WithFields(
DbFluent.FieldAsAll(DbFluent.Table("table")),
DbFluent.Field("Field1", DbFluent.Table("table")),
DbFluent.Field("Field2", DbFluent.Table("table")))
.From(
DbFluent.Table(nameof(DbRegionalDirectorate).Substring(2), "schema1").WithAlias("table"),
DbFluent.TableAsJoin(
DbQueryJoinKind.Left,
DbFluent.Table("DbTable1".Substring(2), "schema2").WithAlias("table1"),
DbFluent.And(
DbFluent.Eq(
DbFluent.Field("table1key", DbFluent.Table("table1")),
DbFluent.Field(nameof(DbRegionalDirectorate.Code), DbFluent.Table("table"))),
DbFluent.Eq(
DbFluent.Field("table2key", DbFluent.Table("table2")),
DbFluent.Field(nameof(DbRegionalDirectorate.LabelEN), DbFluent.Table("table"))))),
DbFluent.TableAsJoin(
DbQueryJoinKind.Left,
DbFluent.Table("DbTable1".Substring(2), "schema2").WithAlias("table2"),
DbFluent.Eq(
DbFluent.Field("table1key", DbFluent.Table("table2")),
DbFluent.Field("Field1", DbFluent.Table("table"))))
)
.Filter(
q,
DbApiFluent.CreateFilterDefinition(
DbApiFluent.CreateFilterClause("startDate", DbFluent.Field("CreationDate", DbFluent.Table("table")), DataOperators.GreaterOrEqual),
DbApiFluent.CreateFilterClause("endDate", DbFluent.Field("LastModificationDate", DbFluent.Table("table")), DataOperators.LesserOrEqual),
DbApiFluent.CreateFilterClause("code", DbFluent.Field("Code", DbFluent.Table("table")), DataOperators.Equal)),
log)
.Sort(
orderBy,
DbApiFluent.CreateSortDefinition(
DbApiFluent.CreateSortClause("startDate", DbFluent.Field("CreationDate", DbFluent.Table("table"))),
DbApiFluent.CreateSortClause("endDate", DbFluent.Field("LastModificationDate", DbFluent.Table("table"))),
DbApiFluent.CreateSortClause("code", DbFluent.Field("Code", DbFluent.Table("table")))),
log);
