public static SqlBuilder ToSqlBuilder(this MetadataTable table, string ListName)
{
List <string> columns = new List <string>(table.PrimaryKey.Columns.Select(x => x.Name));
List <string> columnDef = null;
List <MetadataColumn> mcs = new List <MetadataColumn>();
if (table.ListDefinitions.TryGetValue(ListName, out columnDef))
{
mcs = table.Columns.Values.Where(x => columnDef.Contains(x.Name)).ToList();
}
else
{
mcs = table.Columns.Values.Where(x => x.IsRowGuid == false && x.IsPrimaryKey == false).ToList();
}
columns.AddRange(mcs.Select(x => x.Name));
SqlBuilder Builder = SqlBuilder.Select()
.From(table.Name, null, table.Schema).Builder();
foreach (MetadataColumn mc in mcs.Where(x => x.IsForeignKey))
{
Builder.BaseTable().WithMetadata().AutoJoin(mc.Name);
}
Builder.From(table.Name, null, table.Schema).Columns(columns.ToArray());
return(Builder);
}
public static SqlBuilder Select(Type ObjectType, string TableName = null, string[] Properties = null, string[] ExcludeProperties = null, int?Top = null, bool Distinct = false)
{
SqlBuilder builder = SqlBuilder.Select(Top, Distinct);
Table BaseTable = null;
if (string.IsNullOrEmpty(TableName))
{
BaseTable = builder.From(ObjectType.Name);
}
else
{
BaseTable = builder.From(TableName);
}
if (Properties == null)
{
Properties = ObjectType.GetProperties().Select(x => x.Name).Union(ObjectType.GetFields().Select(x => x.Name)).ToArray();
}
if (ExcludeProperties == null)
{
ExcludeProperties = new string[0];
}
foreach (string Name in Properties.Except(ExcludeProperties))
{
if (Name.Equals("*"))
{
BaseTable.AllColumns(false);
return(builder);
}
BaseTable.Column(Name);
}
return(builder);
}
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);
}
