Entity Framework: Sorting EntitySets According to parent-child relationship using Context properties
Some month ago while I was working with Entity Framework V1 in data access layer, Client ask me to make a backup of the db from a generic place. Because client wants a flexibility to use any db storage(MS Sql server ,MySql, may be VistaDB ) with same schema. So I need to make a backup system from a generic place. So what I did (I don’t know it was a good idea or not) was that, I serialized the all the EntitySet graphs and store into some files and then compressed and encrypt them in a single file. So, when a user wants to restore the DB , I need to decrypt and de-serialized all the entity objects and attach or add the DB . To attach or Add I have to maintain the order of parent child relationship. So When I store in into File(what I say as Backup) I follow the parent child relationship of the Entity set and maintain the order like – Root First and then The child. During restore I also have to do the reverse way. I mean to say I have attch or add the child first and then the parent. So to do that I have sort the entity set in a parent Child relationship. To make this, first I make a dictionary where I find the references of each entity set. So the key was the Type entity set and value is List of Type of Entity Set those are references the the key Entity set.
/// <summary> /// Get Current Relationship Dictionary of Entites /// </summary> /// <returns>Dictionary of Current Entites</returns> public static Dictionary<Type, List<Type>> GetCurrentDBEntityRelationshipChildren<T>() where T : ObjectContex { try { //Take a dictionary Dictionary<Type, List<Type>> objectCollection = new Dictionary<Type, List<Type>>(); //Get al properties contex PropertyInfo[] properties = typeof(T).GetProperties(); foreach (var item in properties) {//check //Pickup the properties only those represent entitys of database if (item.PropertyType.BaseType.Equals(typeof(ObjectQuery))) { //Get Propety value Type entityType = item.PropertyType.GetGenericArguments().First(); ; if (null != entityType) { try { AddToDictionary(objectCollection, entityType, null); PropertyInfo[] entityProperties = entityType.GetProperties(); foreach (var entityProp in entityProperties) { if (entityProp.PropertyType.BaseType.Equals(typeof(RelatedEnd))) //Collection Property { #region Detail Reference - Add on "* to 1" relationship Type detail = entityProp.PropertyType.GetGenericArguments().First(); if (null != detail) { AddToDictionary(objectCollection, entityType, detail); }//end if #endregion } else if (entityProp.PropertyType.BaseType.Equals(typeof(EntityReference))) { #region Master referance - Add on "1 to 1" relationship bool addInDictionary = false; Type master = entityProp.PropertyType.GetGenericArguments().First(); if (null != master) { try { IEnumerable<PropertyInfo> props = master.GetProperties().Where(p => p.PropertyType.BaseType.Equals(typeof(EntityReference))); if (null != props && props.Count() > 0) { foreach (var p in props) { if (p.PropertyType.GetGenericArguments().FirstOrDefault().Equals(entityType) && p.PropertyType.BaseType.Equals(typeof(EntityReference)) && !IsInDictionary(objectCollection, master, entityType)) { addInDictionary = true; } } } } catch (System.Reflection.TargetException ex) { throw ErrorHandling.ErrorHandler.WrapException(ex, ErrorCodes.DB_EntityRelationship_Error_Code); } catch (TargetInvocationException ex) { throw ErrorHandling.ErrorHandler.WrapException(ex, ErrorCodes.DB_EntityRelationship_Error_Code); } if (addInDictionary) { AddToDictionary(objectCollection, entityType, master); } }//end if #endregion } } }//end try catch (ReflectionTypeLoadException ex) { throw ErrorHandling.ErrorHandler.WrapException(ex, ErrorCodes.DB_EntityRelationship_Error_Code); } catch (EntityException ex) { throw ErrorHandling.ErrorHandler.WrapException(ex, ErrorCodes.DB_EntityRelationship_Error_Code); } }//end if }//end if }//end foreach //return the dictionary od priority list return objectCollection; } catch (Exception ex) { throw ErrorHandling.ErrorHandler.WrapException(ex, ErrorCodes.DB_EntityRelationship_Error_Code); } } #region Private Methods /// <summary> /// Add Value into into Dictionary on checking old data /// </summary> /// <param name="Dictionary">Dictionary</param> /// <param name="Key">Key</param> /// <param name="Value">Value</param> private static void AddToDictionary(Dictionary<Type, List<Type>> Dictionary, Type Key, Type Value) { if (Dictionary.ContainsKey(Key)) { List<Type> prevValue = Dictionary[Key] as List<Type>; if (prevValue != null && null != Value) { prevValue.Add(Value); Dictionary[Key] = prevValue; }//end if } else {//add new entry with default priority if (null != Value) { Dictionary.Add(Key, new List<Type>() { Value }); } else { Dictionary.Add(Key, new List<Type>()); } }//end else } /// <summary> /// Is in dictionary ? /// </summary> /// <param name="Dictionary"></param> /// <param name="key"></param> /// <param name="value"></param> /// <returns></returns> private static bool IsInDictionary(Dictionary<Type, List<Type>> Dictionary, Type key, Type value) { if (Dictionary.ContainsKey(key)) { foreach (var item in (Dictionary[key] as List<Type>)) { if (item.Equals(value)) { return true; } } } return false; }
Now I was going to Sort this Using an Sorting algorithm , To make that First I declared a enum –
[Flags] public enum EnitySetRefType { None = 0x00, ReferencedBy = 0x01, RefAndRefBy = 0x02, References = 0x04 }
And make a Wrapper class Of EntitySet Like this –
/// <summary> /// Wrapper class of EntitySet /// </summary> public class EntitySetTypeWrapper { #region Data Members // member variables public static int MaxLen; public static readonly SortedList<string, EntitySetTypeWrapper> sortedList = new SortedList<string, EntitySetTypeWrapper>(); // Instance variables private List<string> _referencedBy = new List<string>(); private EnitySetRefType _refType; private string _name; #endregion #region Properites /// <summary> /// Get/Set for ReferencedBy /// </summary> public List<string> ReferencedBy { get { return (_referencedBy); } set { _referencedBy = value; } } /// <summary> /// Get/Set for RefType /// </summary> public EnitySetRefType RefType { get { return (_refType); } set { _refType = value; } } /// <summary> /// Get/Set for Name /// </summary> public string Name { get { return (_name); } set { _name = value; } } #endregion #region Constructor /// <summary> /// /// </summary> /// <param name="row"></param> private EntitySetTypeWrapper(string _name) { Name = _name; if (Name.Length > MaxLen) MaxLen = Name.Length; AddRefType(EnitySetRefType.None); } #endregion #region Methods - Private /// <summary> /// Private Method /// </summary> /// <param name="referenceType"></param> private void AddRefType(EnitySetRefType referenceType) { if (_refType == EnitySetRefType.RefAndRefBy) return; _refType |= referenceType; if (_refType == (EnitySetRefType.ReferencedBy | EnitySetRefType.References)) _refType = EnitySetRefType.RefAndRefBy; } #endregion }
Now What I have to do , I should make a Comparer class to sort the entity set Using this wrapper class . So I make a class named EntityInfoComparer which is inherited from IComparer<EntitySetTypeWrapper>
public class EntityInfoComparer : IComparer<EntitySetTypeWrapper> { /// <summary> /// Compare Two Entity /// </summary> /// <param name="x"></param> /// <param name="y"></param> /// <returns></returns> public int Compare(EntitySetTypeWrapper x, EntitySetTypeWrapper y) { // Invalid Data if ((x == null) || (y == null)) throw new ArgumentException("Invalid data type"); // Objects are the Same if (x == y) { return (0); } // Sort by Category Number - Object X is in a LOWER Categroy than Object Y if (x.RefType < y.RefType) return (-1); // Sort by Category Number - Object X is in a HIGHER Categroy than Object Y if (x.RefType > y.RefType) return (1); // Sort by Category Number - Object X and Object Y are in the SAME Category // Is this a Category 3 if (x.RefType == EnitySetRefType.RefAndRefBy) { if (IsInReferenceList(y.Name, x)) { return (-1); } if (IsInReferenceList(x.Name, y)) { return (1); } } // As a last resort sort by name return CompareName(x, y); } /// <summary> /// Compare Two Entity /// </summary> /// <param name="x">First Entity Defination</param> /// <param name="y">Second Entity Defination</param> /// <returns></returns> private static int CompareName(EntitySetTypeWrapper x, EntitySetTypeWrapper y) { return (x.Name.CompareTo(y.Name)); } /// <summary> /// Is in Reference List? /// </summary> /// <param name="entityName">Name of Entity</param> /// <param name="entityInfo">Entity Defination</param> /// <returns></returns> private static bool IsInReferenceList(string entityName, EntitySetTypeWrapper entityInfo) { // Nothing to compare if (entityInfo.ReferencedBy.Count == 0) return (false); // This Entity is Referenced By Entity if (entityInfo.ReferencedBy.Contains(entityName)) return (true); // Recursive call foreach (string childEntityName in entityInfo.ReferencedBy) { // Looking for a child of this entity if (IsInReferenceList(entityName, EntitySetTypeWrapper.sortedList[childEntityName])) return (true); } return (false); } }
So , Going to sort using the above dictionary and wrapper class of entity set.So I am going to write an static mathod who First make a dictionary using the method GetCurrentDBEntityRelationshipChildren and then make a SortedList<string, EntitySetTypeWrapper> using the dictionary contents and at the end I sort the SortedList using EntityInfoComparer.
/// <summary> /// Create a sorted EntitySet list /// </summary> /// <returns></returns> public static List<EntitySetTypeWrapper> CreateSortedEntityListOnChildFirst<T>() where T : ObjectContext { // Retrieve Tables and Foreign Key Relationships //DataSet dataSet = GetDatabaseTableInformation(); Dictionary<Type, List<Type>> entries = GenericDataAccessHelper.GetCurrentDBEntityRelationshipChildren<T>(); sortedList.Clear(); // Add All Tables to a List foreach (Type key in entries.Keys) { EntitySetTypeWrapper tableInfo = new EntitySetTypeWrapper(key.Name); sortedList.Add(tableInfo.Name, tableInfo); } // Find Reference Table Information For Each Table foreach (var item in entries) { EntitySetTypeWrapper entityInfo = sortedList[(item.Key as Type).Name]; List<Type> valueRange = item.Value as List<Type>; if (null != valueRange && valueRange.Count > 0 && entityInfo != null) { foreach (var value in valueRange) { string childTable = value.Name; if (childTable.Equals(entityInfo.Name)) continue; // Add Child Table to Current Table if (!entityInfo.ReferencedBy.Contains(childTable)) { entityInfo.ReferencedBy.Add(childTable); // Add Reference Type Enumeration For Both Referenced and Referenced By Tables entityInfo.AddRefType(EnitySetRefType.ReferencedBy); sortedList[childTable].AddRefType(EnitySetRefType.References); } }//end foreach } // Sort Referenced By Tables By Name entityInfo.ReferencedBy.Sort(); } // Perform the Sort with the Custom Sorter List<EntitySetTypeWrapper> list = new List<EntitySetTypeWrapper>(sortedList.Values); list.Sort(new EntityInfoComparer()); return (list); }
That’s the end of story here…Still its working good with the Domain models of Entity set . Hope it will help you also. Feel free if you wanna comment on this.
Senior Software Engineer, Adaptive Enterprise Limited. Dhaka , Bangladesh
Morshed Anwar's Blog 
Leave a comment