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