This is Part 1 of a question about performance and general best practices. Part 2 is located here. The body of the messages are the same to illustrate their similarity, the difference is the code provided. This one provides the data the text template utilizes, the other provides the logic to generate the code. The focus isn't so much on the template, but the pattern of managing a database that tracks changes across potentially multiple domains.
Another thing I decided I'll be adding: the templates will construct the basic outline of the dialogs through WPF for me. In the case of searching for items within the database, I've already used the data on the entities to generate query builders:
var recipeQuery = new RecipeQueryData(user);
recipeQuery.CreateIngredientsQueryData();
recipeQuery.IngredientsData.CreateItemQueryData();
recipeQuery.IngredientsData.ItemData.NameCriteria = "t";
recipeQuery.IngredientsData.ItemData.SearchNameType = StringSearchType.Contains;
var recipesWithT2 = RecipeQueryData.ConstructQuery(recipeQuery, fcasCtx.Recipes);
Compared with:
var recipesWithT =
(from recipe in fcasCtx.Recipes
from ingredient in recipe.Ingredients
where ingredient.Item.Name.Contains(criteria)
select recipe).Distinct();
Console.WriteLine(recipesWithT.ToString() == recipesWithT2.ToString());
Yields true. While by itself isn't much, it's interesting that I'll be able to enable a user to have a fairly powerful search tool by specifying nested conditions.
I've uploaded a small example which demonstrates the template's use. It's changed quite a lot since the code posted and edited by lol.upvote. It automatically generates the files and folders based off of the context namespace provided in EntityConstants.tt. To preserve space I've removed all binaries, so you might have to fix it in the package manager console when you load the solution.
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.
A portion of the logic from Part 2 was moved here for space reasons when it was updated to the most recent version of the code.
Template
Header
<#@ template debug="true" hostspecific="false" language="C#" #>
<#@ assembly name="System.Core" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ output extension=".cs" #>
using AllenCopeland.FCAS.Administration;
using AllenCopeland.FCAS.Instances;
<#
const long ER_NONE = 0x000,
ER_NOTTRACKED = 0x001,
ER_REQUIRED = 0x002,
ER_MAXLEN = 0x004,
ER_KEY = 0x008,
ER_FIXED = 0x010 | ER_NOTTRACKED,
ER_COLLECTION = 0x020 | ER_NOTTRACKED,
ER_COLLECTION_BIDIRECTIONAL = 0x040 | ER_COLLECTION,
ER_COLLECTION_UNIDIRECTIONAL_REQUIRED = 0x080 | ER_COLLECTION,
ER_COLLECTION_UNIDIRECTIONAL_OPTIONAL = 0x100 | ER_COLLECTION,
ER_IGNORE_ON_ADDMETHOD = 0x200,
ER_REQUIRED_NO_ATTRIBUTE = 0x400 | ER_REQUIRED;
var nullableTypes = new string[] { "bool", "byte", "short", "int", "long", "decimal", "double", "float" };
const string ContextName = "FCASDBContext";
const string ContextNamespace = "AllenCopeland.FCAS";
const string authorityEntity = "DomainUser";
const string authorityAuthIDFieldName = "AuthUserID";
const string authorityAuthIDFieldValue = "AuthorizerID";
var shortformLookup = new Dictionary<string, string>()
{
{ "string", "System.String" },
{ "bool", "System.Boolean" },
{ "byte", "System.Byte" },
{ "short", "System.Int16" },
{ "int", "System.Int32" },
{ "long", "System.Int64" },
{ "decimal", "System.Decimal" },
{ "double", "System.Double" },
{ "float", "System.Single" },
};
Entities
var entities = new []
{
new {
Namespace = "AllenCopeland.FCAS.Stock",
EntityName = "DistributorInventoryItem",
PluralName = "DistributorInventoryItems",
PluralInstanceName = "DistributorInventoryItemInstances",
Properties = new []
{
new { Name = "Name", DataType = "string", Requirements = ER_REQUIRED, Description = "of the name of the <see cref=\"DistributorInventoryItem\" />"},
new { Name = "Price", DataType = "decimal", Requirements = ER_REQUIRED, Description = "of the price of the <see cref=\"DistributorInventoryItem\" />" },
new { Name = "PriceByWeight", DataType = "bool", Requirements = ER_REQUIRED, Description = "on whether the <see cref=\"DistributorInventoryItem.Price\" /> is charged by weight." },
new { Name = "Quantity", DataType = "int", Requirements = ER_REQUIRED, Description = "of the number of items received relative to each <see cref=\"DistributorInventoryItem.Pack\" />" },
new { Name = "QuantityMax", DataType = "int", Requirements = ER_NONE, Description = "of the maximum number of items received relative to each <see cref=\"DistributorInventoryItem.Pack\" />" },
new { Name = "Pack", DataType = "int", Requirements = ER_REQUIRED, Description = "of the number of packs for the <see cref=\"DistributorInventoryItem\" /> received per each unit on an order." },
new { Name = "Distributor", DataType = "Distributor", Requirements = ER_REQUIRED, Description = "the <see cref=\"DistributorInventoryItem\" /> belongs to." },
new { Name = "ProductID", DataType = "int", Requirements = ER_REQUIRED, Description = "that represents the unique identifier of the <see cref=\"DistributorInventoryItem\" /> relative to its <see cref=\"Distributor\" />." },
new { Name = "Brand", DataType = "Brand", Requirements = ER_REQUIRED, Description = "that represents the unique identifier of the <see cref=\"DistributorInventoryItem\" /> relative to its <see cref=\"Distributor\" />." },
}.ToDictionary(keySelector => keySelector.Name, valueSelector => valueSelector),
},
new {
Namespace = "AllenCopeland.FCAS.Stock",
EntityName = "Distributor",
PluralName = "Distributors",
PluralInstanceName = "DistributorInstances",
Properties = new []
{
new { Name = "Name", DataType = "string", Requirements = ER_REQUIRED, Description = "of the name of the <see cref=\"Distributor\" />." },
new { Name = "Logo", DataType = "DatabaseImage", Requirements = ER_NONE, Description = "which represents the logo for the <see cref=\"Distributor\" />." },
}.ToDictionary(keySelector => keySelector.Name, valueSelector => valueSelector),
},
new {
Namespace = "AllenCopeland.FCAS.Stock",
EntityName = "Brand",
PluralName = "Brands",
PluralInstanceName = "BrandInstances",
Properties = new []
{
new { Name = "Name", DataType = "string", Requirements = ER_REQUIRED, Description = "of the name of the <see cref=\"Brand\" />" },
}.ToDictionary(keySelector => keySelector.Name, valueSelector => valueSelector),
},
new {
Namespace = "AllenCopeland.FCAS",
EntityName = "DatabaseImage",
PluralName = "DatabaseImages",
PluralInstanceName = "DatabaseImageInstances",
Properties = new []
{
new { Name = "Name", DataType = "string", Requirements = ER_REQUIRED, Description = "of the name of the <see cref=\"DatabaseImage\" />." },
new { Name = "Description", DataType = "string", Requirements = ER_NONE, Description = "of the description of the <see cref=\"DatabaseImage\" />." },
new { Name = "Data", DataType = "Byte[]", Requirements = ER_NOTTRACKED | ER_REQUIRED,
Description = "which contains the binary data of the <see cref=\"DatabaseImage\" />." },
}.ToDictionary(keySelector => keySelector.Name, valueSelector => valueSelector),
},
new {
Namespace = "AllenCopeland.FCAS.Administration",
EntityName = "DomainUser",
PluralName = "DomainUsers",
PluralInstanceName = "DomainUserInstances",
Properties = new []
{
new { Name = "SecurityIdentifier", DataType = "string", Requirements = ER_KEY | ER_FIXED | ER_MAXLEN | (46L << 32) | ER_REQUIRED,
Description = "of the security identifier of the <see cref=\"DomainUser\" />" },
new { Name = "Disabled", DataType = "bool", Requirements = ER_NONE | ER_IGNORE_ON_ADDMETHOD,
Description = "of whether the <see cref=\"DomainUser\" /> is disabled." },
new { Name = "Domain", DataType = "Domain", Requirements = ER_REQUIRED_NO_ATTRIBUTE | ER_FIXED,
Description = "which denotes the domain from which the <see cref=\"DomainUser\" /> is derived." },
}.ToDictionary(keySelector => keySelector.Name, valueSelector => valueSelector),
},
new {
Namespace = "AllenCopeland.FCAS.Administration",
EntityName = "Domain",
PluralName = "Domains",
PluralInstanceName = "DomainInstances",
Properties = new []
{
new { Name = "Name", DataType = "string", Requirements = ER_REQUIRED, Description = "of the name of the <see cref=\"Domain\" />" },
new { Name = "Companies", DataType = "Company", Requirements = ER_COLLECTION_BIDIRECTIONAL,
Description = "set which denotes which companies have access to the <see cref=\"Domain\" />." }
}.ToDictionary(keySelector => keySelector.Name, valueSelector => valueSelector),
},
new {
Namespace = "AllenCopeland.FCAS.Administration",
EntityName = "Company",
PluralName = "Companies",
PluralInstanceName = "CompanyInstances",
Properties = new []
{
new { Name = "Name", DataType = "string", Requirements = ER_REQUIRED, Description = "of the name of the <see cref=\"Company\" />" },
new { Name = "Description", DataType = "string", Requirements = ER_NONE, Description = "of the descriptive text of the <see cref=\"Company\" />" },
new { Name = "Domains", DataType = "Domain", Requirements = ER_COLLECTION_BIDIRECTIONAL,
Description = "set which denotes which domains the <see cref=\"Company\" /> has access to the ." }
}.ToDictionary(keySelector => keySelector.Name, valueSelector => valueSelector),
},
new {
Namespace = "AllenCopeland.FCAS.Stock",
EntityName = "InventoryItem",
PluralName = "InventoryItems",
PluralInstanceName = "InventoryItemInstances",
Properties = new []
{
new { Name = "Name", DataType = "string", Requirements = ER_REQUIRED, Description = "of the name of the <see cref=\"InventoryItem\" />" },
new { Name = "Units", DataType = "InventoryUnit", Requirements = ER_COLLECTION_BIDIRECTIONAL,
Description = "set which denotes the inventory units associated to the <see cref=\"InventoryItem\" />" },
new { Name = "Categories", DataType = "InventoryItemCategory",
Requirements = ER_COLLECTION_BIDIRECTIONAL,
Description = "set which denotes the inventory categories associated to the <see cref=\"InventoryItem\" />" },
}.ToDictionary(keySelector => keySelector.Name, valueSelector => valueSelector),
},
new {
Namespace = "AllenCopeland.FCAS.Stock",
EntityName = "InventoryItemCategory",
PluralName = "InventoryItemCategories",
PluralInstanceName = "InventoryItemCategoryInstances",
Properties = new []
{
new { Name = "Name", DataType = "string", Requirements = ER_REQUIRED, Description = "of the name of the <see cref=\"InventoryItem\" />" },
new { Name = "Items", DataType = "InventoryItem", Requirements = ER_COLLECTION_BIDIRECTIONAL,
Description = "set which denotes the inventory items associated to the <see cref=\"InventoryItemCategory\" />" },
}.ToDictionary(keySelector => keySelector.Name, valueSelector => valueSelector),
},
new {
Namespace = "AllenCopeland.FCAS.Stock",
EntityName = "InventoryUnit",
PluralName = "InventoryUnits",
PluralInstanceName = "InventoryUnitInstances",
Properties = new []
{
new { Name = "Name", DataType = "string", Requirements = ER_REQUIRED, Description = "of the name of the <see cref=\"InventoryUnit\" />" },
new { Name = "PluralName", DataType = "string", Requirements = ER_REQUIRED, Description = "of the plural name of the <see cref=\"InventoryUnit\" />" },
new { Name = "ShortName", DataType = "string", Requirements = ER_NONE, Description = "of the shortened name of the <see cref=\"InventoryUnit\" />" },
new { Name = "Items", DataType = "InventoryItem", Requirements = ER_COLLECTION_BIDIRECTIONAL,
Description = "set which denotes the inventory items associated to the <see cref=\"InventoryUnit\" />" },
}.ToDictionary(keySelector => keySelector.Name, valueSelector => valueSelector),
},
};
Followed by:
var orderedEntities = (from e in entities
orderby e.Namespace,
e.EntityName
group e by e.Namespace).ToDictionary(key=>key.Key, value=>value.ToArray());
int index = 0;
var indices = entities.ToDictionary(keySelector=>keySelector, valueSelector=>0);
foreach (var entity in from ns in orderedEntities.Keys
from entity in orderedEntities[ns]
select entity)
indices[entity] = index++;
var maxEL = index.ToString().Length;
Enums
#>
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
<#
foreach (var entityNamespace in orderedEntities.Keys)
{
#>
namespace <# WriteLine(entityNamespace); #>
{
using <# Write(entityNamespace); #>.Instances;
<#
foreach (var entity in orderedEntities[entityNamespace])
{
var currentEntityIndex = indices[entity];
#>
/// <summary>
/// Denotes the potential elements within a <see cref="<# Write(entity.EntityName); #>" />
/// that can change.
/// </summary>
[Flags]
public enum <# Write(entity.EntityName); #>Changes :
<#
int pC = entity.Properties.Count - entity.Properties.Values.Where(k=>(k.Requirements & ER_NOTTRACKED) == ER_NOTTRACKED).Count();
if (pC <= 6)
WriteLine("byte");
else if (pC <= 13)
WriteLine("short");
else if (pC <= 29)
WriteLine("int");
else
WriteLine("long");
#>
{
/// <summary>
/// The <see cref="<# Write(entity.EntityName); #>" /> was created.
/// </summary>
Created = 1,<#
WriteLine("");
int offset = 0;
foreach (var property in entity.Properties)
{
if ((property.Value.Requirements & ER_NOTTRACKED) == ER_NOTTRACKED)
continue;#>
/// <summary>
/// The <see cref="<# Write(string.Format("{0}.{1}", entity.EntityName, property.Key)); #>"/>
/// was changed on the <see cref="<# Write(entity.EntityName); #>" />.
/// </summary>
<#WriteLine(string.Format("{0} = {1},", property.Key, Math.Pow(2, ++offset)));#><#}
#>
/// <summary>
/// The <see cref="<# Write(entity.EntityName); #>.Deleted"/>
/// was changed on the <see cref="<# Write(entity.EntityName); #>" />.
/// </summary>
Deleted = <# Write(Math.Pow(2, ++offset).ToString()); #>,
}
Entity Classes
public class <# Write(entity.EntityName); #> :
GenericRootEntity<<# Write(entity.EntityName); #>, <# Write(entity.EntityName); #>Changes, <# Write(entity.EntityName); #>Instance>
{
<#if (entity.EntityName == authorityEntity) {#>internal const string <# Write(authorityAuthIDFieldName); #> = "<# Write(authorityAuthIDFieldValue); #>";<#}#>
internal const string TableName = "[" + FCASDBContext.TablePrefix + "<# Write(entity.PluralName); #>]";
<#
foreach (var property in entity.Properties.Values)
{
if ((property.Requirements & ER_COLLECTION) == ER_COLLECTION)
{#>
/// <summary>
/// Data member for <see cref="<# Write(property.Name); #>" />
/// </summary>
private ICollection<<# Write(property.DataType); #>> _<# Write(property.Name); #>;
<#}}#>
public bool Deleted { get; protected set; }
<#
foreach (var property in entity.Properties)
{#>
/// <summary>
/// Returns the <see cref="<#
string shortForm;
shortformLookup.TryGetValue(property.Value.DataType, out shortForm);
shortForm = shortForm ?? property.Value.DataType;
Write(shortForm);
#>"/> value <# WriteLine(property.Value.Description); #>
/// </summary>
<#
if ((property.Value.Requirements & ER_REQUIRED) == ER_REQUIRED && (property.Value.Requirements & ER_REQUIRED_NO_ATTRIBUTE) != ER_REQUIRED_NO_ATTRIBUTE){
#>
[Required()]
<#}
if ((property.Value.Requirements & ER_KEY) == ER_KEY){
#>
[Key()]
<#}
if ((property.Value.Requirements & ER_KEY) == ER_KEY){
#>
[MaxLength(<# Write(((property.Value.Requirements & 0x7FFFFFFF00000000L) >> 32).ToString()); #>)]
<#}
if ((property.Value.Requirements & ER_FIXED) == ER_FIXED){
#>
[Editable(false)]
<#}
if (((property.Value.Requirements & ER_COLLECTION) == ER_COLLECTION))
{#>
public virtual ICollection<<# Write(property.Value.DataType);#>> <# WriteLine(property.Key); #>
{
get
{
if (this._<# Write(property.Key); #> == null)
this._<# Write(property.Key); #> = new HashSet<<# Write(property.Value.DataType);#>>();
return this._<# Write(property.Key); #>;
}
protected set
{
this._<# Write(property.Key); #> = value;
}
}<#
}
else
{
var targetEnt = entities.FirstOrDefault(ent=>ent.EntityName == property.Value.DataType);
#>
public <# WriteLine(string.Format("{3}{0} {1} {{ get; {2}set; }}", string.Format("{0}{1}", property.Value.DataType, (nullableTypes.Contains(property.Value.DataType) && (property.Value.Requirements & ER_REQUIRED) != ER_REQUIRED) ? "?" : string.Empty), property.Key, ((property.Value.Requirements & ER_NOTTRACKED) == ER_NOTTRACKED) ? string.Empty : "protected ", targetEnt != null ? "virtual " : string.Empty));
}
}
foreach (var property in entity.Properties)
{
if ((property.Value.Requirements & ER_NOTTRACKED) == ER_NOTTRACKED)
continue;#>
internal void Set<# Write(property.Key); #>(<# Write(property.Value.DataType); #> value, <# Write(authorityEntity); #> authorizingUser)
{
if (this.<# Write(property.Key); #> == value)
return;
this.PropertyChange(<# Write(entity.EntityName); #>Changes.<# Write(property.Key); #>, this.<# Write(property.Key); #> = value, authorizingUser);
}
<#}#>
internal void Delete(<# Write(authorityEntity); #> authorizingUser)
{
if (this.Deleted)
return;
this.PropertyChange(<# Write(entity.EntityName); #>Changes.Deleted, this.Deleted = true, authorizingUser);
}
internal void Undelete(<# Write(authorityEntity); #> authorizingUser)
{
if (!this.Deleted)
return;
this.PropertyChange(<# Write(entity.EntityName); #>Changes.Deleted, this.Deleted = false, authorizingUser);
}
}
<#
}
#>
}
<#
}
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.
The focus isn't so much on the template, but the pattern of managing a database that tracks changes across potentially multiple domains.- this would normally be off-topic on this site, we... review code ;) \$\endgroup\$