/// <summary> /// Get columns that are wanted to execute select query. /// </summary> /// <param name="type"></param> /// <param name="columns"></param> /// <param name="tables"></param> /// <param name="excluded">Excluded columns.</param> private void GetColumns( Type type, Dictionary <Type, List <string> > columns, Dictionary <Type, GXSerializedItem> tables) { if (tables.ContainsKey(type)) { bool exists = columns.ContainsKey(type); List <string> list; if (exists) { list = columns[type]; } else { list = new List <string>(); } tables.Remove(type); string tableName = null; Dictionary <string, GXSerializedItem> properties = GXSqlBuilder.GetProperties(type); foreach (var it in properties) { if (!list.Contains(it.Key)) { if (it.Value.Relation != null) { if (it.Value.Relation.RelationType == RelationType.ManyToMany) { GetColumns(it.Value.Relation.RelationMapTable.Relation.PrimaryTable, columns, tables); } else { GetColumns(it.Value.Relation.ForeignTable, columns, tables); } if (it.Value.Relation.RelationType == RelationType.OneToOne || it.Value.Relation.RelationType == RelationType.Relation) { list.Add(it.Key); } } else { list.Add(it.Key); } } else { Debug.WriteLine("Column " + tableName + "." + it.Key + " is excluded."); } } if (!exists && list.Count != 0) { columns.Add(type, list); } } }
private void GetRelations(Type type, List <GXJoin> joinList, List <Type> tables, Dictionary <Type, List <string> > columns, bool getRelations) { if (!tables.Contains(type)) { //TODO: lisää shared table attribute. bool ignorebaseType = false; foreach (var it in tables) { if (GXDbHelpers.IsSharedTable(it) && type.IsAssignableFrom(it.BaseType)) { ignorebaseType = true; break; } } if (ignorebaseType) { return; } tables.Add(type); Type tp; bool added; List <string> excluded = null; List <string> cols = null; string tableName = null; if (columns != null && columns.ContainsKey(type)) { cols = columns[type]; if (cols.Contains("*")) { cols = null; } } Dictionary <string, GXSerializedItem> properties = GXSqlBuilder.GetProperties(type); foreach (var it in properties) { if (cols != null && !cols.Contains(it.Key)) { continue; } if (!IsExcluded(it.Key, excluded)) { if (it.Value.Relation != null && getRelations) { GXJoin j = new GXJoin(); tp = it.Value.Relation.PrimaryTable; j.Table1Type = tp; bool shared = GXDbHelpers.IsSharedTable(tp); if (shared || GXDbHelpers.IsAliasName(tp)) { j.Alias1 = GXDbHelpers.OriginalTableName(tp); } j.Table2Type = it.Value.Relation.ForeignTable; j.UpdateTables(tp, it.Value.Relation.ForeignTable); //If nullable. Type tp2 = it.Value.Relation.ForeignId.Type; j.AllowNull1 = !shared && tp2.IsGenericType && tp2.GetGenericTypeDefinition() == typeof(Nullable <>); if (!j.AllowNull1) { tp2 = it.Value.Relation.PrimaryId.Type; j.AllowNull2 = !GXDbHelpers.IsSharedTable(it.Value.Relation.ForeignTable) && tp2.IsGenericType && tp2.GetGenericTypeDefinition() == typeof(Nullable <>); } if (GXDbHelpers.IsSharedTable(it.Value.Relation.ForeignTable) || GXDbHelpers.IsAliasName(it.Value.Relation.ForeignTable)) { j.Alias2 = GXDbHelpers.OriginalTableName(it.Value.Relation.ForeignTable); } if (it.Value.Relation.RelationType == RelationType.OneToOne || it.Value.Relation.RelationType == RelationType.Relation) { j.Column2 = GXDbHelpers.GetColumnName(it.Value.Relation.PrimaryId.Relation.ForeignId.Target as PropertyInfo, Parent.Settings.ColumnQuotation); j.Column1 = GXDbHelpers.GetColumnName(it.Value.Relation.PrimaryId.Target as PropertyInfo, Parent.Settings.ColumnQuotation); if (tables.Contains(it.Value.Relation.ForeignTable)) { continue; } //If nullable. tp2 = it.Value.Relation.PrimaryId.Relation.ForeignId.Type; j.AllowNull1 = !shared && tp2.IsGenericType && tp2.GetGenericTypeDefinition() == typeof(Nullable <>); if (!j.AllowNull1) { tp2 = it.Value.Relation.PrimaryId.Relation.PrimaryId.Type; j.AllowNull2 = !GXDbHelpers.IsSharedTable(it.Value.Relation.ForeignTable) && tp2.IsGenericType && tp2.GetGenericTypeDefinition() == typeof(Nullable <>); } } else if (it.Value.Relation.RelationType == RelationType.OneToMany) { j.Column1 = GXDbHelpers.GetColumnName(it.Value.Relation.PrimaryId.Relation.ForeignId.Target as PropertyInfo, Parent.Settings.ColumnQuotation); j.Column2 = GXDbHelpers.GetColumnName(it.Value.Relation.PrimaryId.Target as PropertyInfo, Parent.Settings.ColumnQuotation); //If nullable. tp2 = it.Value.Relation.ForeignId.Type; j.AllowNull1 = !shared && tp2.IsGenericType && tp2.GetGenericTypeDefinition() == typeof(Nullable <>); if (!j.AllowNull1) { tp2 = it.Value.Relation.PrimaryId.Type; j.AllowNull2 = !GXDbHelpers.IsSharedTable(it.Value.Relation.ForeignTable) && tp2.IsGenericType && tp2.GetGenericTypeDefinition() == typeof(Nullable <>); } } else if (it.Value.Relation.RelationType == RelationType.ManyToMany) { j.Table2Type = it.Value.Relation.RelationMapTable.Relation.PrimaryTable; j.UpdateTables(tp, it.Value.Relation.RelationMapTable.Relation.PrimaryTable); j.Column2 = GXDbHelpers.GetColumnName(GXSqlBuilder.FindRelation(it.Value.Relation.RelationMapTable.Relation.PrimaryTable, tp).Target as PropertyInfo, Parent.Settings.ColumnQuotation); j.Column1 = GXDbHelpers.GetColumnName(GXSqlBuilder.FindUnique(tp).Target as PropertyInfo, Parent.Settings.ColumnQuotation); added = false; //Check that join is not added already. string j1 = j.Table1; string j2 = j.Table2; foreach (var it4 in joinList) { string t1 = it4.Table1; string t2 = it4.Table2; if ((j1 == t1 && j2 == t2) || (j2 == t1 && j1 == t2)) { added = true; break; } } if (!added) { joinList.Add(j); j = new GXJoin(); Type tmp = it.Value.Type; j.AllowNull1 = !!GXDbHelpers.IsSharedTable(tmp) && tmp.IsGenericType && tmp.GetGenericTypeDefinition() == typeof(Nullable <>); j.UpdateTables(it.Value.Relation.RelationMapTable.Relation.PrimaryTable, it.Value.Relation.ForeignTable); j.Column1 = GXDbHelpers.GetColumnName(it.Value.Relation.RelationMapTable.Target as PropertyInfo, Parent.Settings.ColumnQuotation); j.Column2 = GXDbHelpers.GetColumnName(it.Value.Relation.ForeignId.Target as PropertyInfo, Parent.Settings.ColumnQuotation); joinList.Add(j); tables.Add(it.Value.Relation.RelationMapTable.Relation.PrimaryTable); if (cols != null) { tables.Add(it.Value.Relation.ForeignTable); } } j = null; } if (j != null) { //Check that join is not added already. added = false; string j1 = j.Table1; string j2 = j.Table2; int index = -1; foreach (var it4 in joinList) { string t1 = it4.Table1; string t2 = it4.Table2; if ((j1 == t1 && j2 == t2) || (j2 == t1 && j1 == t2)) { added = true; break; } if (j.Alias2 != null && it4.Alias2 == j.Alias2) { ++index; } } //If we have multiple referenced to same table. if (index != -1) { j.Index = index + 1; } if (!added) { joinList.Add(j); } } if (cols == null || cols.Contains(it.Key)) { GetRelations(it.Value.Relation.ForeignTable, joinList, tables, columns, getRelations); } } } else { System.Diagnostics.Debug.WriteLine("Column " + tableName + "." + it.Key + " Skipped."); } } } }
public override string ToString() { if (Parent.Updated || Updated) { ColumnList.Clear(); List <GXJoin> joinList = new List <GXJoin>(); GXOrderByCollection.UpdateJoins(Parent.Settings, Joins, joinList); string[] list; StringBuilder sb = new StringBuilder(); //Get columns. Dictionary <string, GXSerializedItem> properties; Dictionary <Type, GXSerializedItem> neededTables = new Dictionary <Type, GXSerializedItem>(); foreach (var it in List) { //No relations. if (!neededTables.ContainsKey(it.Parameters[0].Type)) { neededTables.Add(it.Parameters[0].Type, null); } list = GXDbHelpers.GetMembers(Parent.Settings, it, '\0', false); foreach (var it2 in list) { properties = GXSqlBuilder.GetProperties(it.Parameters[0].Type); if (it2 != "*" && ColumnList.ContainsKey(it.Parameters[0].Type)) { GXSerializedItem si = properties[it2]; if (si.Relation != null) { //Get properties. GetColumns(si.Relation.ForeignTable, ColumnList, neededTables); } if (si.Relation == null || si.Relation.RelationType != RelationType.ManyToMany) { ColumnList[it.Parameters[0].Type].Add(it2); } } else { if (it2 == "*") { GetColumns(it.Parameters[0].Type, ColumnList, neededTables); } else { if (neededTables.ContainsKey(it.Parameters[0].Type)) { neededTables.Remove(it.Parameters[0].Type); } List <string> columns2 = new List <string>(); columns2.Add(it2); ColumnList.Add(it.Parameters[0].Type, columns2); if (properties.ContainsKey(it2)) { GXSerializedItem si = properties[it2]; if (si.Relation != null) { //Get properties. GetColumns(si.Relation.ForeignTable, ColumnList, neededTables); } } } } } } foreach (var it in ColumnList) { foreach (KeyValuePair <Type, LambdaExpression> x in Excluded) { if (x.Key == it.Key) { string[] removed = GXDbHelpers.GetMembers(null, x.Value, '\0', false); foreach (string col in removed) { it.Value.Remove(col); } } } } SelectToString(Parent.Settings, sb, Parent.Distinct, ColumnList, joinList, Parent.Index, Parent.Count); sql = sb.ToString(); Updated = false; } return(sql); }
/// <summary> /// Update where condition. The DefaultValue attribute is used as a filter. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="target"></param> /// <param name="exact">Is compared value exact or containing given value.</param> public void FilterBy <T>(T target, bool exact) { if (target != null) { Dictionary <string, GXSerializedItem> properties = GXSqlBuilder.GetProperties(GXInternal.GetPropertyType(target.GetType())); foreach (var it in properties) { if ((it.Value.Attributes & Attributes.DefaultValue) != 0 && it.Value.Get != null) { object actual = it.Value.Get(target); if (actual != null && it.Value.DefaultValue == null) { if (actual is DateTime d) { if (d == DateTime.MinValue) { continue; } } else if (actual is DateTimeOffset dto) { if (dto.DateTime == DateTime.MinValue) { continue; } } else if (actual is Guid q) { if (q == Guid.Empty) { continue; } } else if (!(actual is string) && typeof(System.Collections.IEnumerable).IsAssignableFrom(actual.GetType())) { //Arrays and lists are not filtered. continue; } else if (!(actual is string) && actual.GetType().IsClass) { FilterBy(actual, exact); continue; } } if (Convert.ToString(it.Value.DefaultValue) != Convert.ToString(actual)) { if (actual != null) { if (actual.GetType().IsEnum) { actual = Convert.ToInt64(actual); } if (actual is bool b) { int val = b ? 1 : 0; And <T>(q => it.Value.Target.Equals(val)); } else if (exact || actual is Guid) { And <T>(q => it.Value.Target == actual); } else { if (actual is DateTime d) { if (d == DateTime.MinValue || d == DateTime.MaxValue) { And <T>(q => it.Value.Target == actual); } else { And <T>(q => (DateTime)it.Value.Target >= d); } } else { And <T>(q => GXSql.Contains(it.Value.Target, actual)); } } } } } } } }
internal string ToString(ref string post) { if (Parent.Updated || Updated) { ColumnList.Clear(); List <GXJoin> joinList = new List <GXJoin>(); GXOrderByCollection.UpdateJoins(Parent.Settings, Joins, joinList); string[] list; StringBuilder sb = new StringBuilder(); //Get columns. Dictionary <string, GXSerializedItem> properties; Dictionary <Type, GXSerializedItem> neededTables = new Dictionary <Type, GXSerializedItem>(); foreach (var it in List) { //No relations. if (!neededTables.ContainsKey(it.Key.Parameters[0].Type)) { neededTables.Add(it.Key.Parameters[0].Type, null); } list = GXDbHelpers.GetMembers(Parent.Settings, it.Key, '\0', false, ref post); foreach (var it2 in list) { properties = GXSqlBuilder.GetProperties(it.Key.Parameters[0].Type); if (it2 != "*" && ColumnList.ContainsKey(it.Key.Parameters[0].Type)) { if (properties.ContainsKey(it2)) { GXSerializedItem si = properties[it2]; if (si.Relation != null) { //Get properties. GetColumns(si.Relation.ForeignTable, ColumnList, neededTables); } if (si.Relation == null || si.Relation.RelationType != RelationType.ManyToMany) { ColumnList[it.Key.Parameters[0].Type].Add(it2); } } else { string str = it2; if (it.Value != null) { string[] tmp = GXDbHelpers.GetMembers(Parent.Settings, it.Value, '\0', false, ref post); str += " AS " + tmp[0]; } ColumnList[it.Key.Parameters[0].Type].Add(str); } } else { if (it2 == "*") { GetColumns(it.Key.Parameters[0].Type, ColumnList, neededTables); } else { if (neededTables.ContainsKey(it.Key.Parameters[0].Type)) { neededTables.Remove(it.Key.Parameters[0].Type); } List <string> columns2 = new List <string>(); columns2.Add(it2); ColumnList.Add(it.Key.Parameters[0].Type, columns2); if (properties.ContainsKey(it2)) { GXSerializedItem si = properties[it2]; if (si.Relation != null) { //Get properties. GetColumns(si.Relation.ForeignTable, ColumnList, neededTables); } } } } } } foreach (var it in ColumnList) { foreach (KeyValuePair <Type, LambdaExpression> x in Excluded) { if (x.Key == it.Key) { string[] removed = GXDbHelpers.GetMembers(null, x.Value, '\0', false, ref post); foreach (string col in removed) { bool includeQuery = false; string col2 = GXDbHelpers.AddQuotes(col, Parent.Settings.ColumnQuotation); //Joins are not removed from the qyery or 1:1 doesn't work. foreach (var j in joinList) { if ((it.Key == j.Table1Type && j.Column1 == col2) || (it.Key == j.Table2Type && j.Column2 == col2)) { includeQuery = true; break; } } if (!includeQuery) { it.Value.Remove(col); } } } } } SelectToString(Parent.Settings, sb, Parent.Distinct, ColumnList, joinList, Parent.Index, Parent.Count, post); sql = sb.ToString(); Updated = false; } return(sql); }
/// <summary> /// Update where condition. The DefaultValue attribute is used as a filter. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="target"></param> /// <param name="exact">Is compared value exact or containing given value.</param> public void FilterBy <T>(T target, bool exact) { if (target != null) { Dictionary <string, GXSerializedItem> properties = GXSqlBuilder.GetProperties(GXInternal.GetPropertyType(target.GetType())); foreach (var it in properties) { if ((it.Value.Attributes & Attributes.DefaultValue) != 0) { object actual = it.Value.Get(target); if (it.Value.DefaultValue == null) { if (actual is DateTime d) { if (d == DateTime.MinValue) { continue; } } if (actual is Guid q) { if (q == Guid.Empty) { continue; } } } if (Convert.ToString(it.Value.DefaultValue) != Convert.ToString(actual)) { if (actual != null) { if (actual.GetType().IsEnum) { actual = Convert.ToInt64(actual); } if (exact || actual is Guid) { And <T>(q => it.Value.Target == actual); } else { if (actual is DateTime d) { if (d == DateTime.MinValue || d == DateTime.MaxValue) { And <T>(q => it.Value.Target == actual); } else { And <T>(q => (DateTime)it.Value.Target >= d); } } else { And <T>(q => GXSql.Contains(it.Value.Target, actual)); } } } } } } } }