private Tuple <MetadataWorkspace, SchemaData> LoadEdmx(string path)
{
try
{
string spath = Path.GetFullPath(path);
XElement runtime = XElement.Load(path).Elements().First(e => e.Name.LocalName == "Runtime");
XElement csdlSchema = runtime.Elements().First(e => e.Name.LocalName == "ConceptualModels")
.Elements().First(e => e.Name.LocalName == "Schema")
.Elements().First(e => e.Name.LocalName == "EntityContainer")
;
string entityName = csdlSchema.Attributes().First(e => e.Name.LocalName == "Name").Value;
EdmItemCollection edmCollection;
XElement csdl = runtime.Elements().First(e => e.Name.LocalName == "ConceptualModels")
.Elements().First(e => e.Name.LocalName == "Schema");
using (XmlReader reader = csdl.CreateReader())
{
edmCollection = new EdmItemCollection(new[] { reader });
}
StoreItemCollection storeCollection;
XElement ssdl = runtime.Elements().First(e => e.Name.LocalName == "StorageModels")
.Elements().First(e => e.Name.LocalName == "Schema");
using (XmlReader reader = ssdl.CreateReader())
{
storeCollection = new StoreItemCollection(new[] { reader });
}
SchemaData schema = new SchemaData()
{
Name = entityName
};
foreach (XNode nod in ssdl.Nodes())
{
if (nod.NodeType == XmlNodeType.Element)
{
XElement nodEle = (XElement)nod;
if (nodEle.Name.LocalName == "EntityType")
{
CodeGen.EntityType entityType = new CodeGen.EntityType()
{
Name = nodEle.Attribute("Name").Value
};
if (entityType.Name.StartsWith("VwCustomers"))
{
entityType.Name = entityType.Name + "";
}
List <string> primaryKeyList = new List <string>();
foreach (XNode member in ((XElement)nod).Nodes())
{
if (member.NodeType == XmlNodeType.Element)
{
XElement nodMember = (XElement)member;
if (nodMember.Name.LocalName == "Property")
{
Property property = new Property()
{
IsNullable = true
};
property.Name = nodMember.Attribute("Name").Value ?? "";
property.Type = nodMember.Attribute("Type").Value ?? "";
if (nodMember.Attribute("MaxLength") != null)
{
property.MaxLength = int.Parse(nodMember.Attribute("MaxLength").Value);
}
if (nodMember.Attribute("Nullable") != null)
{
property.IsNullable = bool.Parse(nodMember.Attribute("Nullable").Value);
}
if (nodMember.Attribute("Precision") != null)
{
property.Precision = int.Parse(nodMember.Attribute("Precision").Value);
}
if (nodMember.Attribute("Scale") != null)
{
property.Scale = int.Parse(nodMember.Attribute("Scale").Value);
}
if ((nodMember.Attribute("StoreGeneratedPattern") != null) && (nodMember.Attribute("StoreGeneratedPattern").Equals("Identity")))
{
property.IsIdentity = true;
}
entityType.Properties.Add(property.Name, property);
}
else if (nodMember.Name.LocalName == "Key")
{
foreach (XNode nodKey in ((XElement)nodMember).Nodes())
{
XElement eleKey = (XElement)nodKey;
string sKey = eleKey.Attribute("Name").Value ?? "";
primaryKeyList.Add(sKey);
}
}
}
}
foreach (string primaryKeyName in primaryKeyList)
{
if (entityType.Properties.ContainsKey(primaryKeyName))
{
entityType.Properties[primaryKeyName].IsKey = true;
entityType.PrimaryKeys.Add(entityType.Properties[primaryKeyName]);
}
}
schema.Add(entityType.Name, entityType);
}
}
}
StorageMappingItemCollection mappingCollection;
XElement msl = runtime.Elements().First(e => e.Name.LocalName == "Mappings")
.Elements().First(e => e.Name.LocalName == "Mapping");
using (XmlReader reader = msl.CreateReader())
{
mappingCollection = new StorageMappingItemCollection(
edmCollection,
storeCollection,
new[] { reader });
}
MetadataWorkspace mdRet = new MetadataWorkspace(() => edmCollection, () => storeCollection, () => mappingCollection);
//Resolve foriegn keys using MetadataWorkspace
var relationshipEndMembers = new Dictionary <RelationshipEndMember, Tuple <System.Data.Entity.Core.Metadata.Edm.EntityType, NavigationProperty> >();
var entitySets = mdRet.GetItems <EntityContainer>(DataSpace.CSpace).First().BaseEntitySets;
foreach (var s in entitySets)
{
try
{
if ((s.BuiltInTypeKind == BuiltInTypeKind.EntitySet) || (s.BuiltInTypeKind == BuiltInTypeKind.EntityType) || (s.BuiltInTypeKind == BuiltInTypeKind.EntitySetBase))
{
if (s.Name.StartsWith("VwCustomers"))
{
var Name = (s.Name + " ").Trim();
}
var navProperties = ((System.Data.Entity.Core.Metadata.Edm.EntityType)s.ElementType).DeclaredNavigationProperties;
if ((navProperties.Count > 0) && (schema.ContainsKey(s.Name)))
{
foreach (var navProperty in navProperties)
{
//Thank you http://stackoverflow.com/questions/5365708/ef4-get-the-linked-column-names-from-navigationproperty-of-an-edmx
AssociationType association = mdRet.GetItems <AssociationType>(DataSpace.CSpace).Single(a => a.Name == navProperty.RelationshipType.Name);
string fromEntity = association.ReferentialConstraints[0].FromRole.Name;
string fromEntityField = association.ReferentialConstraints[0].FromProperties[0].Name;
string toEntity = association.ReferentialConstraints[0].ToRole.Name;
string toEntityField = association.ReferentialConstraints[0].ToProperties[0].Name;
string toEntityColumnName = toEntityField.Replace("Id", "").Replace("UID", "");
string fromEntityColumnName = fromEntityField.Replace("Id", "").Replace("UID", "");
var ns = new XmlNamespaceManager(new NameTable());
ns.AddNamespace("edmx", "http://schemas.microsoft.com/ado/2009/11/edm");
var r = entitySets.First(a => a.Name == navProperty.RelationshipType.Name);
//Figure out the real entity names based off the end keys
var ends = ((System.Data.Entity.Core.Metadata.Edm.AssociationSet)r).AssociationSetEnds;
if (ends != null)
{
var fromEnd = ends.First(e => e.Name == fromEntity);
if (fromEnd != null)
{
fromEntity = fromEnd.EntitySet.Name;
}
var toEnd = ends.First(e => e.Name == toEntity);
if (toEnd != null)
{
toEntity = toEnd.EntitySet.Name;
}
}
var newRel = new Relationship()
{
Name = navProperty.RelationshipType.Name, FromTableName = fromEntity, FromFieldName = fromEntityField, ToFieldName = toEntityField,
ToTableName = toEntity, ToColumnName = toEntityColumnName,
Type = navProperty.FromEndMember.RelationshipMultiplicity.ToString() + " to " + navProperty.ToEndMember.RelationshipMultiplicity.ToString()
};
schema[s.Name].Relationships.Add(newRel);
var fieldToMarkRelation = (s.Name.Equals(newRel.FromTableName) ? newRel.FromFieldName : newRel.ToFieldName);
if (schema[s.Name].Properties.ContainsKey(fieldToMarkRelation))
{
schema[s.Name].Properties[fieldToMarkRelation].RelatedTo.Add(newRel);
}
}
}
}
}
catch (Exception exE)
{
Console.WriteLine(exE.Message);
throw;
}
}
//Go through Edmx and get the type of object of each entity. Important because we do different things when we mess with views
XElement xeele = runtime.Elements().First(e => e.Name.LocalName == "StorageModels")
.Elements().First(e => e.Name.LocalName == "Schema")
.Elements().First(e => e.Name.LocalName == "EntityContainer");
foreach (var item in xeele.Nodes())
{
XElement ele = (XElement)item;
if (ele.Attribute("Name") == null)
{
Console.WriteLine("Error: could not read Name Attribute");
}
var name = ele.Attribute("Name").Value;
var schemaName = "";
if (ele.Attribute("Schema") != null)
{
schemaName = ele.Attribute("Schema").Value;
}
XNamespace w = "http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator";
var entityType = ele.Attribute(w + "Type");
if (entityType != null)
{
if (schema.ContainsKey(name))
{
schema[name].Type = entityType.Value.ToSingular();
schema[name].Schema = schemaName;
}
}
}
return(new Tuple <MetadataWorkspace, SchemaData>(mdRet, schema));
}
catch (Exception ex)
{
Console.Write(ex.Message);
throw;
}
}
public static SchemaData GenerateSchemaObjects(string entityName, string ConnectionString)
{
try
{
SchemaData schema = new SchemaData()
{
Name = entityName
};
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlCommand cmdCompat11 = new SqlCommand("DECLARE @sql NVARCHAR(1000) = 'ALTER DATABASE ' + DB_NAME() + ' SET COMPATIBILITY_LEVEL = 110'; EXECUTE sp_executesql @sql", connection);
cmdCompat11.CommandType = CommandType.Text;
cmdCompat11.ExecuteNonQuery();
using (SqlCommand cmd = new SqlCommand(FKSQL, connection))
{
cmd.CommandType = CommandType.Text;
var ds = new DataSet();
var adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
var tableColumnData = ds.Tables[0];
var CurrentEntityName = "";
EntityType entityType = null;
PrimaryKeyProperties primaryKeyList = null;
foreach (DataRow row in tableColumnData.Rows)
{
var tableName = row["TABLENAME"].ToString();
if (CurrentEntityName != tableName)
{
if (CurrentEntityName.Length > 0)
{
//Temporal views are handled below
if ((!entityType.HasPrimaryKeys() && (!entityType.IsTemporalView) && (entityType.TemporalType != "HISTORY_TABLE")))
{
Console.WriteLine("Warning... '" + CurrentEntityName + "' no primary keys.. adding all as primary keys");
int order = 0;
foreach (var prop in entityType.Properties.Values)
{
order++;
prop.IsKey = true;
prop.KeyOrder = order;
entityType.PrimaryKeys.Add(prop);
}
}
schema.Add(CurrentEntityName, entityType);
}
entityType = new EntityType()
{
Name = tableName
, Type = row["OBJECT_TYPE"].ToString()
, Schema = row["SCHEMANAME"].ToString()
, IsTemporalView = ((tableName.EndsWith("TemporalView")) && (row["OBJECT_TYPE"].ToString() == "VIEW"))
, TemporalType = (row["TEMPORAL_TYPE_DESC"] == DBNull.Value ? "" : row["TEMPORAL_TYPE_DESC"].ToString())
};
primaryKeyList = new PrimaryKeyProperties(entityType);
entityType.PrimaryKeys = primaryKeyList;
CurrentEntityName = tableName;
}
Property property = new Property()
{
IsNullable = (bool)row["IS_NULLABLE"]
};
property.Name = (row["COLUMNNAME"] == DBNull.Value ? "" : row["COLUMNNAME"].ToString());
property.MaxLength = (int)(row["CHARACTER_MAXIMUM_LENGTH"] == DBNull.Value ? 0 : row["CHARACTER_MAXIMUM_LENGTH"]);
property.Precision = (int)(row["NUMERIC_PRECISION"] == DBNull.Value ? 0 : Convert.ToInt32(row["NUMERIC_PRECISION"]));
property.Scale = (int)(row["NUMERIC_SCALE"] == DBNull.Value ? 0 : Convert.ToInt32(row["NUMERIC_SCALE"]));
property.IsIdentity = (bool)(row["IS_IDENTITY"] == DBNull.Value ? false : row["IS_IDENTITY"]);
property.IsKey = (bool)(row["PRIMARY_KEY_ORDER"] == DBNull.Value ? false : true);
property.KeyOrder = (int)(row["PRIMARY_KEY_ORDER"] == DBNull.Value ? 0 : Convert.ToInt32(row["PRIMARY_KEY_ORDER"]));
if ((entityType.IsTemporalView) && ((property.Name == "SysEndTime") || (property.Name == "SysStartTime")))
{
property.IsKey = true;
property.KeyOrder = ((property.Name == "SysStartTime") ? 1 : 2);
}
property.Type = (row["DATA_TYPE"] == DBNull.Value ? "" : row["DATA_TYPE"].ToString());
if (property.IsKey)
{
entityType.PrimaryKeys.Add(property);
}
entityType.Properties.Add(property.Name, property);
}
if (CurrentEntityName.Length > 0)
{
//Temporal views are handled abolve
if ((!entityType.HasPrimaryKeys() && (!entityType.IsTemporalView) && (entityType.TemporalType != "HISTORY_TABLE")))
{
Console.Write("Warning... '" + CurrentEntityName + "' no primary keys.. adding all as primary keys");
int order = 0;
foreach (var prop in entityType.Properties.Values)
{
order++;
prop.IsKey = true;
prop.KeyOrder = order;
entityType.PrimaryKeys.Add(prop);
}
}
schema.Add(CurrentEntityName, entityType);
}
var tableFKeyData = ds.Tables[1];
foreach (DataRow row in tableFKeyData.Rows)
{
var tableName = row["EntityTable"].ToString();
string fromEntity = (row["EntityTable"] == DBNull.Value ? "" : row["EntityTable"].ToString());
string fromEntityField = (row["EntityColumn"] == DBNull.Value ? "" : row["EntityColumn"].ToString());
string toEntity = (row["RelatedTable"] == DBNull.Value ? "" : row["RelatedTable"].ToString());
string toEntityField = (row["RelatedColumn"] == DBNull.Value ? "" : row["RelatedColumn"].ToString());
string toEntityColumnName = toEntityField.AsFormattedName();
string fromEntityColumnName = fromEntityField.AsFormattedName();
string multiplicity = (row["Multiplicity"] == DBNull.Value ? "" : row["Multiplicity"].ToString());
var newRel = new Relationship()
{
Name = row["FK_Name"] == DBNull.Value ? "" : row["FK_Name"].ToString(),
FromTableName = fromEntity,
FromFieldName = fromEntityField,
FromColumnName = fromEntityColumnName,
ToFieldName = toEntityField,
ToTableName = toEntity,
ToColumnName = toEntityColumnName,
Type = multiplicity
};
schema[tableName].Relationships.Add(newRel);
var fieldToMarkRelation = (tableName.Equals(newRel.FromTableName) ? newRel.FromFieldName : newRel.ToFieldName);
if (schema[tableName].Properties.ContainsKey(fieldToMarkRelation))
{
schema[tableName].Properties[fieldToMarkRelation].RelatedTo.Add(newRel);
}
}
}
if (schema.ContainsKey("sysdiagrams"))
{
schema.Entities.Remove("sysdiagrams");
}
if (schema.ContainsKey("DeploymentMetadata"))
{
schema.Entities.Remove("DeploymentMetadata");
}
//REMOVE TEMPORAL TABLES
foreach (var t in schema.Keys.Where(k => k.EndsWith("Temporal", StringComparison.OrdinalIgnoreCase)).ToArray())
{
schema.Entities.Remove(t);
}
foreach (var t in schema.Keys.Where(k => k.ToLower().Contains("sysdiagram")).ToArray())
{
schema.Entities.Remove(t);
}
foreach (var t in schema.Keys.Where(k => k.Contains("TemporalHistoryFor")).ToArray())
{
schema.Entities.Remove(t);
}
//REMOVE Redgate tables
foreach (var t in schema.Keys.Where(k => k.EndsWith("DeploymentMetadata", StringComparison.OrdinalIgnoreCase)).ToArray())
{
schema.Entities.Remove(t);
}
var temporal = schema.Keys.Where(k => k.EndsWith("_Archive", StringComparison.OrdinalIgnoreCase)).ToArray();
foreach (var t in temporal)
{
schema.Entities.Remove(t);
}
SqlCommand cmdCompat13 = new SqlCommand("DECLARE @sql NVARCHAR(1000) = 'ALTER DATABASE ' + DB_NAME() + ' SET COMPATIBILITY_LEVEL = 130'; EXECUTE sp_executesql @sql", connection);
cmdCompat13.CommandType = CommandType.Text;
cmdCompat13.ExecuteNonQuery();
return(schema);
}
}
catch (Exception)
{
throw;
}
}