public string GetRelationshipQuery(IRelationshipMetadata rel, List <IDataItem> parents) { //base query StringBuilder sb = new StringBuilder("SELECT"); //get base query sb.AppendFormat(" {0}", rel.RelationTable.Query); //mod sb.AppendFormat(" WHERE {0} % {1} = {2}", rel.RelationTable.ModField, Bucket.Catalog.ComputeNode.GlobalBucketCount, Bucket.BucketMod); //base filter if (!string.IsNullOrWhiteSpace(rel.RelationTable.Filter)) { sb.AppendFormat(" AND ({0})", rel.RelationTable.Filter); } //handle deleted if (!string.IsNullOrWhiteSpace(rel.RelationTable.SaturationDeleteField)) { IColumnMetadata col_delete = rel.RelationTable.ColumnsMetadata.Values.Where(c => c.FieldMapping == rel.RelationTable.SaturationDeleteField).FirstOrDefault(); if (col_delete.ColumnType == typeof(DateTime)) { sb.AppendFormat( " AND {0} {1} {2} {3}", col_delete.ColumnName, Comparators.GetNegatedComparator(rel.RelationTable.SaturationDeleteComparator), ((DateTime)rel.RelationTable.SaturationDeleteValue).ToShortDateString(), ((DateTime)rel.RelationTable.SaturationDeleteValue).ToShortTimeString()); } else if (col_delete.ColumnType == typeof(string)) { sb.AppendFormat( " AND {0} {1} '{2}'", col_delete.ColumnName, Comparators.GetNegatedComparator(rel.RelationTable.SaturationDeleteComparator), rel.RelationTable.SaturationDeleteValue.ToString()); } else if (col_delete.ColumnType == typeof(bool)) { sb.AppendFormat( " AND {0} {1} {2}", col_delete.ColumnName, Comparators.GetNegatedComparator(rel.RelationTable.SaturationDeleteComparator), ((bool)rel.RelationTable.SaturationDeleteValue) ? 1 : 0); } else { sb.AppendFormat( " AND {0} {1} {2}", col_delete.ColumnName, Comparators.GetNegatedComparator(rel.RelationTable.SaturationDeleteComparator), rel.RelationTable.SaturationDeleteValue); } } //handle parent relation StringBuilder parent_keys = new StringBuilder(); foreach (var item in parents) { object parent_key = item.GetType().GetField(rel.ParentField).GetValue(item); if (parent_keys.Length == 0) { parent_keys.AppendFormat("{0}", parent_key); } else { parent_keys.AppendFormat(",{0}", parent_key); } } IColumnMetadata col_parent = rel.RelationTable.ColumnsMetadata.Values.Where(c => c.FieldMapping == rel.ParentRelationshipField).FirstOrDefault(); sb.AppendFormat(" AND {0} in ({1})", col_parent.ColumnName, parent_keys.ToString()); //order by parent key sb.AppendFormat(" ORDER BY {0}", col_parent.ColumnName); return(sb.ToString()); }