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