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"); }
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()); }