Click here to Skip to main content
14,977,142 members
Articles / Programming Languages / Typescript
Article
Posted 6 Apr 2021

Stats

2K views
50 downloads
6 bookmarked

Adaptive Hierarchical Knowledge Management - Part II

Rate me:
Please Sign up or sign in to vote.
5.00/5 (5 votes)
6 Apr 2021CPOL22 min read
Adding relationships and relationship instance management - record hierarches
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

Introduction

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:

Image 1

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, Deleted
  • Entity should be unique for Name, Deleted
  • EntityInstance should be unique for InstanceID, Deleted
  • EntityFieldValue should be unique for InstanceID, EntityID, EntityFieldID, Deleted

Indexing:

  • Indexing needs to be implemented for performance

About the Download

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:

JavaScript
"ConnectionStrings": {
  "DefaultConnection": "[your connection string]"
} 

Refactoring

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:

C#
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.

Naming Conventions

Moving forward, the following terms will have the following meanings.

Meta-Model Naming Conventions

  • 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 Instance Naming Conventions

  • 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.

Serving the Web Pages Properly

I'm deferring this to Part III. It just seems low priority!

Constraints

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.
    SQL
    ALTER TABLE Entity ADD UNIQUE (Name, Deleted);
  • EntityField should be unique for EntityID, Name, Deleted.
    SQL
    ALTER TABLE EntityField ADD UNIQUE (Name, EntityID, Deleted);
  • EntityFieldValue should be unique for EntityInstanceID, EntityID, EntityFieldID, Deleted.
    SQL
    ALTER TABLE EntityFieldValue ADD UNIQUE _
    (EntityInstanceID, EntityID, EntityFieldID, Deleted);

Indexing

The following indices have been added:

SQL
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 Should be Implemented with Interfaces for their Public Methods

C#
services.AddSingleton<ITableService, TableService>();
services.AddSingleton<IRecordService, RecordService>();
services.AddSingleton<IRelationshipService, RelationshipService>();

Done! Not going to bore you with the interface code.

Joins

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:

C#
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:

C#
public string GetJoins()
{
  string ret = String.Empty;

  if (Count > 0)
  {
    List<string> joins = new List<string>();
    var joinAliases = GetJoinAliases();

    this.ForEachWithIndex((j, idx) =>
    {
      // Use the override if it exists, otherwise the default is the "with" table name + ID
      var tableFieldName = j.TableField ?? $"{j.WithTable}ID";
      var alias = joinAliases[idx].alias;
      var joinType = joinTemplate[j.Type];

      // Find the join table alias.
      // Currently this functionality is limited to one table with which we join other tables.
      // To fix this, the dictionary needs to have some "key" 
     // (such as the qualifier) that determines which of multiple joins
      // to continue joining with. Too complicated to set up right now.

      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}";

    // If we have an alias for the WithTable because it's part of a join, use it.
    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:

SQL
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:

C#
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.

Entity and Record Relationships

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:

  1. Relationships often have a descriptor such as "spouse", "husband", "wife", "parent", "child", "father", "mother".
  2. Relationships often having a beginning and ending period.
  3. 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.

Schema

Two tables are added:

  1. EntityRelationship - This describes the allowable relationships between entities.
  2. RelationshipInstance - This describes a specific relationship between two records.
SQL
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:

SQL
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.

Image 2

The Relationship Controller

This controller manages both the entity relationship definitions and the record relationship instances.

Entity Relationship Definitions

The API endpoints to manage the relationship definitions are:

C#
/// <summary>
/// Returns all parent-child relationship definitions.
/// </summary>
[HttpGet("all")]
public object GetRelationshipDefinitions()
{
  // TODO: This needs to support pagination.
  var data = rs.GetAllDefinitions(cfg, ts);

  return data;
}

/// <summary>
/// Returns the definitions for children having a relationship 
/// with the specified parent, as parent-child records.
/// </summary>
[HttpGet("Parent/{parentEntityId}")]
public object GetRelationshipDefinitionsOfParent(int parentEntityId)
{
  var data = rs.GetRelationshipDefinitionsOfParent(cfg, ts, parentEntityId);

  return data;
}

/// <summary>
/// Returns the definitions for parents having a relationship 
/// with the specified child, as parent-child records.
/// </summary>
[HttpGet("Child/{childEntityId}")]
public object GetRelationshipDefinitionsOfChild(int childEntityId)
{
  var data = rs.GetRelationshipDefinitionsOfChild(cfg, ts, childEntityId);

  return data;
}

/// <summary>
/// Creates a parent-child relationship definition between two entities.
/// </summary>
[HttpPost()]
public object Insert(Parameters parms)
{
  var data = rs.InsertDefinition(cfg, ts, parms);

  return data;
}

/// <summary>
/// Deletes a specific parent-child relationship definition given the entity relationship ID.
/// </summary>
[HttpDelete("{entityRelationshipId}")]
public object DeleteParentChild(int entityRelationshipId)
{
  rs.DeleteDefinition(cfg, ts, entityRelationshipId);

  return NoContent();
}

/// <summary>
/// Deletes a specific parent-child relationship definition 
/// given the parent and child entity ID's.
/// </summary>
[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

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:

C#
[HttpGet("instance/all")]
public object GetRelationshipInstances()
{
  // TODO: This REALLY needs to support pagination.
  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();
}

The Relationship Service

Entity Relationship Definitions

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:

C#
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:

C#
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());
}

Usage Example

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:

Image 3

With the following fields:

Image 4

Image 5

Image 6

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:

Image 7

and we'll need to get the IDs of the entities from the database:

Image 8

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:

JavaScript
{
  "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:

JavaScript
{
  "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:

JavaScript
{
  "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:

JavaScript
[
  {
    "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:

JavaScript
[
  {
    "ID":19,
    "ParentEntityID":13,
    "ChildEntityID":15,
    "Deleted":false,
    "Parent":"Recipe",
    "Child":"Recipe Directions"
  }
]

Relationship Instances

The relationship service methods for managing relationship instances:

C#
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:

C#
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.

Usage Example

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:

Image 9

Image 10

Image 11

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:

SQL
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:

Image 12

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:

JavaScript
{
  "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!

Image 13

Done!

Now, when we do a GET /relationship/instance/all

We see two recipes, each with one ingredient and one direction.

JavaScript
[
  {"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

JavaScript
[
  {"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

JavaScript
[
  {"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"}
]

User Interface for Defining Entity Relationships

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.

Image 14

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:

Image 15

There is no testing for duplicate child names, same parent-child name, nor circular relationships.

User Interface for Relationship Instances and Their Records

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.

Get Entity Relationship Hierarchy API

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:

Image 16

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.

C#
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:

C#
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.

Discussion

Creating the hierarchy "graph" has a certain complexity to it.

Given (note that Name is a child of both Contact and Recipe):

JavaScript
[
  {
    "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:

  1. If we start at the topmost element, do we only recursively show the children?
  2. What if a child has a parent that is another pathway to that child entity?
  3. Should that pathway, which leads to a different top-level entity, also be shown?
  4. And what if along the way, those parent entities have other child branches, and do we resolve those again starting at question #2?
  5. If we start at an entity, such as Name, that has two pathways (up to Recipe and up to Contact), do we show both?
  6. Does this depend on some abstraction, such as the "domain" in which a relationship exists?

Are here my answers:

  1. If we start at the topmost element, do we only recursively show the children? NO
  2. What if a child has a parent that is another pathway to that child entity? AT THE MOMENT, SHOW THAT PATH AS WELL.
  3. Should that pathway, which leads to a different top-level entity, also be shown? YES
  4. And what if along the way, those parent entities have other child branches, and do we resolve those again starting at question #2? YES
  5. 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
  6. 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:

JavaScript
{
    "ID": 23,
    "ParentEntityID": 17,
    "ChildEntityID": 18,
    "Deleted": false,
    "Parent": "Task",
    "Child": "Subtask"
  }

Unfortunately, my answers above result in a complex implementation.

Implementation

C#
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];

  // Get the hierarchy for each top-level entity and the 
  // flattened collection of parent/child ID's.
  hierarchy.ForEachWithIndex((h, idx) =>
  {
    // We start with the top level parent ID and add the child ID's.
    var flatIds = new List<int>() { h.ParentEntityID };
    PopulateChildrenOfParent(h, models, h.ParentEntityID, flatIds);
    flattenedHierarchy[idx] = flatIds;
  });

  // Which hierarchies are in use given the entityID passed in?
  flattenedHierarchy.ForEachWithIndex((f, idx) =>
  {
    if (f.Any(id => id == entityId))
    {
      inUseHierarchies[idx] = hierarchy[idx];
    }
  });

  // Which heirarchies intersect the ones in use?
  // Once we add an intersected hierarchy, 
  // we have to also check what hierarchies it also intersects.
  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) =>
  {
    // Ignore self and already in-use hierarchies, 
   // so we only test a hierarchy not currently in use.
    if (idx != hidx && inUseHierarchies[idx] == null)
    {
      // If there are any intersections of entity ID's 
     // between an unused hierarchy and the one that is already in use...
      if (flattenedHierarchy[idx].Intersect(flattenedHierarchy[hidx]).Any())
      {
        // And we haven't already added it to the collection of in-use (object reference)...
        if (!inUseHierarchies.Where(h => h != null).Any(h => h == hierarchy[idx]))
        {
          // Then add this hierarchy, as it has an entity 
         // that intersects with an already in-use hierarchy.
          inUseHierarchies[idx] = hierarchy[idx];

          // Recurse, so we test the newly added hierarcy to see what intersections it may have.
          GetIntersections(idx, hierarchy, flattenedHierarchy, inUseHierarchies);
        }
      }
    }
  });
}

private List<TopLevelEntityRelationship> FindTopLevelParents(List<EntityRelationship> models)
{
  // Top level entities are those entities that do not occur as children in other relationships.
  var parentEntityIds = models.DistinctBy(m => m.ParentEntityID).ToList();
  var topLevelParents = parentEntityIds.NotIn
                       (models, p => p.ParentEntityID, m => m.ChildEntityID);
  
  // model.First because we can have multiple parent-child relationships 
 // with the top level entity as the parent, and we don't care which one.
  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)
{
  // Given a parent, find all the relationships where this parent is referenced.
  parent.Children = models.Where(m => m.ParentEntityID == parentId).Select
                   (m => m.Clone()).ToList();
  flatIds.AddRange(parent.Children.Select(c => c.ChildEntityID));

  // Recurse into grandchildren...
  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:

JavaScript
[
  {
    "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": []
      }
    ]
  }
]

Image 17

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:

JavaScript
[
  {
    "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.

Returning the Child/Parent Records of a Specific Parent/Child Record

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.

Returning all Child Records of a Parent

C#
/// <summary>
/// Returns the child records, grouped by child entity, of the specified parent instance ID.
/// </summary>
[HttpGet("instance/parent/{parentInstanceId}")]
public object GetChildRecordsOfParent(int parentInstanceId)
{
  var childRelationships = rls.GetChildInstancesOfParent(cfg, ts, parentInstanceId);
  var childRecords = new Dictionary<string, List<Record>>();

  // Create the lists for each unique child.
  childRelationships
    .Select(kvp => kvp[Constants.CHILD].ToString())
    .Distinct()
    .ForEach(child => childRecords[child] = new List<Record>());

  // This is not very efficient because we're doing a query per child ID.
  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);
}

Returning all Parent Records of a Child

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.

C#
/// <summary>
/// Returns the parent records, grouped by parent entity, of the specified child instance ID.
/// </summary>
[HttpGet("instance/child/{childInstanceId}")]
public object GetParentRecordsOfChild(int childInstanceId)
{
  var parentRelationships = rls.GetParentInstancesOfChild(cfg, ts, childInstanceId);
  var parentRecords = new Dictionary<string, List<Record>>();

  // Create the lists for each unique child.
  parentRelationships
    .Select(kvp => kvp[Constants.PARENT].ToString())
    .Distinct()
    .ForEach(parent => parentRecords[parent] = new List<Record>());

    // This is not very efficient because we're doing a query per child ID.
    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);
}

Usage Examples

GET /RecordRelationship/instance/parent/19

This returns the child instances of the Recipe instance with ID 19 - the guacamole recipe:

JavaScript
{
  "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:

JavaScript
{
  "Recipe": [
    {
      "ID": 20,
      "Recipe Name": "Hummos",
      "Culture": "Middle East",
      "Preparation Time": "",
      "Cooking Time": ""
    }
  ]
}

Implementing the UI

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:

Image 18

Parent-Child Forward Lookup

Clicking on Recipe, we see (I'm shrinking the width of the browser window here intentionally to try and comply with screenshot guidelines):

Image 19

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):

Image 20

Parent-Child Reverse Lookup

Now here's the more interesting thing. Let's start with ingredients. We see the ingredients for all the recipes:

Image 21

Let's search for "Garlic", and we see two places where garlic is used:

Image 22

If I click on one of them, I see the recipe in which it is used:

Image 23

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.

Extras

A few extra things I did in this iteration.

Swagger API Documentation

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:

Image 24

and we see:

Image 25

Nifty!

Serialization Service

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.

C#
public class PrettyFormat : ISerializationService
{
  public object Serialize(object data)
  {
    // For debugging, this is a lot easier to read.
    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.

Home Page

There's several built-in web pages at this point, so it's useful to have a home page:

Image 26

Conclusion

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:

Image 27

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.

What Will be in Part III

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.

Bug Fixes

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!

Refactoring

  • 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

New Features

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:

Image 28

  • 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:

Image 29

Far Future

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!

History

  • 6th April, 2021: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Marc Clifton
Architect Interacx
United States United States
Blog: https://marcclifton.wordpress.com/
Home Page: http://www.marcclifton.com
Research: http://www.higherorderprogramming.com/
GitHub: https://github.com/cliftonm

All my life I have been passionate about architecture / software design, as this is the cornerstone to a maintainable and extensible application. As such, I have enjoyed exploring some crazy ideas and discovering that they are not so crazy after all. I also love writing about my ideas and seeing the community response. As a consultant, I've enjoyed working in a wide range of industries such as aerospace, boatyard management, remote sensing, emergency services / data management, and casino operations. I've done a variety of pro-bono work non-profit organizations related to nature conservancy, drug recovery and women's health.

Comments and Discussions

 
QuestionReally not a Sunday afternoon project ;-) Pin
LightTempler7-Apr-21 9:33
MemberLightTempler7-Apr-21 9:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.