In this article, I add the ability to create hierarchical relationships between entities and the UIs that support describing the metadata for these relationships as well as creating records that are in relationship with a selected parent record.
Table of Contents
As stated in the conclusion of Part I, Part II will implement the hierarchical aspect of the project name Adaptive Hierarchical Knowledge Management Meta-Model:
New Features:
- The next piece to add is the
ParentChildMap instances and the AllowableParentChildRelationship model, which will allow us to do a lot more interesting things by creating a hierarchy of entity relationships. I demonstrate how we can create a recipe book with the following entities:
- The recipe creator (a
Name entity) - The recipe ingredients (a new
RecipeIngredient entity) - The recipe directions (a new
RecipeStep entity)
- And we will see how we can work these relationships in different ways:
- What recipes do we have?
- What recipes do we have from a particular person?
- What recipes do we have that use a particular ingredient?
- Ordering entity fields so we can re-arrange the default order the record's grid columns.
Refactoring:
- Hard-coded actual table and table column names
- Review naming convention as I'm using the term "entity" to refer to both the model and the collection names the user defines in the model
- Serving the built-in web pages properly rather than the kludge I have currently implemented
- Services should be implemented with interfaces for their
public methods
Constraints:
EntityField should be unique for EntityID, Name, DeletedEntity should be unique for Name, DeletedEntityInstance should be unique for InstanceID, DeletedEntityFieldValue should be unique for InstanceID, EntityID, EntityFieldID, Deleted
Indexing:
- Indexing needs to be implemented for performance
The download includes a backup of the SQL Express 2017 database I'm using for this demo, so if you restore it, the only thing you'll need to do is set the connection string in appsettings.json:
"ConnectionStrings": {
"DefaultConnection": "[your connection string]"
}
Let's deal with the boring part first. The first thing I did was remove the hard-coded string literals for table and table columns, except where used in the SQL statements. There is now a new class:
public static class Constants
{
public const string ID = "ID";
public const string VALUE = "Value";
public const string NAME = "Name";
public const string DELETED = "Deleted";
public const string ENTITY = "Entity";
public const string ENTITY_ID = "EntityID";
public const string ENTITY_FIELD = "EntityField";
public const string ENTITY_FIELD_ID = "EntityFieldID";
public const string ENTITY_FIELD_VALUE = "EntityFieldValue";
public const string ENTITY_FIELDS = "EntityFields";
public const string ENTITY_INSTANCE = "EntityInstance";
public const string ENTITY_INSTANCE_ID = "EntityInstanceID";
}
Second, the EntityController and EntityService have been renamed to RecordController and RecordService respectively to help reduce the confusion between the meta-model tables and record collections of entity instances. This also changes the built-in record editor path from /entity to /record as well as some other minor front-end changes to support / be consistent with the back-end renaming.
Moving forward, the following terms will have the following meanings.
- Entity: Meta-model: a collection of names for which records can be created
- Entity field: Meta-model: a collection of fields associated with an entity
- Entity relationship: Meta-model: a collection of parent entities to permissible child entities
- Record: An instance of an entity, with the associated entity fields pivoted into columns
- Entity instance: In the DB model, the ID of a record, which maps to a specific entity definition
- Entity field value: In the DB model, an instance of a record that is associated with the meta-model's entity name, entity field, and entity instance, hold a value
- Relationship instance: An instance of a relationship between a parent record and a child record.
I'm deferring this to Part III. It just seems low priority!
The following constraints have been added. The side-effect of this is that the back-end should check to see if a field being added has already been deleted, and if so, undelete the deleted field of that name. This also applies to operations on the entity fields.
Entity should be unique for Name, Deleted.
ALTER TABLE Entity ADD UNIQUE (Name, Deleted);
EntityField should be unique for EntityID, Name, Deleted.
ALTER TABLE EntityField ADD UNIQUE (Name, EntityID, Deleted);
EntityFieldValue should be unique for EntityInstanceID, EntityID, EntityFieldID, Deleted.
ALTER TABLE EntityFieldValue ADD UNIQUE _
(EntityInstanceID, EntityID, EntityFieldID, Deleted);
The following indices have been added:
CREATE NONCLUSTERED INDEX [IX_Entity] ON [dbo].[Entity] ([Name] ASC)
CREATE NONCLUSTERED INDEX [IX_EntityField] ON [dbo].[EntityField] ([EntityID] ASC, [Name] ASC)
CREATE NONCLUSTERED INDEX [IX_EntityFieldValue] ON [dbo].[EntityFieldValue] _
([EntityID] ASC, [EntityFieldID] ASC, [EntityInstanceID] ASC)
CREATE NONCLUSTERED INDEX [IX_EntityInstance] ON [dbo].[EntityInstance] ([EntityID] ASC)
services.AddSingleton<ITableService, TableService>();
services.AddSingleton<IRecordService, RecordService>();
services.AddSingleton<IRelationshipService, RelationshipService>();
Done! Not going to bore you with the interface code.
At this point, it is useful to support table joins in our queries. The reason for this is that we want to add the parent and child entity names automatically to the relationship queries so the client doesn't need the collection of entities and doesn't need to map the parent/child entity ID to the entity collection to acquire the parent/child entity name.
To start with, there is now a Join definition class:
public class Join
{
public enum JoinType
{
Inner,
Left,
Right,
Full,
}
public string Table { get; set; }
public string TableField { get; set; }
public string WithTable { get; set; }
public FieldAliases WithTableFields { get; set; }
public JoinType Type { get; set; }
public Join(string table, string withTable, _
FieldAliases withTableFields, JoinType type = JoinType.Inner, string tableField = null)
{
Table = table;
WithTable = withTable;
TableField = tableField;
WithTableFields = withTableFields;
Type = type;
}
}
and we have a Joins collection that lets us acquire the joins the join fields:
public string GetJoins()
{
string ret = String.Empty;
if (Count > 0)
{
List<string> joins = new List<string>();
var joinAliases = GetJoinAliases();
this.ForEachWithIndex((j, idx) =>
{
var tableFieldName = j.TableField ?? $"{j.WithTable}ID";
var alias = joinAliases[idx].alias;
var joinType = joinTemplate[j.Type];
var joinTableAliases = joinAliases.Where(a => a.Value.table == j.Table).ToList();
var joinTableAlias = j.Table;
Assertion.IsTrue(joinTableAliases.Count <= 1, $"Unsupported:
Cannot join multiple instances of {j.Table} with other joins.");
if (joinTableAliases.Count == 1)
{
joinTableAlias = joinTableAliases[0].Value.alias;
}
var join = $"{joinType} {j.WithTable} {alias} on {alias}.ID =
{joinTableAlias}.{tableFieldName} and {alias}.Deleted = 0";
joins.Add(join);
});
ret = String.Join(Constants.CRLF, joins);
}
return ret;
}
public string GetJoinFields(string prepend = "")
{
string ret = String.Empty;
if (Count > 0)
{
List<string> joinFields = new List<string>();
var joinAliases = GetJoinAliases();
this.ForEachWithIndex((j, idx) =>
{
if (j.WithTableFields != null)
{
var joinTableAlias = joinAliases[idx].alias;
j.WithTableFields.ForEach(f =>
{
joinFields.Add($"{joinTableAlias}.{f.Key} {f.Value}");
});
}
});
if (joinFields.Count > 0)
{
ret = prepend + String.Join(",", joinFields);
}
}
return ret;
}
private Dictionary<int, (string alias, string table)> GetJoinAliases()
{
Dictionary<int, (string alias, string table)> joinAliases = new Dictionary<int,
(string alias, string table)>();
this.ForEachWithIndex((j, idx) =>
{
var alias = $"{j.WithTable}{idx}";
joinAliases.TryGetValue(idx, out (string alias, string table) tableAlias);
tableAlias.alias ??= alias;
tableAlias.table ??= j.WithTable;
joinAliases[idx] = tableAlias;
});
return joinAliases;
}
The above is a gnarly piece of code that figures out the table aliases for a complex join, for example, this is the SQL that is generated for a relationship instance query:
select RelationshipInstance.* ,Entity1.Name Parent,Entity2.Name Child
from RelationshipInstance
JOIN EntityRelationship EntityRelationship0 on EntityRelationship0.ID = _
RelationshipInstance.EntityRelationshipID and EntityRelationship0.Deleted = 0
JOIN Entity Entity1 on Entity1.ID = EntityRelationship0.ParentEntityID and Entity1.Deleted = 0
JOIN Entity Entity2 on Entity2.ID = EntityRelationship0.ChildEntityID and Entity2.Deleted = 0
where RelationshipInstance.Deleted = 0
Notice how we're joining on the EntityRelationship (which has no fields we are returning) as well as the Entity records that have the parent and child entity names. There are limitations to this functionality at the moment, as described in the comments and asserted.
This also required modify the "select" builders, and since this is common for both the SqlSelectBuilder and SqlInsertSelectBuilder, I created a method they both can call:
private StringBuilder GetCoreSelect(string table, Joins joins = null)
{
var joinFields = joins?.GetJoinFields(",") ?? "";
var joinTables = joins?.GetJoins() ?? "";
StringBuilder sb = new StringBuilder();
sb.Append($"select {table}.* {joinFields} from {table} {joinTables}
where {table}.Deleted = 0");
return sb;
}
I'll show you later how this is used.
Relationships between records should be dynamic, particularly in the sense that if the user needs a new relationship between two entities, such a relationship is easily created. At the moment, I'm doing a bare-bones implementation to get the core concept of adaptive hierarchical record management working. What I'm not implementing yet is:
- Relationships often have a descriptor such as "
spouse", "husband", "wife", "parent", "child", "father", "mother". - Relationships often having a beginning and ending period.
- Relationships can be recurring, either at fixed or random intervals - think of things you have scheduled annually on your calendar, vs. random things that occur as part of life.
It should also be obvious that a particular record can be in relationship with many other records.
Constraints:
- For our purposes, circular relationship instances are not supported - we do not support the equivalent of the time travel concept where you can be your own grandparent.
- A record cannot be in relationship with itself.
Two tables are added:
EntityRelationship - This describes the allowable relationships between entities.RelationshipInstance - This describes a specific relationship between two records.
CREATE TABLE [dbo].[EntityRelationship](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ParentEntityID] [int] NOT NULL,
[ChildEntityID] [int] NOT NULL,
[Deleted] [bit] NOT NULL,
CONSTRAINT [PK_EntityRelationship] PRIMARY KEY CLUSTERED ...
ALTER TABLE [dbo].[EntityRelationship] _
WITH CHECK ADD CONSTRAINT [FK_EntityRelationship_Entity] FOREIGN KEY([ParentEntityID])
REFERENCES [dbo].[Entity] ([ID])
ALTER TABLE [dbo].[EntityRelationship] CHECK CONSTRAINT [FK_EntityRelationship_Entity]
ALTER TABLE [dbo].[EntityRelationship] WITH CHECK _
ADD CONSTRAINT [FK_EntityRelationship_Entity1] FOREIGN KEY([ChildEntityID])
REFERENCES [dbo].[Entity] ([ID])
and:
CREATE TABLE [dbo].[RelationshipInstance](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EntityRelationshipID] [int] NOT NULL,
[ParentInstanceID] [int] NOT NULL,
[ChildInstanceID] [int] NOT NULL,
[Deleted] [bit] NOT NULL,
CONSTRAINT [PK_RelationshipInstance] PRIMARY KEY CLUSTERED
ALTER TABLE [dbo].[RelationshipInstance] WITH CHECK ADD CONSTRAINT _
[FK_RelationshipInstance_EntityInstance] FOREIGN KEY([ParentEntityInstanceID])
REFERENCES [dbo].[EntityInstance] ([ID])
ALTER TABLE [dbo].[RelationshipInstance] WITH CHECK ADD CONSTRAINT _
[FK_RelationshipInstance_EntityInstance1] FOREIGN KEY([ChildEntityInstanceID])
REFERENCES [dbo].[EntityInstance] ([ID])
ALTER TABLE [dbo].[RelationshipInstance] WITH CHECK ADD CONSTRAINT _
[FK_RelationshipInstance_EntityRelationship] FOREIGN KEY([EntityRelationshipID])
REFERENCES [dbo].[EntityRelationship] ([ID])
Our schema now looks like this, where the top 3 tables are for managing the records (instances) and the bottom 3 tables manage the meta-model definitions.
This controller manages both the entity relationship definitions and the record relationship instances.
The API endpoints to manage the relationship definitions are:
[HttpGet("all")]
public object GetRelationshipDefinitions()
{
var data = rs.GetAllDefinitions(cfg, ts);
return data;
}
[HttpGet("Parent/{parentEntityId}")]
public object GetRelationshipDefinitionsOfParent(int parentEntityId)
{
var data = rs.GetRelationshipDefinitionsOfParent(cfg, ts, parentEntityId);
return data;
}
[HttpGet("Child/{childEntityId}")]
public object GetRelationshipDefinitionsOfChild(int childEntityId)
{
var data = rs.GetRelationshipDefinitionsOfChild(cfg, ts, childEntityId);
return data;
}
[HttpPost()]
public object Insert(Parameters parms)
{
var data = rs.InsertDefinition(cfg, ts, parms);
return data;
}
[HttpDelete("{entityRelationshipId}")]
public object DeleteParentChild(int entityRelationshipId)
{
rs.DeleteDefinition(cfg, ts, entityRelationshipId);
return NoContent();
}
[HttpDelete("{parentEntityId}/{childEntityId}")]
public object DeleteParentChild(int parentEntityId, int childEntityId)
{
rs.DeleteDefinition(cfg, ts, parentEntityId, childEntityId);
return NoContent();
}
Notice that there is no endpoint for updating a relationship between two entities. It either exists or it doesn't. Once it exists, you cannot change either the parent or the child because this would break the concept of what entities that relationship instance maps to for its specific parent/child records.
Relationship instances can be queried for:
- All instances - not a good idea, you may have millions unless you are filtering the resulting
dataset. - Child instances of a parent.
- Parent instances of a child.
And the relationship instances can be deleted. Again, updating a relationship instance is permitted.
The controller code for the relationship instances:
[HttpGet("instance/all")]
public object GetRelationshipInstances()
{
var data = rs.GetAllInstances(cfg, ts);
return data;
}
[HttpGet("instance/parent/{parentEntityId}")]
public object GetChildInstancesOfParent(int parentEntityId)
{
var data = rs.GetChildInstancesOfParent(cfg, ts, parentEntityId);
return data;
}
[HttpGet("instance/child/{childEntityId}")]
public object GetParentInstancesOfChild(int childEntityId)
{
var data = rs.GetParentInstancesOfChild(cfg, ts, childEntityId);
return data;
}
[HttpPost("instance")]
public object InsertInstance(Parameters parms)
{
var data = rs.InsertInstance(cfg, ts, parms);
return data;
}
[HttpDelete("instance/{entityRelationshipId}")]
public object DeleteInstance(int relationshipInstanceId)
{
rs.DeleteInstance(cfg, ts, relationshipInstanceId);
return NoContent();
}
[HttpDelete("instance/{parentInstanceId}/{childInstanceId}")]
public object DeleteInstance(int parentInstanceId, int childInstanceId)
{
rs.DeleteInstance(cfg, ts, parentInstanceId, childInstanceId);
return NoContent();
}
Similarly, the relationship service manages both the entity definitions and adding/deleting relationship instances. Again, there is no update function, as it makes no sense to change an existing relationship instance and it can potentially breaks the matching of the parent/child instance entities with the entity relationship definition. This code hardly warrants being in a service, but the point is that the controller should do little more than present a workflow of calls to lower-level service methods. In this case, there's no "workflow" in the controller other than calling the service. The point however is that this service might be utilized by another kind of controller, and we want to reduce copy & paste code by having all the necessary operations in the service, rather than the controller. As an aside, one often sees controller methods implemented more like a service rather than what, for a web API project, they really should be: an endpoint route handler that calls upon services to return the desired results. You can't re-use logic that's built into a controller's method. You can re-use a service.
Notice also that the return is not just the parent/child IDs, but also the entity names. This is more useful from the UI perspective, as it doesn't require the entity collection nor additional logic to fill in the entity name when we display the relationship definitions. Since the necessary joins are common to all of these operations, they are defined statically as:
private static Joins joinDefinition = new Joins()
{
new Join(
Constants.ENTITY_RELATIONSHIP,
Constants.ENTITY,
new FieldAliases() { { "Name", "Parent" } },
tableField: Constants.PARENT_ENTITY_ID),
new Join(
Constants.ENTITY_RELATIONSHIP,
Constants.ENTITY,
new FieldAliases() { { "Name", "Child" } },
tableField: Constants.CHILD_ENTITY_ID),
};
This join definition is passed in for the appropriate TableService method calls. The implementation of the RelationshipService then looks like this:
public Records GetAllDefinitions(IConfiguration cfg, ITableService ts)
{
var data = ts.GetAll(cfg, Constants.ENTITY_RELATIONSHIP, joins: joinDefinition);
return data;
}
public Records GetRelationshipDefinitionsOfParent
(IConfiguration cfg, ITableService ts, int parentEntityId)
{
var cond = new Conditions()
{
{new Condition(Constants.PARENT_ENTITY_ID, Condition.Op.Equals, parentEntityId) }
};
var data = ts.GetAll(cfg, Constants.ENTITY_RELATIONSHIP, cond, joinDefinition);
return data;
}
public Records GetRelationshipDefinitionsOfChild
(IConfiguration cfg, ITableService ts, int childEntityId)
{
var cond = new Conditions()
{
{new Condition(Constants.CHILD_ENTITY_ID, Condition.Op.Equals, childEntityId) }
};
var data = ts.GetAll(cfg, Constants.ENTITY_RELATIONSHIP, cond, joinDefinition);
return data;
}
public Record InsertDefinition(IConfiguration cfg, ITableService ts, Parameters parms)
{
var data = ts.Insert(cfg, Constants.ENTITY_RELATIONSHIP, parms, joinDefinition);
return data;
}
public void DeleteDefinition(IConfiguration cfg, ITableService ts, int entityRelationshipId)
{
ts.Delete(cfg, Constants.ENTITY_RELATIONSHIP, entityRelationshipId);
}
public void DeleteDefinition(IConfiguration cfg,
ITableService ts, int parentEntityId, int childEntityId)
{
var cond = new Conditions()
{
{new Condition(Constants.PARENT_ENTITY_ID, Condition.Op.Equals, parentEntityId) },
{new Condition(Constants.CHILD_ENTITY_ID, Condition.Op.Equals, childEntityId) }
};
var data = ts.GetSingle(cfg, Constants.ENTITY_RELATIONSHIP, cond);
ts.Delete(cfg, Constants.ENTITY_RELATIONSHIP, data[Constants.ID].ToInt());
}
Here, we'll again use Postman for demonstrating the API endpoints. However, as I mentioned at the beginning of this article, we're going to create a recipe application. We can create the entity and entity field definitions through the existing /sysadmin UI:
With the following fields:
Notice that we can use special characters like "#", have spaces between words, etc., because we're creating a meta-model rather than actual tables.
Here's a big red flag related to reserved field names. I had to change the field "Name" for the Recipe entity to "Recipe Name", as otherwise I get an error with the pivot statement because "Name" is already a field of the EntityField table! I will deal with this as part of the refactoring section in Part III.
Now we want to create the following relationships:
and we'll need to get the IDs of the entities from the database:
Now we can start inserting the entity relationships:
Recipe-Name:
curl --location --request POST 'http://localhost:8493/relationship' \
--header 'Content-Type: application/json' \
--data-raw '{"ParentEntityID": 13, "ChildEntityID": 11}'
Response:
{
"ID": 17,
"ParentEntityID": 13,
"ChildEntityID": 11,
"Deleted": false,
"Parent": "Recipe",
"Child": "Name"
}
Recipe-Ingredients:
curl --location --request POST 'http://localhost:8493/relationship' \
--header 'Content-Type: application/json' \
--data-raw '{"ParentEntityID": 13, "ChildEntityID": 14}'
Response:
{
"ID": 18,
"ParentEntityID": 13,
"ChildEntityID": 14,
"Deleted": false,
"Parent": "Recipe",
"Child": "Recipe Ingredients"
}
Recipe-Directions:
curl --location --request POST 'http://localhost:8493/relationship' \
--header 'Content-Type: application/json' \
--data-raw '{"ParentEntityID": 13, "ChildEntityID": 15}'
Response:
{
"ID": 19,
"ParentEntityID": 13,
"ChildEntityID": 15,
"Deleted": false,
"Parent": "Recipe",
"Child": "Recipe Directions"
}
Querying all entity relationships with /relationship/all, (and with /relationship/parent/13, since we have only defined one relationship set), we see:
[
{
"ID":17,
"ParentEntityID":13,
"ChildEntityID":11,
"Deleted":false,
"Parent":"Recipe",
"Child":"Name"
},
{
"ID":18,
"ParentEntityID":13,
"ChildEntityID":14,
"Deleted":false,
"Parent":"Recipe",
"Child":"Recipe Ingredients"
},
{
"ID":19,
"ParentEntityID":13,
"ChildEntityID":15,
"Deleted":false,
"Parent":"Recipe",
"Child":"Recipe Directions"
}
]
or with /relationship/child/15, we see:
[
{
"ID":19,
"ParentEntityID":13,
"ChildEntityID":15,
"Deleted":false,
"Parent":"Recipe",
"Child":"Recipe Directions"
}
]
The relationship service methods for managing relationship instances:
public Records GetAllInstances(IConfiguration cfg, ITableService ts)
{
var data = ts.GetAll(cfg, Constants.RELATIONSHIP_INSTANCE, joins: joinInstance);
return data;
}
public Records GetChildInstancesOfParent
(IConfiguration cfg, ITableService ts, int parentInstanceId)
{
var cond = new Conditions()
{
{new Condition(Constants.PARENT_ENTITY_INSTANCE_ID,
Condition.Op.Equals, parentInstanceId) }
};
var data = ts.GetAll(cfg, Constants.RELATIONSHIP_INSTANCE, cond, joinInstance);
return data;
}
public Records GetParentInstancesOfChild
(IConfiguration cfg, ITableService ts, int childInstanceId)
{
var cond = new Conditions()
{
{new Condition(Constants.CHILD_ENTITY_INSTANCE_ID, Condition.Op.Equals, childInstanceId) }
};
var data = ts.GetAll(cfg, Constants.RELATIONSHIP_INSTANCE, cond, joinInstance);
return data;
}
public Record InsertInstance(IConfiguration cfg, ITableService ts, Parameters parms)
{
var data = ts.Insert(cfg, Constants.RELATIONSHIP_INSTANCE, parms, joins: joinInstance);
return data;
}
public void DeleteInstance(IConfiguration cfg, ITableService ts, int relationshipInstanceId)
{
ts.Delete(cfg, Constants.RELATIONSHIP_INSTANCE, relationshipInstanceId);
}
public void DeleteInstance(IConfiguration cfg,
ITableService ts, int parentInstanceId, int childInstanceId)
{
var instRel = ts.GetSingle(cfg, Constants.RELATIONSHIP_INSTANCE, new Conditions()
{
new Condition(Constants.PARENT_ENTITY_INSTANCE_ID, Condition.Op.Equals, parentInstanceId),
new Condition(Constants.CHILD_ENTITY_INSTANCE_ID, Condition.Op.Equals, childInstanceId)
});
var id = instRel[Constants.ID].ToInt();
ts.Delete(cfg, Constants.RELATIONSHIP_INSTANCE, id);
}
Notice how we have a join that we use for instances:
private static Joins joinInstance = new Joins()
{
new Join(
Constants.RELATIONSHIP_INSTANCE,
Constants.ENTITY_RELATIONSHIP),
new Join(
Constants.ENTITY_RELATIONSHIP,
Constants.ENTITY,
new FieldAliases() { { "Name", "Parent" } },
tableField: Constants.PARENT_ENTITY_ID),
new Join(
Constants.ENTITY_RELATIONSHIP,
Constants.ENTITY,
new FieldAliases() { { "Name", "Child" } },
tableField: Constants.CHILD_ENTITY_ID),
};
This gives us the entity name along with the parent/child instances. This is for the convenience of the client.
First, using the /record editor, and because I'm lazy and don't want to create the full set of ingredients and directions, I'm going to create two recipes with one ingredient and one direction step each. So here's what we have in the instance tables:
Now, since I don't have a UI for this yet (the next sections, and you can see why a UI is so useful), here's a query to figure out what we're doing:
select e.Name, ei.ID EntityIntanceID, ef.Name, efv.Value from EntityInstance ei
join Entity e on e.ID = ei.EntityID
join EntityFieldValue efv on efv.EntityInstanceID = ei.ID and _
efv.Value is not null and efv.Value != ''
join EntityField ef on ef.ID = efv.EntityFieldID
where e.Name like 'Recipe%'
and ef.Name in ('Name', 'Recipe Name', 'Ingredient', 'Instruction')
which gives us these instance relationships:
We know our entity relationship IDs from earlier:
- 18: Recipe - Recipe Ingredients
- 19: Recipe - Recipe Directions
So now we can insert the relationships using Postman -- I'm only going to show one example:
cURL:
curl --location --request POST 'http://localhost:8493/relationship/instance' \
--header 'Content-Type: application/json' \
--data-raw '{"EntityRelationshipID": 18, "ParentEntityInstanceID": 19,
"ChildEntityInstanceID": 21}'
Response:
{
"ID": 2,
"EntityRelationshipID": 17,
"ParentEntityInstanceID": 19,
"ChildEntityInstanceID": 21,
"Deleted": false,
"Parent": "Recipe",
"Child": "Recipe Ingredients"
}
I should have named the entities Recipe Ingredients and Recipe Directions singular!
Done!
Now, when we do a GET /relationship/instance/all
We see two recipes, each with one ingredient and one direction.
[
{"ID":6,"EntityRelationshipID":18,"ParentEntityInstanceID":19,
"ChildEntityInstanceID":21,"Deleted":false,"Parent":"Recipe","Child":"Recipe Ingredient"},
{"ID":7,"EntityRelationshipID":19,"ParentEntityInstanceID":19,
"ChildEntityInstanceID":23,"Deleted":false,"Parent":"Recipe","Child":"Recipe Direction"},
{"ID":8,"EntityRelationshipID":18,"ParentEntityInstanceID":20,
"ChildEntityInstanceID":22,"Deleted":false,"Parent":"Recipe","Child":"Recipe Ingredient"},
{"ID":9,"EntityRelationshipID":19,"ParentEntityInstanceID":20,
"ChildEntityInstanceID":24,"Deleted":false,"Parent":"Recipe","Child":"Recipe Direction"}
]
And we can get the parents of a child with, for example, GET /relationship/instance/child/24
[
{"ID":9,"EntityRelationshipID":19,"ParentEntityInstanceID":20,
"ChildEntityInstanceID":24,"Deleted":false,"Parent":"Recipe","Child":"Recipe Direction"}
]
Or the children of a parent with GET /relationship/instance/parent/20
[
{"ID":8,"EntityRelationshipID":18,"ParentEntityInstanceID":20,
"ChildEntityInstanceID":22,"Deleted":false,"Parent":"Recipe","Child":"Recipe Ingredient"},
{"ID":9,"EntityRelationshipID":19,"ParentEntityInstanceID":20,
"ChildEntityInstanceID":24,"Deleted":false,"Parent":"Recipe","Child":"Recipe Direction"}
]
As in Part I, I'm not bothering to show you the TypeScript code -- you can look at it yourself. It's not the most elegant thing as, for the purpose of this series of articles, the UI is secondary.
The UI is very simple and very dumb -- you select the entity on the left as the "parent", and then add entities on the right as children. The child entity is a dropdown:
There is no testing for duplicate child names, same parent-child name, nor circular relationships.
Now we're going to work with the existing "records" UI to introduce the concept of displaying relationships of the instance's entity to its parents and children, recursively. The really interesting part will be when we implement selecting a record at different levels of the hierarchy.
What would be useful is to have an API endpoint that returns the hierarchy of entities regardless of what entity we start with. This way, we can identify the top-level entity/entities (and the ordering of child entities when there is more than one child of a parent, but this is not implemented here.) So regardless of what entity is selected on the left:
We should always see a consistent hierarchy. And of course, in a complex graph, there can be multiple top-level entities. I'm refraining from using the word "root" because the roots of a tree are at the bottom of the tree, and we want to display the "root" (aka top-level entities) at the top of the UI! Also note that various use-cases have not been adequately tested at this point! And as much as I'm happy to avoid it, at this point, it would actually be useful to have a C# model so we can create the structure in the code in a readable manner. The code for this is going into the RelationshipService so it's more suited for integration testing.
public interface IEntityRelationship
{
List<EntityRelationship> Children { get; set; }
}
public class TopLevelEntityRelationship : IEntityRelationship
{
[JsonProperty(Order = 0)]
public int ID { get; set; }
[JsonProperty(Order = 1)]
public string Parent { get; set; }
[JsonProperty(Order = 2)]
public int ParentEntityID { get; set; }
[JsonProperty(Order = 3)]
public List<EntityRelationship> Children { get; set; }
public TopLevelEntityRelationship(EntityRelationship er)
{
ID = er.ID;
Parent = er.Parent;
ParentEntityID = er.ParentEntityID;
}
public TopLevelEntityRelationship Clone()
{
return MemberwiseClone() as TopLevelEntityRelationship;
}
}
public class EntityRelationship : IEntityRelationship
{
[JsonProperty(Order = 0)]
public int ID { get; set; }
[JsonProperty(Order = 1)]
public string Parent { get; set; }
[JsonProperty(Order = 2)]
public string Child { get; set; }
[JsonProperty(Order = 3)]
public int ParentEntityID { get; set; }
[JsonProperty(Order = 4)]
public int ChildEntityID { get; set; }
[JsonProperty(Order = 5)]
public List<EntityRelationship> Children {get;set;}
public EntityRelationship Clone()
{
return MemberwiseClone() as EntityRelationship;
}
}
Why do we have a clone method? The reason is that if we don't clone the instance of an EntityRelationship, we can up having items in the Children collection being exact object references to their parents. This actually happens with the top-level entities, as these will actually be parent-child EnityRelationship instances that do not have parents, however, we want them to appear in the child list. If we don't clone the instance, the JSON serializer has conniption fits. First, we'll notice the error "Self referencing loop detected", which is quite legitimate. If we add options to the serializer such as:
ReferenceLoopHandling = ReferenceLoopHandling.Ignore,
PreserveReferencesHandling = PreserveReferencesHandling.Objects
then we get what we want, but with $id and $ref keys in the JSON, and the $ref in particular has to be decoded by the client to get the properties of the object it is referencing. So to avoid all this and provide reasonable JSON to the client, we clone the child EntityRelationship instances so they are new objects.
Creating the hierarchy "graph" has a certain complexity to it.
Given (note that Name is a child of both Contact and Recipe):
[
{
"ID": 17,
"ParentEntityID": 13,
"ChildEntityID": 11,
"Deleted": false,
"Parent": "Recipe",
"Child": "Name"
},
{
"ID": 18,
"ParentEntityID": 13,
"ChildEntityID": 14,
"Deleted": false,
"Parent": "Recipe",
"Child": "Recipe Ingredient"
},
{
"ID": 19,
"ParentEntityID": 13,
"ChildEntityID": 15,
"Deleted": false,
"Parent": "Recipe",
"Child": "Recipe Direction"
},
{
"ID": 22,
"ParentEntityID": 12,
"ChildEntityID": 11,
"Deleted": false,
"Parent": "Contact",
"Child": "Name"
}
]
Here are some questions:
- If we start at the topmost element, do we only recursively show the children?
- What if a child has a parent that is another pathway to that child entity?
- Should that pathway, which leads to a different top-level entity, also be shown?
- And what if along the way, those parent entities have other child branches, and do we resolve those again starting at question #2?
- If we start at an entity, such as
Name, that has two pathways (up to Recipe and up to Contact), do we show both? - Does this depend on some abstraction, such as the "domain" in which a relationship exists?
Are here my answers:
- If we start at the topmost element, do we only recursively show the children? NO
- What if a child has a parent that is another pathway to that child entity? AT THE MOMENT, SHOW THAT PATH AS WELL.
- Should that pathway, which leads to a different top-level entity, also be shown? YES
- And what if along the way, those parent entities have other child branches, and do we resolve those again starting at question #2? YES
- If we start at an entity, such as
Name, that has two pathways (up to Recipe and up to Contact), do we show both? YES - Does this depend on some abstraction, such as the "domain" in which a relationship exists? FUTURE FEATURE
To test this, I'm going add two more entity definitions, "Task" and "Subtask" which have a parent-child relationship, which adds to the above JSON:
{
"ID": 23,
"ParentEntityID": 17,
"ChildEntityID": 18,
"Deleted": false,
"Parent": "Task",
"Child": "Subtask"
}
Unfortunately, my answers above result in a complex implementation.
public List<TopLevelEntityRelationship> GetHierarchy
(IConfiguration cfg, ITableService ts, int entityId)
{
var models = GetAllDefinitions(cfg, ts).SerializeTo<EntityRelationship>();
var hierarchy = FindTopLevelParents(models);
var flattenedHierarchy = new List<int>[hierarchy.Count];
var inUseHierarchies = new TopLevelEntityRelationship[hierarchy.Count];
hierarchy.ForEachWithIndex((h, idx) =>
{
var flatIds = new List<int>() { h.ParentEntityID };
PopulateChildrenOfParent(h, models, h.ParentEntityID, flatIds);
flattenedHierarchy[idx] = flatIds;
});
flattenedHierarchy.ForEachWithIndex((f, idx) =>
{
if (f.Any(id => id == entityId))
{
inUseHierarchies[idx] = hierarchy[idx];
}
});
inUseHierarchies.ForEachWithIndex((h, idx) =>
GetIntersections(idx, hierarchy, flattenedHierarchy, inUseHierarchies), h => h != null);
var actualInUse = inUseHierarchies.Where(h => h != null).ToList();
return actualInUse;
}
private void GetIntersections(
int hidx,
List<TopLevelEntityRelationship> hierarchy,
List<int>[] flattenedHierarchy,
TopLevelEntityRelationship[] inUseHierarchies)
{
flattenedHierarchy.ForEachWithIndex((f, idx) =>
{
if (idx != hidx && inUseHierarchies[idx] == null)
{
if (flattenedHierarchy[idx].Intersect(flattenedHierarchy[hidx]).Any())
{
if (!inUseHierarchies.Where(h => h != null).Any(h => h == hierarchy[idx]))
{
inUseHierarchies[idx] = hierarchy[idx];
GetIntersections(idx, hierarchy, flattenedHierarchy, inUseHierarchies);
}
}
}
});
}
private List<TopLevelEntityRelationship> FindTopLevelParents(List<EntityRelationship> models)
{
var parentEntityIds = models.DistinctBy(m => m.ParentEntityID).ToList();
var topLevelParents = parentEntityIds.NotIn
(models, p => p.ParentEntityID, m => m.ChildEntityID);
var topLevelEntities = topLevelParents.Select(p =>
new TopLevelEntityRelationship(models.First
(m => m.ParentEntityID == p.ParentEntityID))).ToList();
return topLevelEntities;
}
private void PopulateChildrenOfParent(IEntityRelationship parent,
List<EntityRelationship> models, int parentId, List<int> flatIds)
{
parent.Children = models.Where(m => m.ParentEntityID == parentId).Select
(m => m.Clone()).ToList();
flatIds.AddRange(parent.Children.Select(c => c.ChildEntityID));
parent.Children.ForEach(c => PopulateChildrenOfParent(c, models, c.ChildEntityID, flatIds));
}
Notice the mapping of the children to cloned children with .Select(m => m.Clone()
Now, regardless of whether we ask for the known top level parent or a child in the hierarchy:
GET /relationship/hierarchy/13
GET /relationship/hierarchy/11
The result for the recipe model we've created is:
[
{
"ID": 17,
"Parent": "Recipe",
"ParentEntityID": 13,
"Children": [
{
"ID": 17,
"Parent": "Recipe",
"Child": "Name",
"ParentEntityID": 13,
"ChildEntityID": 11,
"Children": []
},
{
"ID": 18,
"Parent": "Recipe",
"Child": "Recipe Ingredient",
"ParentEntityID": 13,
"ChildEntityID": 14,
"Children": []
},
{
"ID": 19,
"Parent": "Recipe",
"Child": "Recipe Direction",
"ParentEntityID": 13,
"ChildEntityID": 15,
"Children": []
}
]
},
{
"ID": 22,
"Parent": "Contact",
"ParentEntityID": 12,
"Children": [
{
"ID": 22,
"Parent": "Contact",
"Child": "Name",
"ParentEntityID": 12,
"ChildEntityID": 11,
"Children": []
}
]
}
]
Notice how we always get the Contact entity as well because of the intersection of the Name entity between Recipe-Name and Contact-Name.
Conversely, if we ask for the hierarchy of Task or Subtask:
GET /relationship/hierarchy/17
GET /relationship/hierarchy/18
We only get:
[
{
"ID": 23,
"Parent": "Task",
"ParentEntityID": 17,
"Children": [
{
"ID": 23,
"Parent": "Task",
"Child": "Subtask",
"ParentEntityID": 17,
"ChildEntityID": 18,
"Children": []
}
]
}
]
There are no intersections between entities in the Task/Subtask hierarchy and the Recipe or Contact hierarchy.
Before proceeding with the UI, we need a couple additional endpoints:
- Return child records of a particular parent
- Return parent records of a particular child
The reason we need these endpoints is so that:
- When the user selects a parent record, we can display all the associated child records for each child-relationship entity.
- When the user select a child record, we can display the associated (usually one) parent record for each parent-relationship entity.
Both endpoints return the record instances has the expected, "pivoted", records. The implementation is not optimized for performance! This initial pass is simply to get something working.
[HttpGet("instance/parent/{parentInstanceId}")]
public object GetChildRecordsOfParent(int parentInstanceId)
{
var childRelationships = rls.GetChildInstancesOfParent(cfg, ts, parentInstanceId);
var childRecords = new Dictionary<string, List<Record>>();
childRelationships
.Select(kvp => kvp[Constants.CHILD].ToString())
.Distinct()
.ForEach(child => childRecords[child] = new List<Record>());
childRelationships.ForEach(rel =>
{
var childEntityName = rel[Constants.CHILD].ToString();
var rec = rs.GetSingle(cfg, ts, childEntityName,
rel[Constants.CHILD_ENTITY_INSTANCE_ID].ToInt());
childRecords[childEntityName].Add(rec);
});
return ser.Serialize(childRecords);
}
This is the mirror image of the previous code -- the code duplication could be avoided but the both API endpoints will end up getting rewritten when I work out the joins necessary with the RelationshipInstance table in conjunction with the pivot code.
[HttpGet("instance/child/{childInstanceId}")]
public object GetParentRecordsOfChild(int childInstanceId)
{
var parentRelationships = rls.GetParentInstancesOfChild(cfg, ts, childInstanceId);
var parentRecords = new Dictionary<string, List<Record>>();
parentRelationships
.Select(kvp => kvp[Constants.PARENT].ToString())
.Distinct()
.ForEach(parent => parentRecords[parent] = new List<Record>());
parentRelationships.ForEach(rel =>
{
var parentEntityName = rel[Constants.PARENT].ToString();
var rec = rs.GetSingle(cfg, ts, parentEntityName,
rel[Constants.PARENT_ENTITY_INSTANCE_ID].ToInt());
parentRecords[parentEntityName].Add(rec);
});
return ser.Serialize(parentRecords);
}
GET /RecordRelationship/instance/parent/19
This returns the child instances of the Recipe instance with ID 19 - the guacamole recipe:
{
"Recipe Ingredient": [
{
"ID": 21,
"Ingredient": "Avocado",
"Quantity": "1"
}
],
"Recipe Direction": [
{
"ID": 23,
"Step #": "1",
"Instruction": "Mash avocado"
}
]
}
GET /RecordRelationship/instance/child/22
This returns the parent instances (in this case one) for the ingredient "garbanzo beans" in the hummos recipe:
{
"Recipe": [
{
"ID": 20,
"Recipe Name": "Hummos",
"Culture": "Middle East",
"Preparation Time": "",
"Cooking Time": ""
}
]
}
For a general purpose editing tool, with the information we currently have available, the layout is going to be dynamically created as grids in "rows" based on the depth:
- Row 1 contains the grids for the top level entities. In the JSON example above, this would be just "
Recipe." - Row 2 contains the child grids. In the JSON example above, this woud be "
Name", "Recipe Ingredient", and "Recipe Direction." - Row 3 contains the grandchild grids. In the JSON example above, there are no grandchildren.
- etc.
The endpoint we'll use for UI is /recordRelationship. On the left, we can select the entity that we to "start with", as in, for which of these entities do we want to see all the instances? Note that not having implemented paging yet, don't try to stress test the system by creating hundreds of entity instances! Regardless, the point of this is that the UI will display the same structure, but the initial grid that gets loaded with data depends on the entity selected.
In the screenshots below, I have filled out the two recipes, and I've also deleted the relationship to the "Name" entity and create a "Recipe Source" entity. I also removed a couple other entities that are not relevant to this demonstration. All the editing has been done through the UI -- no behind-the-scenes SQL or Postman calls anymore! The hummos recipe is from memory, so don't try this at home - I'm sure I don't have the quantity of ingredients right. First, we see the usual list of entities:
Clicking on Recipe, we see (I'm shrinking the width of the browser window here intentionally to try and comply with screenshot guidelines):
Notice how our two recipes are populated. Clicking on any entity automatically populates the records for that entity. Also keep in mind that this UI is generated completely automatically from the entity relationship definitions, and the columns are also determined from the entity field definitions.
When I click on a recipe, I see the records for the ingredients, directions, and the source of the recipe automatically populated for that recipe (vertically truncated):
Now here's the more interesting thing. Let's start with ingredients. We see the ingredients for all the recipes:
Let's search for "Garlic", and we see two places where garlic is used:
If I click on one of them, I see the recipe in which it is used:
And the records for the other children, because there is only one parent for this specific ingredient, auto-populate. In Part III, I will take this a step further an introduce "key" fields -- the idea being that you can filter by unique key field values, so that "Garlic" appears only once, and when you click on it, all the parent entities (recipes in our case) that reference that child entity instance (ingredients in our case) are displayed in the parent grid. Similarly, you could do a reverse lookup of all the recipes from a particular source, for example.
One caveat of the current UI implementation is that I haven't tested it with more than a parent-child relationship -- in other words, three or more level relationships are untested, and when I was coding the built-in UI, I believe there are some limitations at the moment.
A few extra things I did in this iteration.
At this point, the API is getting complicated enough that it deserves an API page. Using Microsoft's tutorial Get started with Swashbuckle and ASP.NET Core it was a simple matter to add Swagger documentation. So now, the endpoint /swagger displays the API and you can play with it. For example:
and we see:
Nifty!
While Swagger does a great job of formatting the JSON return, I still find myself simply putting the GET endpoint into Chrome. There's something easier on my eyes to see the JSON in black and white, and to not have to scroll, so I've added a serialization service that formats the returned JSON.
public class PrettyFormat : ISerializationService
{
public object Serialize(object data)
{
string json = JsonConvert.SerializeObject(data, Formatting.Indented);
var cr = new ContentResult()
{ Content = json, ContentType = "text/json", StatusCode = 200 };
return cr;
}
}
I suppose this ought to be handled in a custom output formatter similar to what is described here, but I haven't implemented services.AddMvc, so that's not an option right now. And then, in .NET Core 3, there's three new ways of doing this, and my "oh geez, this is too complicated for something so simple" response kicks in. But then again, maybe this is what I'm looking for with regards having ASP.NET Core serve my built in pages, rather than the kludge I currently have. Reminder to self -- AddControllers seems to be the thing I want.
As an aside, it's starting to get annoying to add all the dependency injected services into the controller! There's a post Dealing with "Too Many Dependencies" problem but the author does not present a solution.
There's several built-in web pages at this point, so it's useful to have a home page:
At this point, even using the cheesy grids and built-in UI, we now have a usable general purpose, metadata driven (as in, "adaptive" and "hierarchical") tool for creating "knowledge management." According to Visual Studio's code metrics:
This has been accomplished in less than 600 lines of back-end executable code. Personally, I find that impressive. Not to mention that all this is accomplished six database tables.
Part III will not be published as quickly as Part II! I had already written most of Part II when I actually published Part I, but that is not the case for Part III! I might break up Part III into smaller pieces depending on how the content starts to fill in.
public Records GetAll(IConfiguration cfg, ITableService ts, string entity) will fail if the entity does not have any fields on which to pivot.
Deleting a record in the record UI will cause the record relationship UI to blow up because the relationship instance of that record wasn't deleted.
The UI probably doesn't handle relationship depths > 2.
I had to change the field "Name" for the Recipe entity to "Recipe Name", as otherwise I get an error with the pivot statement because "Name" is already a field of the EntityField table!
- I ignored the refactoring of how the .html/.js/.ts files are loaded, so this ought to be done in Part III.
- Circular relationship instance constraint
- Self-relationship constraint
- Async instead of sync endpoint handlers
- The ordering of child entities when there is more than one child of a parent
- Paging of data
- Performance: work out the joins necessary with the
RelationshipInstance table in conjunction with the pivot code
In Part III, I'm planning on focusing on the UI generation, specifically additional entity and entity field metadata as described in the diagram in Part I, which would be:
- Entity field labels for the UI
- Discrete controls instead of grids for everything
- Lookups associated with entity fields
- Something a bit better looking than jsGrid
This will also touch on "pluggable" services in .NET Core 3, possibly as described here, as certain aspects of the above, such as format/interpolation, validation, and range/lookup should be handled as a plug-in service because these start to tread upon application specific requirements and often are not static algorithms and data but dynamic based on the context of other data. So it makes sense to me to start looking at these pieces of the puzzle:
Besides the main features in the gloriosky diagram from Part I, I have these notes:
- Descriptors for relationships
- Temporal aspect of relationship
- parent-child ordinality: 1 or many
- There is no testing for duplicate child names, same parent-child name, nor circular relationships.
- Auto-select up the parent hierarchy and down the child hierarchy if there is only one record in the current relationship instance collection.
- Logical grouping of entities -- domains. Can/how should entities cross domains?
That's it for now!
- 6th April, 2021: Initial version
