private void RemoveUnwantedColumns(XRoot root)
{
using (var dc = new LinqToSqlSchema.SqlSchemaDataContext(Common.Properties.ConnectionString))
{
var columnsJoinedToExtendedProperties =
from t in dc.tables
join c in dc.columns on t.object_id equals c.object_id
join ep in dc.extended_properties on c.object_id equals ep.major_id
where ep.name == "MS_Description" && ep.minor_id == c.column_id && (string) (ep.value ?? "") != ""
select new {ColumnName = t.name + "." + c.name};
var columnsWithoutExtendedProperties =
from t in dc.tables
join c in dc.columns on t.object_id equals c.object_id
where !columnsJoinedToExtendedProperties.Any(x => x.ColumnName == t.name + "." + c.name)
select new {TableName = t.name, ColumnName = c.name};
foreach (var column in columnsWithoutExtendedProperties)
{
root.Table(column.TableName).Type.Column.Remove(
root.Table(column.TableName).Type.Column(column.ColumnName));
}
}
//using (var dc = new LinqToSqlSchema.SqlSchemaDataContext(Common.Properties.ConnectionString))
//{
// var columns =
// (from t in dc.tables
// join c in dc.columns on t.object_id equals c.object_id
// join ep in dc.extended_properties on c.object_id equals ep.major_id
// where ep.name == "MS_Description" && ep.minor_id == c.column_id && (string)(ep.value ?? "") == ""
// select new { TableName = t.name, ColumnName = c.name }).ToArray();
//
//}
}
private void RemoveUnwantedTables(XRoot root)
{
List<string> tablesWithoutMsExtendedProperties = null;
using (var dc = new LinqToSqlSchema.SqlSchemaDataContext(Common.Properties.ConnectionString))
{
var tablesJoinedToExtendedProperties =
from t in dc.tables
join ep in dc.extended_properties on t.object_id equals ep.major_id
where ep.name == "MS_Description" && ep.minor_id == 0 && (string)(ep.value ?? "") != ""
select t.name;
var tablesWithoutExtendedProperties =
from t in dc.tables
where !tablesJoinedToExtendedProperties.Contains(t.name)
select t.name;
tablesWithoutMsExtendedProperties = tablesWithoutExtendedProperties.ToList();
}
foreach (string tableName in tablesWithoutMsExtendedProperties)
{
root.Database.Table.Remove(root.Table(tableName));
}
}
private void OverrideTypesWithEnumProperties(XRoot root)
{
using (var dc = new LinqToSqlSchema.SqlSchemaDataContext(Common.Properties.ConnectionString))
{
var enumProps =
(from t in dc.tables
join c in dc.columns on t.object_id equals c.object_id
join ep in dc.extended_properties on c.object_id equals ep.major_id
where ep.name == "EnumProperty" && ep.minor_id == c.column_id && (string)(ep.value ?? "") != ""
select new { TableName = t.name, ColumnName = c.name, EnumProperty = (string)ep.value }).ToArray();
foreach (var enumProp in enumProps)
{
root.Table(enumProp.TableName).Type.Column(enumProp.ColumnName).Type = enumProp.EnumProperty;
}
}
}
private void SetNotNullColumnsForIsNotNullProperty(XRoot root)
{
using (var dc = new LinqToSqlSchema.SqlSchemaDataContext(Common.Properties.ConnectionString))
{
var notNullColumns =
(from t in dc.tables
join c in dc.columns on t.object_id equals c.object_id
join ep in dc.extended_properties on c.object_id equals ep.major_id
where ep.name == "IsNotNull" && ep.minor_id == c.column_id && (string)(ep.value ?? "") == "true"
select new { TableName = t.name, ColumnName = c.name }).ToArray();
foreach (var notNullColumn in notNullColumns)
{
root.Table(notNullColumn.TableName).Type.Column(notNullColumn.ColumnName).CanBeNull = false;
}
}
}