private static Table JoinInternal(MetadataHelper helper, string Totable, string ToSchema, Join.JoinTypes JoinType)
{
if (helper.Model.PrimaryKey.Columns.Count != 1)
{
throw new InvalidOperationException("Only tables with one primary key field is supported");
}
MetadataColumn FromField = helper.Model.PrimaryKey.Columns.First();
MetadataTable mt = helper.Table.Builder.Metadata.FindTable(ToSchema + "." + Totable);
JoinConditionGroup jcg = To(helper.Table.Builder, helper.Table, helper.Model, FromField, mt, JoinType, false);
return(jcg.ToTable());
}
private static Table JoinInternal(MetadataHelper helper, string ForeignKeyField, Join.JoinTypes JoinType)
{
MetadataColumn FromField = null;
if (!helper.Model.Columns.TryGetValue(ForeignKeyField, out FromField))
{
throw new ArgumentException("The Field " + ForeignKeyField + " was not found", "FromField");
}
List <MetadataForeignKey> Fks = new List <MetadataForeignKey>(helper.Model.FindForeignKeys(FromField));
if (Fks.Count != 1)
{
throw new ArgumentException("The Field " + ForeignKeyField + " points to more than one table", "FromField");
}
MetadataForeignKey FK = Fks.First();
string table = FK.ReferencedSchema + "." + FK.ReferencedTable;
MetadataTable ToTable = helper.Table.Builder.Metadata.FindTable(table);
if (ToTable == null)
{
throw new InvalidOperationException("The table '" + table + "' was not found in metadata");
}
JoinConditionGroup jcg = To(helper.Table.Builder, helper.Table, helper.Model, FromField, ToTable, JoinType, true);
Table toTable = jcg.ToTable();
if (FromField.IncludeColumns != null)
{
foreach (string include in FromField.IncludeColumns)
{
string iName = include;
string iAlias = null;
if (include.IndexOf('=') > 0)
{
iName = include.Split('=')[0];
iAlias = include.Split('=')[1];
}
else
{
iAlias = FromField.Name + "_" + iName;
}
toTable.Column(iName, iAlias);
}
}
return(toTable);
}
private static JoinConditionGroup To(SqlBuilder Builder, Table FromSqlTable, MetadataTable FromTable, MetadataColumn FromField, MetadataTable ToTable, Join.JoinTypes JoinType, bool PreferForeignKeyOverPrimaryKey = true)
{
MetadataDatabase mdb = Builder.Metadata;
List <MetadataForeignKey> Fks = null;
MetadataForeignKey FK = null;
Join j = null;
MetadataColumnReference mcr = null;
JoinConditionGroup jcg = null;
if (FromField.IsPrimaryKey)
{
if (!FromField.IsForeignKey || !PreferForeignKeyOverPrimaryKey)
{
Fks = ToTable.ForeignKeys.Values.Where(x => x.ReferencedTable.Equals(FromTable.Name) && x.ReferencedSchema.Equals(FromTable.Schema) && x.ColumnReferences.Any(y => y.ReferencedColumn.Equals(FromField))).ToList();
if (Fks.Count != 1)
{
throw new InvalidOperationException(string.Format("The column '{0}' is referenced by {1} keys in the table {2}. Expected 1. Make the join manually",
FromField.Name, Fks.Count, ToTable.Fullname));
}
FK = Fks.First();
j = SqlStatementExtensions.MakeJoin(JoinType, FromSqlTable, ToTable.Name, null, ToTable.Schema.Equals("dbo") ? null : ToTable.Schema);
mcr = FK.ColumnReferences.First();
jcg = j.On(FromField.Name, SqlOperators.Equal, mcr.Name);
return(jcg);
}
}
if (FromField.IsForeignKey)
{
Fks = new List <MetadataForeignKey>(FromTable.FindForeignKeys(FromField, ToTable.Name));
if (Fks.Count != 1)
{
throw new InvalidOperationException(string.Format("The column '{0}' resolves to {1} keys in the table {2}. Expected 1. Make the join manually",
FromField.Name, Fks.Count, ToTable.Fullname));
}
FK = Fks.First();
j = SqlStatementExtensions.MakeJoin(JoinType, FromSqlTable, ToTable.Name, null, ToTable.Schema.Equals("dbo") ? null : ToTable.Schema);
mcr = FK.ColumnReferences.First();
jcg = j.On(FromField.Name, SqlOperators.Equal, mcr.ReferencedColumn.Name);
if (FK.ColumnReferences.Count > 1)
{
foreach (MetadataColumnReference mcr2 in FK.ColumnReferences.Skip(1))
{
if (mcr2.Name.StartsWith("\""))
{
// its a value reference
// jcg.And(FK.ReferencedTable, mcr2.ReferencedColumn.Name, SqlOperators.Equal, mcr2.Name.Trim('\"'),null);
decimal d;
object o;
if (decimal.TryParse(mcr2.Name.Trim('\"'), out d))
{
o = d;
}
else
{
o = (object)mcr2.Name.Trim('\"');
}
jcg.And(mcr2.ReferencedColumn.Name, SqlOperators.Equal, o, null);
}
else
{
jcg.And(mcr2.Column.Name, SqlOperators.Equal, mcr2.ReferencedColumn.Name);
}
}
}
return(jcg);
}
throw new ArgumentException(string.Format("The Column '{0}' in the table '{1}' must be a foreign key or primary key", FromField.Name, FromTable.Fullname), "FromField");
}
