1
\$\begingroup\$

This is Part 2 of a question about performance and general best practices. Part 1 is located here. The body of the messages are the same to illustrate their similarity, the difference is the code provided. One provides the logic the to generate the code the other provides the data to utilized by the template. The focus isn't so much on the template, but the pattern of managing a database that tracks changes across potentially multiple domains.

I'm kind of new to the realm of Database authoring, so I wanted to get some feedback on a system which generates auditable entities.

I'm using Visual Studio 2013 with the Entity Framework 6.0.2. Handling Migrations through the Package Manager Console. I'm rather unfamiliar with EF Performance, and what such a change tracking system would do. I know that I could enable the change tracking on the server, but this system will involve tracking who changed what, based off of their identity defined within the database (and honestly, I don't understand the SQL server change tracking one bit.) It's meant to focus on potentially multiple domains, so whoever would use the client would login using SQL Authentication. The end result is Active Directory supplies the authentication, and users are handled by storing their SID within the registry which links to the proper domain.

Here's the Text Template that contains the logic (the header portion was moved to Part 1):

foreach (var entityNamespace in orderedEntities.Keys)
{
#>
namespace <# Write(entityNamespace); #>.Instances
{<#
    foreach (var entity in orderedEntities[entityNamespace])
    {
        var currentEntityIndex = indices[entity];#>

    public class <# Write(entity.EntityName); #>Instance :
        GenericInstanceEntity<<# Write(entity.EntityName); #>, <# Write(entity.EntityName); #>Changes, <# Write(entity.EntityName); #>Instance>
    {

        internal const string TableName = "[" + <# Write(ContextName); #>.TableInstancesPrefix + "<# Write(entity.PluralName); #>]";
        /// <summary>
        /// Whether the <see cref="<# Write(entity.EntityName); #>" />
        /// has been deleted.
        /// </summary>
        /// <remarks>
        /// If the deletion state hasn't changed on the current instance,
        /// this will be null.  Refer to <see cref="<# Write(entity.EntityName); #>.Deleted" />
        /// to determine its current deletion state.
        /// </remarks>
        public bool? Deleted { get; set; }
<# 
        foreach (var property in entity.Properties)
        {
            if ((property.Value.Requirements & ER_NOTTRACKED) == ER_NOTTRACKED)
                continue;#>

        /// <summary>
        /// Returns/sets the <see cref="<#
        string shortForm;
        shortformLookup.TryGetValue(property.Value.DataType, out shortForm);
        shortForm = shortForm ?? property.Value.DataType;
        Write(shortForm);
        #>"/> value the <see cref="<# Write(string.Format("{0}.{1}", entity.EntityName, property.Key)); #>"/>
        /// was changed to.
        /// </summary>
        /// <remarks>
        /// Returns null if it was not changed on the current
        /// <see cref="<# Write(entity.EntityName); #>Instance" />.
        /// </remarks>
        public <# WriteLine(string.Format("{0} {1} {{ get; set; }}", nullableTypes.Contains(property.Value.DataType) ? string.Format("{0}?", property.Value.DataType) : property.Value.DataType, property.Key)); 

        }#>

        protected internal override void SetCreatedState()
        {
            this.Changes |= <# Write(entity.EntityName); #>Changes.Created;
        }

        protected override <# Write(entity.EntityName); #>Changes MergeChanges(<# Write(entity.EntityName); #>Changes first, <# Write(entity.EntityName); #>Changes second)
        {
            return first | second;
        }

        protected internal override bool IsPropertySet(<# Write(entity.EntityName); #>Changes property)
        {
            switch (property)
            {
                case <# Write(entity.EntityName); #>Changes.Created:
                    return (this.Changes & <# Write(entity.EntityName); #>Changes.Created) != <# Write(entity.EntityName); #>Changes.Created;<# 
                foreach (var property in entity.Properties)
                {
                    if ((property.Value.Requirements & ER_NOTTRACKED) == ER_NOTTRACKED)
                        continue;#>

                case <# Write(entity.EntityName); #>Changes.<# Write(property.Key); #>:
                    return this.<# Write(property.Key); #> != null;<#
                }
                    #>

                case <# Write(entity.EntityName); #>Changes.Deleted:
                    return this.Deleted != null;
            }
            return false;
        }

        protected override TValue GetProperty<TValue>(<# Write(entity.EntityName); #>Changes property)
        {
            switch (property)
            {
                case <# Write(entity.EntityName); #>Changes.Created:
                    return (TValue)(object)((this.Changes & <# Write(entity.EntityName); #>Changes.Created) != <# Write(entity.EntityName); #>Changes.Created);<#
                foreach (var property in entity.Properties)
                {
                    if ((property.Value.Requirements & ER_NOTTRACKED) == ER_NOTTRACKED)
                        continue;#>

                case <# Write(entity.EntityName); #>Changes.<# Write(property.Key); #>:
                    return (TValue)(object)(this.<# Write(property.Key); #>);<#
                }
                    #>

                case <# Write(entity.EntityName); #>Changes.Deleted:
                    return ((TValue)(object)this.Deleted);
            }
            return default(TValue);
        }
        protected override void OnPropertyChanged<TValue>(<# Write(entity.EntityName); #>Changes property, TValue value)
        {
            switch (property)
            {
                case <# Write(entity.EntityName); #>Changes.Created:
                    bool bValue = (bool)(object)value;
                    if (bValue)
                        this.Changes |= <# Write(entity.EntityName); #>Changes.Created;
                    else
                        this.Changes &= ~<# Write(entity.EntityName); #>Changes.Created;
                    break;<#
                foreach (var property in entity.Properties)
                {
                    if ((property.Value.Requirements & ER_NOTTRACKED) == ER_NOTTRACKED)
                        continue;#>

                case <# Write(entity.EntityName); #>Changes.<# Write(property.Key); #>:
                    this.<# Write(property.Key); #> = (<# Write(property.Value.DataType); #>)(object)(value);
                    break;<#
                }
                    #>

                case <# Write(entity.EntityName); #>Changes.Deleted:
                    this.Deleted = (bool?)(object)value;
                    break;
            }
        }
        public override <# Write(entity.EntityName); #> Root { get { return base.Root; } protected internal set { base.Root = value; } }

        public override <# Write(authorityEntity); #> AuthorizingUser { get { return base.AuthorizingUser; } protected internal set { base.AuthorizingUser = value; } }

    }
<#
    }
#>
}
<#
}
#>
namespace <# WriteLine(ContextNamespace); #>
{
    using System.Data.Entity;
<#
foreach (var entityNamespace in orderedEntities.Keys)
{
#>
    using <# Write(entityNamespace);  #>;
    using <# Write(entityNamespace);  #>.Instances;
<#
}
#>

    partial class <# WriteLine(ContextName); #>
    {
<#
foreach (var entityNamespace in orderedEntities.Keys)
{
    foreach (var entity in orderedEntities[entityNamespace])
    {
#>
        /// <summary>
        /// Returns/sets the <see cref="DbSet{TEntity}" /> of <see cref="<# Write(entity.EntityName); #>" />
        /// instances associated to the <see cref="<# Write(ContextName); #>" />.
        /// </summary>
        public DbSet<<# Write(entity.EntityName); #>> <# Write(entity.PluralName); #> { get; set; }
<#
    }
}#>

<#foreach (var entityNamespace in orderedEntities.Keys)
{
    foreach (var entity in orderedEntities[entityNamespace])
    {
#>
        /// <summary>
        /// Returns/sets the <see cref="DbSet{TEntity}" /> of <see cref="<# Write(entity.EntityName); #>Instance" />
        /// instances associated to the <see cref="<# Write(ContextName); #>" /> which represent the changes
        /// applied to <see cref="<# Write(entity.EntityName); #>" /> entities.
        /// </summary>
        public DbSet<<# Write(entity.EntityName); #>Instance> <# Write(entity.PluralInstanceName); #> { get; set; }
<#
    }
}
foreach (var entityNamespace in orderedEntities.Keys)
{
    foreach (var entity in orderedEntities[entityNamespace])
    {
        var currentEntityIndex = indices[entity];
#>

        /// <summary>
        /// Describes the relationships on the <see cref="<# Write(entity.EntityName); #>" /> entity
        /// with the <paramref name="modelBuilder" /> provided.
        /// </summary>
        /// <param name="modelBuilder">The <see cref="DbModelBuilder" /> instance
        /// which maintains the schema of the data model.</param>
        private void EntitySetup<# {
            var numZeroes = maxEL - (currentEntityIndex+1).ToString().Length;
            for (int i = 0; i < numZeroes; i++)
                Write("0");
            Write((currentEntityIndex + 1).ToString());
         }#>(DbModelBuilder modelBuilder)
        {<#string lowerName = String.Format("{0}{1}", entity.EntityName[0].ToString().ToLower(), entity.EntityName.Substring(1));#>

            var <# Write(lowerName); #>Configuration = modelBuilder.Entity<<# Write(entity.EntityName); #>>();
            <# Write(lowerName); #>Configuration.ToTable(<# Write(entity.EntityName); #>.TableName);
            <# Write(lowerName); #>Configuration.Property(<# Write(lowerName); #>Prop => <# Write(lowerName); #>Prop.Identifier).HasColumnName(<# Write(entity.EntityName); #>.IDName);
            <#
                if (entity.EntityName == authorityEntity)
                {
                    #>/* *
            * Denote the actual key of this entity.  
            * This is required on <# Write(entity.EntityName); #> due to its
            * relationships confusing the model builder?
            * */
            <#
                    Write(lowerName); #>Configuration.HasKey(<# Write(lowerName); #>Prop => <# Write(lowerName); #>Prop.Identifier);
                    <#
                }
            #>

            #region Change Tracking for <# WriteLine(entity.EntityName); #>
            <# Write(lowerName); #>Configuration.HasMany(<# Write(lowerName); #>Navigation => <# Write(lowerName); #>Navigation.Instances).WithRequired(<# Write(lowerName); #>InstNavigation => <# Write(lowerName); #>InstNavigation.Root)
                .Map(<# Write(lowerName); #>InstMapping =>
                    <# Write(lowerName); #>InstMapping.MapKey(<# Write(entity.EntityName); #>.IDName));
            #endregion
<#
            foreach (var property in entity.Properties.Values)
            {
                var targetEnt = entities.FirstOrDefault(ent=>ent.EntityName == property.DataType);
                if (targetEnt != null)
                {
                    var targetEntID = indices[targetEnt];
                    if ((property.Requirements & ER_COLLECTION_BIDIRECTIONAL) == ER_COLLECTION_BIDIRECTIONAL)
                    {
                        string targetLowerName = String.Format("{0}{1}", targetEnt.EntityName[0].ToString().ToLower(), targetEnt.EntityName.Substring(1));
                        var targetEntityProperty = targetEnt.Properties.Values.FirstOrDefault(prop=>prop.DataType == entity.EntityName);
                        if (targetEntityProperty != null && targetEntID > currentEntityIndex)
                        {#>

            <#
                        Write(lowerName); #>Configuration.HasMany(<# Write(lowerName); #>Navigation => <# Write(lowerName); #>Navigation.<# Write(property.Name); #>).WithMany(<# Write(targetLowerName); #>Navigation => <# Write(targetLowerName); #>Navigation.<# Write(targetEntityProperty.Name); #>)
                .Map(<# Write(lowerName); #>To<# Write(targetEnt.EntityName); #>Mapping =>
                {
                    <# Write(lowerName); #>To<# Write(targetEnt.EntityName); #>Mapping.MapLeftKey(<# Write(entity.EntityName); #>.IDName);
                    <# Write(lowerName); #>To<# Write(targetEnt.EntityName); #>Mapping.MapRightKey(<# Write(targetEnt.EntityName); #>.IDName);
                    <# Write(lowerName); #>To<# Write(targetEnt.EntityName); #>Mapping.ToTable(GenerateMappingTableName<<# Write(entity.EntityName); #>, <# Write(targetEnt.EntityName); #>>());
                });
<#

                        }
                    }
                    else
                    {
                        var targetEntityProperty = targetEnt.Properties.Values.FirstOrDefault(prop=>prop.DataType == entity.EntityName);
                        if (targetEntID > currentEntityIndex || targetEntityProperty == null)
                        {
                            bool oneToMany = ((property.Requirements & ER_KEY) != ER_KEY) ||
                                             targetEntityProperty != null &&
                                             (targetEntityProperty.Requirements & ER_COLLECTION) == ER_COLLECTION;
                            bool altIsRequired = 
                                targetEntityProperty != null &&
                                (targetEntityProperty.Requirements & ER_REQUIRED) == ER_REQUIRED;
                            if ((property.Requirements & ER_REQUIRED) == ER_REQUIRED)
                            {#>
                <# Write(lowerName); #>Configuration.HasRequired(<# Write(lowerName); #>Navigation => <# Write(lowerName); #>Navigation.<# Write(property.Name); #>).<# Write(oneToMany ? "WithMany" : altIsRequired ? "WithRequiredDependent" : "WithOptional"); #>()
                    .Map(<# Write(lowerName); #>InstAuthUserMap =>
                        <# Write(lowerName); #>InstAuthUserMap.MapKey(<# Write(targetEnt.EntityName); #>.IDName));

    <#
                            }
                            else
                            {#>
                <# Write(lowerName); #>Configuration.HasOptional(<# Write(lowerName); #>Navigation => <# Write(lowerName); #>Navigation.<# Write(property.Name); #>).<# Write(oneToMany ? "WithMany" : altIsRequired ? "WithRequired" : "WithOptionalDependent"); #>()
                    .Map(<# Write(lowerName); #>InstAuthUserMap =>
                        <# Write(lowerName); #>InstAuthUserMap.MapKey(<# Write(targetEnt.EntityName); #>.IDName));

    <#
                            }
                        }
                    }
                }
            }#>

            var <# Write(lowerName); #>InstanceConfiguration = modelBuilder.Entity<<# Write(entity.EntityName); #>Instance>();
            <# Write(lowerName); #>InstanceConfiguration.ToTable(<# Write(entity.EntityName); #>Instance.TableName);
            <# Write(lowerName); #>InstanceConfiguration.Property(<# Write(lowerName); #>InstProp => <# Write(lowerName); #>InstProp.InstanceIdentifier).HasColumnName(<# Write(entity.EntityName); #>Instance.IDName);

            #region Change Tracking for <# Write(entity.EntityName); #>Instance<#if (entity.EntityName == authorityEntity) {#>

            /* *
             * Denote the self relationship between the <# Write(entity.EntityName); #>,
             * optional in this case to avoid model issues with two required 1:1
             * relationships which isn't allowed.
             * */
            <# Write(lowerName); #>InstanceConfiguration.HasOptional(<# Write(lowerName); #>InstNavigation => <# Write(lowerName); #>InstNavigation.AuthorizingUser).WithMany()
                .Map(<# Write(lowerName); #>InstAuthUserMap =>
                    <# Write(lowerName); #>InstAuthUserMap.MapKey(<# Write(authorityEntity); #>.<# Write(authorityAuthIDFieldName); #>));
            <#}else{#>

            /* *
             * Denote the relationship between the <# Write(entity.EntityName); #>Instance and
             * the <# Write(authorityEntity); #>.
             * */
            <# Write(lowerName); #>InstanceConfiguration.HasRequired(<# Write(lowerName); #>InstNavigation => <# Write(lowerName); #>InstNavigation.AuthorizingUser).WithMany()
                .Map(<# Write(lowerName); #>InstAuthUserMap =>
                    <# Write(lowerName); #>InstAuthUserMap.MapKey(<# Write(authorityEntity); #>.<# Write(authorityAuthIDFieldName); #>));
            <#}#>

            <# Write(lowerName); #>InstanceConfiguration.HasOptional(<# Write(lowerName); #>InstNavigation => <# Write(lowerName); #>InstNavigation.Previous).WithOptionalDependent()
                .Map(<# Write(lowerName); #>InstPrevMapping =>
                    <# Write(lowerName); #>InstPrevMapping.MapKey(<# Write(entity.EntityName); #>Instance.PreviousIDName));
            #endregion<#if (currentEntityIndex + 2 <= entities.Length)
             {
             #>


            EntitySetup<# {
            var numZeroes = maxEL - (currentEntityIndex+2).ToString().Length;
            for (int i = 0; i < numZeroes; i++)
                Write("0");
            Write((currentEntityIndex + 2).ToString());
         }#>(modelBuilder);<#}#>

        }

<#
    }
}
foreach (var entityNamespace in orderedEntities.Keys)
{
    foreach (var entity in orderedEntities[entityNamespace])
    {
            var requiredProperties = (from property in entity.Properties.Values
                                      where (property.Requirements & ER_REQUIRED) == ER_REQUIRED
                                      let UseSet = (property.Requirements & ER_NOTTRACKED) != ER_NOTTRACKED
                                      orderby UseSet descending
                                      select new { Property = property, UseSet }).ToArray();
            var optionalProperties = (from property in entity.Properties.Values
                                      where (property.Requirements & ER_REQUIRED)   != ER_REQUIRED &&
                                            (property.Requirements & ER_COLLECTION) != ER_COLLECTION &&
                                            (property.Requirements & ER_IGNORE_ON_ADDMETHOD) != ER_IGNORE_ON_ADDMETHOD
                                      let UseSet = (property.Requirements & ER_NOTTRACKED) != ER_NOTTRACKED
                                      orderby UseSet descending
                                      select new { Property = property, UseSet }).ToArray();
            var noPropertiesUseSet = 
                ((from rp in requiredProperties
                  where rp.UseSet
                  select rp).FirstOrDefault() ??
                 (from op in optionalProperties
                  where op.UseSet
                  select op).FirstOrDefault()) == null;
        #>
        /// <summary>
        /// Adds a <see cref="<# Write(entity.EntityName); #>" /> to the <see cref="<# Write(ContextName); #>" />
        /// with the <paramref name="authorizingUser" /><#
        bool last = false;
        var lastProp = optionalProperties.LastOrDefault() ?? requiredProperties.LastOrDefault();
        foreach (var property in requiredProperties)
        {
            string lowerPropertyName = property.Property.Name[0].ToString().ToLower() + property.Property.Name.Substring(1);
            Write(", ");
            bool isLast = lastProp == property;
            if (isLast)
                Write("and ");
            Write(string.Format("<paramref name=\"{0}\" />", lowerPropertyName));
        }
        foreach (var property in optionalProperties)
        {
            string lowerPropertyName = property.Property.Name[0].ToString().ToLower() + property.Property.Name.Substring(1);
            Write(", ");
            bool isLast = lastProp == property;
            if (isLast)
                Write("and ");
            Write(string.Format("<paramref name=\"{0}\" />", lowerPropertyName));
        }
        #> provided.
        /// </summary>
        /// <param name="authorizingUser">
        /// The <see cref="<# Write(authorityEntity); #>"/> which is responsible
        /// for the insertion request.
        /// </param>
<#
        foreach (var property in requiredProperties)
        {
            string lowerPropertyName = property.Property.Name[0].ToString().ToLower() + property.Property.Name.Substring(1);
        #>
        /// <param name="<# Write(lowerPropertyName); #>">
        /// The required <see cref="<#
        string shortForm;
        shortformLookup.TryGetValue(property.Property.DataType, out shortForm);
        shortForm = shortForm ?? property.Property.DataType;
        Write(shortForm);
        #>"/> which denotes the <see cref="<# Write(entity.EntityName); #>.<# Write(property.Property.Name); #>" />
        /// of the <see cref="<# Write(entity.EntityName); #>" /> to add.
        /// </param>
<#
        }
        foreach (var property in optionalProperties)
        {
            string lowerPropertyName = property.Property.Name[0].ToString().ToLower() + property.Property.Name.Substring(1);
        #>
        /// <param name="<# Write(lowerPropertyName); #>">
        /// The optional <see cref="<#
        string shortForm;
        shortformLookup.TryGetValue(property.Property.DataType, out shortForm);
        shortForm = shortForm ?? property.Property.DataType;
        Write(shortForm);
        #>"/> which denotes the <see cref="<# Write(entity.EntityName); #>.<# Write(property.Property.Name); #>" />
        /// of the <see cref="<# Write(entity.EntityName); #>" /> to add.
        /// </param>
<#
        }
        #>
        internal <# Write(entity.EntityName); #> Add<# Write(entity.EntityName); #>(<# Write(authorityEntity); #> authorizingUser<#
        foreach (var property in requiredProperties)
        {
            string lowerPropertyName = property.Property.Name[0].ToString().ToLower() + property.Property.Name.Substring(1);
            Write(", ");
            Write(property.Property.DataType);
            Write(" ");
            Write(lowerPropertyName);
        }
        foreach (var property in optionalProperties)
        {
            Write(", ");
            string lowerPropertyName = property.Property.Name[0].ToString().ToLower() + property.Property.Name.Substring(1);
            Write(property.Property.DataType);
            if (nullableTypes.Contains(property.Property.DataType))
                Write("?");
            Write(" ");
            Write(lowerPropertyName);
            Write(" = null");
        }
        #>)
        {
            var result = new <# Write(entity.EntityName); #>();<#
            if (noPropertiesUseSet)
            {
            #>

            result.CheckInstance(authorizingUser);
<#
            }#>
<#
        foreach (var property in requiredProperties)
        {
            string lowerPropertyName = property.Property.Name[0].ToString().ToLower() + property.Property.Name.Substring(1);#>
            <#Write("result.");
            if (property.UseSet)
            {
                WriteLine(string.Format("Set{0}({1}, authorizingUser);", property.Property.Name, lowerPropertyName));
            }
            else
            {
                WriteLine(string.Format("{0} = {1};", property.Property.Name, lowerPropertyName));                
            }
        }
        foreach (var property in optionalProperties)
        {
            string lowerPropertyName = property.Property.Name[0].ToString().ToLower() + property.Property.Name.Substring(1);#>
            if (<# Write(lowerPropertyName); #> != null)
                <#Write("result.");
            if (property.UseSet)
            {
                WriteLine(string.Format("Set{0}({1}{2}, authorizingUser);", property.Property.Name, lowerPropertyName, (nullableTypes.Contains(property.Property.DataType)) ? ".Value" : string.Empty));
            }
            else
            {
                WriteLine(string.Format("{0} = {1}{2};", property.Property.Name, lowerPropertyName, (nullableTypes.Contains(property.Property.DataType)) ? ".Value" : string.Empty));
            }
        }
            #>
            this.<# Write(entity.PluralName); #>.Add(result);
            this.<# Write(entity.PluralInstanceName); #>.Add(result.CurrentInstance);
            return result;
        }

<#
    }
}
#>
    }
}
  • The referenced GenericInstanceEntity is here.
  • The referenced GenericRootEntity is here.
  • The results of running the Text Template is here.

The referenced method and associated constant values are:

internal const string TablePrefix = "FCAS.";
internal const string TableInstancesPrefix = "FCAS.INST.";
internal const string TableMappingPrefix = "FCAS.MAP.";


internal static bool ValuesEqual<TValue>(TValue left, TValue right)
{
    var obj = (object)left;
    if (obj != null)
    if (typeof(IEquatable<TValue>).IsAssignableFrom(typeof(TValue)))
        return ((IEquatable<TValue>)left).Equals(right);
    else
        return left.Equals(right);
    else
    return ((object)right) == null;
}

I'm posting this because I'm curious if this is a decent path to follow for such a project, or if I'm sorely mislead. Bidirectional collections of entities are supported; however, single 1:many relationships aren't present within the template's construction. The means to which change tracking is managed is a bit convoluted; however, its goal is to simplify construction of such a model. I'm pretty sure if there's overhead it will mostly be due to the entity framework itself.

I'd like to know if I'm on the right track.

Deletion of primary entities is not allowed, for data retention reasons. The appearance of deletion is handled through flags.

Changes are tracked by Column, and only the columns that you state are, by your own design, malleable. It seems to make more sense than trying to use the Standard T-SQL change tracking, unless there's something I missed about it.

\$\endgroup\$
  • \$\begingroup\$ You should probably narrow down your question. I can hardly imagine someone scrolling through this unreadable wall of code. \$\endgroup\$ – Nikita B Feb 18 '14 at 6:19
  • \$\begingroup\$ Is it that unreadable? I find it easier than comprehending language-centric projects. I had a pattern I wanted structured for a project and instead of trudging through redundant boiler plate code, I chose the solution which constructs the code for me. I do it once, and it replicates automatically, benefits abound since fixes propagate through everything. \$\endgroup\$ – Alexander Morou Feb 18 '14 at 6:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.