/// <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>
/// <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>
/// <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)));
}
/// <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="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);
/// <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="builder"></param>
public void OnCreating_Test1()
{
AddTable <DbEmployee>(DbFluent.Table(nameof(DbEmployee).Substring(2), "Mdm"))
.AddTable("RegionalDirectorate", DbFluent.Table(nameof(DbRegionalDirectorate).Substring(2), "Mdm"))
.AddTable <DbContact>(DbFluent.Table(nameof(DbContact).Substring(2), "Mdm"))
.AddRelationship("Employee_RegionalDirectorate",
Table <DbEmployee>(), Table("RegionalDirectorate"),
(nameof(DbEmployee.EmployeeId), nameof(DbRegionalDirectorate.RegionalDirectorateId)))
.AddRelationship <DbEmployee, DbContact>((p => p.MainContactId, q => q.ContactId))
.AddRelationship <DbEmployee, DbContact>("secondary", (p => p.MainContactId, q => q.ContactId))
.AddTuple("Fields_SelectEmployee",
DbFluent.FieldAsAll(Table <DbEmployee>()),
DbFluent.Field(nameof(DbRegionalDirectorate.RegionalDirectorateId), Table("RegionalDirectorate")),
DbFluent.Field(nameof(DbRegionalDirectorate.Code), Table("RegionalDirectorate")))
.AddTuple("Fields_SelectEmployee2",
DbFluent.FieldAsAll(Table <DbEmployee>("employee")),
DbFluent.Field <DbContact>(p => p.Code, Table <DbContact>("contact")).WithAlias("contactCode"),
DbFluent.Field(nameof(DbRegionalDirectorate.RegionalDirectorateId), Table("RegionalDirectorate", "regionalDirectorate")),
DbFluent.Field(nameof(DbRegionalDirectorate.Code), Table("RegionalDirectorate", "regionalDirectorate")))
.AddQuery(
DbFluent.StoredQuery(
DbFluent.SelectQuery(Table <DbEmployee>())
.From(
Table <DbEmployee>(),
DbFluent.TableAsJoin(
DbQueryJoinKind.Left,
Table("RegionalDirectorate"),
JoinCondition("Employee_RegionalDirectorate")))
.WithFields(Tuple("Fields_SelectEmployee"))
.AddIdField(p => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), p.UseParameter("code")))));
}
/// <summary> /// Creates a new Select advanced database query. /// </summary> /// <returns>Returns a new Select advanced database query</returns> public IDbSingleQuery SelectQuery <T>(Action <IDbSingleQuery> initAction = null) => DbFluent.SelectQuery(Table <T>(), initAction);
// Select --------------------------------
/// <summary>
/// Creates a new Select advanced database query.
/// </summary>
/// <returns>Returns a new Select advanced database query</returns>
public IDbSingleQuery SelectQuery <T>(string name, Action <IDbSingleQuery> initAction = null)
=> DbFluent.SelectQuery(name, Table <T>(), initAction);