public static SqlBuilder ToSqlBuilder(this MetadataColumn ForeignKeyColumn)
{
MetadataForeignKey FK = ForeignKeyColumn.Parent.FindForeignKeys(ForeignKeyColumn).First();
MetadataTable PK = SqlBuilder.DefaultMetadata.FindTable(FK.ReferencedSchema + "." + FK.ReferencedTable);
string namecol = PK.GuessTitleColumn();
string[] valuecols = FK.ColumnReferences.Where(x => !x.Column.IsComputed).Select(x => x.ReferencedColumn.Name).ToArray();
SqlBuilder Builder = SqlBuilder.Select()
.From(PK.Name, null, PK.Schema)
.Column(namecol)
.Columns(valuecols)
.Builder();
List <MetadataColumnReference> mcrs = FK.ColumnReferences.Where(x => x.Column.IsComputed).ToList();
if (mcrs.Count > 0)
{
MetadataColumnReference first = mcrs.First();
Builder.From(PK.Name, null, PK.Schema)
.Where(PK.Name, first.ReferencedColumn.Name, SqlOperators.Equal, (object)first.Column.Name.Trim('\"'))
.Builder();
foreach (MetadataColumnReference mcr in mcrs.Skip(1))
{
Builder.From(PK.Name, null, PK.Schema)
.Where(PK.Name, mcr.ReferencedColumn.Name, SqlOperators.Equal, (object)mcr.Column.Name.Trim('\"'))
.Builder();
}
}
return(Builder);
}
private void BuildVirtualKeys(List<VirtualForeignKey> keys, MetadataTable mt, MetadataDatabase mdb, bool PrimaryKeyIndexOnly)
{
foreach (VirtualForeignKey vfk in keys)
{
MetadataForeignKey mfk = new MetadataForeignKey()
{
ID = 0,
Name = vfk.values[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)[0],
ReferencedKey = "",
ReferencedTable = vfk.values[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)[1],
ReferencedSchema = vfk.values[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)[2],
Parent = mt,
IsVirtual = true
};
MetadataTable mtref = null;
if (!mdb.Tables.TryGetValue(mfk.ReferencedSchema + "." + mfk.ReferencedTable, out mtref))
{
bool self = false;
if (mfk.ReferencedSchema == mt.Schema && mfk.ReferencedTable == mt.Name)
{
self = true;
}
mtref = BuildMetadata(mdb, mfk.ReferencedTable, mfk.ReferencedSchema, PrimaryKeyIndexOnly, self);
}
for (int i = 1; i < vfk.values.Length; i++)
{
MetadataColumnReference mcf = new MetadataColumnReference()
{
ReferencedColumn = mtref[vfk.values[i].Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries)[1]],
};
string from = vfk.values[i].Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries)[0];
if (from.StartsWith("\""))
{
MetadataColumn mcVirtual = new MetadataColumn()
{
Name = from,
IsForeignKey = true,
ID = 0,
SqlDataType = SqlDbType.NVarChar,
Nullable = false,
Length = 0,
IsComputed = true,
DataType = typeof(string),
};
mcf.Column = mcVirtual;
mcf.Name = from;
}
else
{
mcf.Column = mt[from];
mcf.Name = mcf.Column.Name;
}
mfk.ColumnReferences.Add(mcf);
}
mt.ForeignKeys.AddOrUpdate(mfk.Name, mfk, (k, v) => { return mfk; });
}
}
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");
}
