Entity–attribute–value model (EAV) is a data model to encode, in a space-efficient manner, entities where the number of attributes (properties, parameters) that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest. Such entities correspond to the mathematical notion of a sparse matrix.

See more here: https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

Entity Attribute Value

The Entity Attribute Value, also known and mentioned in this document as EAV, allows dynamic assignment of Attributes and Values to objects saved in the database without changing the database structure. For example, without using EAVs, if you need to add a new property to an entity, you would need to add a new field in the class and a column in the database. By using the EAV method, you add at design time this functionality to the entities you know that might need dynamic properties and later you can define the object’s characteristics as needed. These properties can be defined by users and don’t involve special authorizations as a database update would.

If the Category is not assigned to the Entity, its Attributes can’t be assigned to the Entity

You can only assign Attributes defined in a Category only if that Category is assigned to the Entity.

1       Entity Attribute Values

There are three generic classes that that are used for this functionality:

• Entity Category (EntityCategory<TEntity>), also known as class in other systems;
• Attribute Definition (AttrDef<TEntityCategory>);
• Entity Attribute Value (EAV<TEntity, TEntityCategory>).

The generic type is the enhanced Entity’s type.

The Entity Attribute Values are added to an Entity by implementing the IHasEntityAttributeValues<TEntity> interface. This interface needs two fields to be added to the entity: the EAV that is a list of values and the EAV_Category which points to the assigned Categories:

public List<EAV<TEntity, EntityCategory<TEntity>>> EAV { get; set; }
public List<EntityCategory<TEntity>> EAV_Category { get; set; }

You will also need to add to the DbContext three DbSet properties:

public DbSet<EntityCategory<TEntity>> TEntity_Category { get; set; }
public DbSet<AttrDef<EntityCategory<TEntity>>> TEntity_AttrDef { get; set; }
public DbSet<EAV<TEntity, EntityCategory<TEntity>>> TEntity_EAV { get; set; }

We must note that when using this functionality, there are other tables created in the database along with the ones mentioned here, i.e., join tables.

1.1     The Entity Category

The Entity Category groups together related Attribute Definitions that can all be applied to the entities. If certain attributes can be optional for certain entities, they must belong to different Entity Categories.

In order to add dynamic properties to an entity, you need to assign Entity Categories to it. The Entity Category assignment will enable you to assign Attributes defined in that Entity Category to those entities. For example, if we need to assign the Attribute “Color” to a product, we will create a “Product with Color” Entity Category and assign it to those products that must provide information about color. Inside the Entity Category we would define the Attribute.

1.1.1     Implementation

The Entity Category is a Diamond Node type entity, that means it has Parents and Children.

In the Entity Category you define the Attribute Definitions that can be assigned to entities when the Entity Category is assigned to them. Not all the Attributes in a Category need to be assigned to an entity if that Category is assigned to that entity.

It is recommended that you define inside the Categories only those Attributes that are related and convey the same idea. For example, we would not add an Attribute about length inside a category that has other attributes about color.

The Entity Category references (List) to all the Entities it is assigned to.

1.2     The Attribute Definition

The Attributes are defined and grouped together under a Category. Each Attribute Definition has references to the Categories to which it belongs to.

When we define an Attribute for an entity, we need to assign it an Eav Data Type to tell the system what kind of data we are going to store to it and a Dynamic Form Control Type to tell the UI how to render the form control. Certain combinations of Eav Data Type and Dynamic Form Control Type are not allowed. For example, we can’t assign a Combo Box form control type to a numeric field.

Depending on the data type, we might need to define some other properties, for example:

• for decimal numeric types we can define the Number of Decimals For any numeric type we can configure a Min Value and a Max Value;
• for string and char data types, we can define a Validation Regex;
• if the Attribute refers to a Unit of Measure, we can assign a reference to it;
• if we need to reference another entity, we would need to configure the Reference Data Type Name field (this needs to be the Full Type Name). If multiple references are needed, we need to set the Is Collection

1.2.1     Attribute configuration fields

The validity table will indicate on which data type a configuration can be used. The color of the cell’s data type indicates:

• Green can be used without any special considerations;
• Yellow means limited use;
• Red means that the configuration field has no meaning for that specific data type;
• Gray means that the configuration doesn’t apply.

1.2.1.1 Name, Description, Label and Note

Configuration validity

The Name, Description, Label and Note fields are used to describe to the user in detail the scope of the Attribute. In order to avoid errors, use these fields to fully describe the Attribute’s role in the system and its usage as much as possible.

1.2.1.2      Eav Data Type

Configuration validity

The Eav Data Type is the most important configuration of an Attribute. This tells the system what kind of data type the Attribute represents. The possible data types are defined in the EavDataType enum:

• NULL
• SystemBooleanNullable
• SystemByteNullable
• SystemSByteNullable
• SystemCharNullable
• SystemInt32Nullable
• SystemUInt32Nullable
• SystemInt64Nullable
• SystemUInt64Nullable
• SystemInt16Nullable
• SystemUInt16Nullable
• SystemDecimalNullable
• SystemDoubleNullable
• SystemSingleNullable
• SystemString
• SystemGuidNullable
• ReferenceEntity
• SystemDateTimeOffsetNullable
• SystemDateTimeNullable
• JsonDocument

Some of these data types are grouped together in the Configuration Validity tables, for example all signed integer values are under Signed Integer and all floating-point data types are under Floating Point.

1.2.1.3      Dynamic Form Control Type

Configuration validity

The Dynamic Form Control Type tells the UI how to render the form control representing the Attribute. Certain combinations of Eav Data Type and Dynamic Form Control Type are not allowed, for example, we can’t assign a Combo Box form control type to a numeric field.

All the possible form control types are defined in the DynamicFormControlType enum. We list here only those controls that are valid for Entity Attributes:

• autocomplete (single reference of collection items)
• checkbox (only for bool values)
• colorPicker
• comboBox (single reference of collection items)
• date
• dateInterval
• dateTimeInterval
• dateTimeLocal
• email
• list (single or multiple reference of collection items)
• number (integer or decimal numbers)
• password
• phone
• listBox (single or multiple reference of collection items)
• radioButtons (single reference of collection items)
• searchHelp (single or multiple reference of collection items)
• text
• time
• url
• dataTable (read-only single or multiple reference of collection items)
• html

If you need more details about each of these form controls, please consult the UI configuration documentation.

1.2.1.4      Reference Data Type Name

Configuration validity

If the Attribute represents a reference to another entity, this will indicate the referenced entity’s Full Type Name. You can use the Referenced Data Types to provide more searching and filtering options by configuring projections or other data set sources.

The data sources provided by the Referenced Data Types must return the same Id referencing the main reference provided by the Reference Data Type Name.

1.2.1.5      Referenced Data Types

Configuration validity

You can use this field to specify what data sets can be used to provide the reference. Usually, these data types are related to the main reference used in the Reference Data Type Name field such as projections.

When specifying other referenced data types, you need to use the searchHelp UI control in order to be able to switch between the data sets.

1.2.1.6      Payload As Json and Json Document

Configuration validity

You can use this field to provide options for collection items. In order to send the option values to the UI, the JSON must contain a collection field called optionsForControl containing items that have the structure derived from EntityEnum:

{
  "optionsForControl": [
{
  "id": "3033b1e7-b64d-4a37-b463-c75ed16ff2d8",
  "name": "Option One",
  "value": 1
},
{
  "id": "3033b1e7-b64d-4a37-b463-c75ed16ff2d9",
  "name": "Option Two",
  "value": 2
},
{
  "id": "3033b1e7-b64d-4a37-b463-c75ed16ff2da",
  "name": "Option Three",
  "value": 3
}
]
}

Do not use the optionsForControl field in this configuration when using a reference to entities. You can use tough filtering options for the data returned by the Referenced Data Types sources.

Important: When using the optionsForControl field to provide options for the Attribute, you need to configure the Eav Data Type to ReferenceEntity. The UI will behave as if an entity is referenced.

You can add more fields to the options by deriving the EntityEnum class in order to fully define your needs. If the selection allows multiple items to be selected, you also need to set the Is Collection field.

When storing the reference to the option in the database, the Id field will be stored in the Guid field of the Primitive Value.

When using the value in the business logic, you need to deserialize the options and search the referenced value by its Id.

The UI in its basic for only displays the Id of the selected option.

1.2.1.7      Unit of Measure

Configuration validity

If the Attribute’s value represents a Unit of Measure, reference it by configuring the Unit of Measure field. Usually numeric and Entity References can represent Units or Measure.

1.2.1.8 Is Collection

Configuration validity

Is Collection flag will be set to true if multiple entities will be referenced or if multiple values can be entered. If the Attribute represent a Collection, make sure you selected a form control that allows multiple references to be selected. For multiple selection you would normally use a searchHelp or listBox form control.

If the Attribute represents a Collection, for each referenced entity or value there will be one record entered in the EAV table.

1.2.1.9      Is Unique Value

Configuration validity

If the value must be unique throughout all the Entities that use the Attribute, we will set the Is Unique Value to true.

On Date Time data types, the uniqueness check can trigger false results.

1.2.1.10  Is Unique Entity Value

Configuration validity

If we need only one value of a certain Attribute to be assigned to an Entity, we will set the true the Is Unique Entity Value. This will ensure that the entities that use the specific Attribute have only one value of that specific Attribute. Not configuring this field allows multiple values of the same Attribute to be assigned to the entity.

Note that on Date Time data types, the uniqueness check can trigger false results.

1.2.1.11  Is Mandatory

Configuration validity

Is Mandatory field will be set to true if we need the value to be mandatory.

1.2.1.12  Min Count and Max Count

Configuration validity

If we need to limit the maximum and/or the minimum number of values assigned to collections by using the Attribute, we will use the Min Count and Max Count fields.

Using this configuration on string type Attributes will limit their length.

1.2.1.13  Validation Regex

Configuration validity

You can configure a Validation Regex to check the validity of the user input. Usually this is used on string type values but can be used with limited functionality to check numbers. For example, you can check if a number is between 1 and 999 by using the following regex:

([1-9]|[1-9][0-9]|[1-9][0-9][0-9])

1.2.1.14  Number Of Decimals

Configuration validity

The field Number of Decimals configure the number of decimals that will be displayed when rendering the value. It can only be used for floating-point type numbers and their collections.

1.2.1.15  Min Value and Max Value

Configuration validity

The Min Value and Max Value of an Attribute’s value can be configured with this fields. This fields only make sense for numeric values and their collections.

1.2.1.16  Order Index

Configuration validity

Order Index is a base functionality and helps the user order entities and values when displayed in lists. The Order Index used in Attribute Definition configures the order in which the attributes are shown in lists.

1.2.1.17  Attribute Definition Aggregate

Configuration validity

When the Attribute need to be aggregated in a way, this field configures how the aggregation will behave. The options for aggregating Attribute values are in the AttributeDefinitionAggregate enum:

• Average
• Count
• Distinct
• Concat
• Min
• Max
• OrderBy
• OrderByDescending
• Sum
• Multiply

1.2.1.18  Definition Inheritance

Configuration validity

If an Attribute need to be inherited by the Entity’s Parents and/or Children, the Definition Inheritance configures this behavior.

This behavior can be configured by using the Attribute’s Parents and Children.

The inheritance options are in the AttributeDefinitionInheritanceMode enum:

• None
• ByParentOnly
• ByChildrenOnly
• ByBoth

1.2.2     Reference Entity and Options

When an Attribute references another Entity, we need to configure the following fields:

• Reference Data Type Name field will point to the Full Type Name of the referenced Entity;
• Is Collection flag will be set to true if multiple entities will be referenced If this option is used, make sure you use a UI form control that allows multiple selection, such as a search help or list box;
• If we can provide the search help’s data from multiple sources, we can use the Referenced Data Types to point to those sources;
• optionally we can configure the Is Mandatory field if we need the value to be mandatory;
• if we need to limit the maximum and/or the minimum number of values assigned by using the Attribute, we will use the Min Count and Max Count fields;

If no entity exists but we can provide a list of valid values from which the user can choose from, we will use the PayloadAsJson property to configure the options.

1.3     The EAV

The EAV stores a reference to the Attribute it represents and to the Entity to which the value belongs to.

The value of an EAV is stored in a Primitive Value variable. From a database point of view, this generates one column for each data type. One EAV will only use only one column in order to store its value, the rest of them being null or ignored. When reading a value, the engine looks at the Attribute’s Eav Data Type and decides what field from the Primitive Value to return or write to. If the Attribute’s Eav Data Type is not configured, an exception will be thrown.

2       Enhancing EAVs

Due to its complex implementation, we don’t recommend you modify the behavior of the EAV engine. If you need to add fields to the Entity Category, Attribute Definition or the Entity Attribute Value you should create other entities that will reference the enhanced entity and add there all the enhancements.

When referencing EAV entities we will reference them by their generic type and Id. The Id should be used in a ForeignKeyAttribute assigned to the property that reference the generic data type.

Example

You need to enable or disable Bill of Material Inputs according to the Product’s EAVs. In order to trigger the Bill of Material Inputs we need to check the EAV’s value and only for certain values enable the use of that specific Bill of Material Input.

To solve this problem, we will create a new entity BillOfMaterialsEav referencing the Bill of Material’s EAVs.

[Table(nameof(BillOfMaterialsEav), Schema = nameof(Manufacturing))]
public class BillOfMaterialsEav : BaseEntity
{
    public Guid? EavId { get; set; }

[UiDisplayAs(ErpExtensions.Ui.Form.DynamicFormControlType.searchHelp)]
[ForeignKey(nameof(EavId))]
    public EAV<BillOfMaterials, EntityCategory<BillOfMaterials>> EAV 
{ get; set; }

/// <summary>
/// A list of BOM inputs triggered by this EAV
/// </summary>
    public List<BillOfMaterialsInput> BillOfMaterialsInput { get; set; }
}

Please not the code in BOLD. We reference a generic class and we store the EavId. The generic class is also a DbSet in the DbContext. The EAV engine behaves as before because the EAV entity has no idea it is being referenced.

The enhancement implementation should do all the business logic checks when enhancing the EAV functionality.