// 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()));
}
/// <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);
