The well-structured Entity in EF Core

A database entity represents an identifiable entry, often also things from the real world, which are to be stored and managed in the database. The purpose of database entities is to provide a structured and efficient implementation for organizing and managing information. By identifying entities, defining attributes and establishing relationships, a coherent and meaningful structure is created that allows data to be stored, retrieved and updated effectively. This facilitates data management and analysis in applications and systems.

Primary Key

Each entity should be uniquely identifiable. This is achieved by a primary key, which ensures that each row in the table has a unique identification. The simplest representation of an entity key is a column that contains a value.

Guid IDs scale better (even with more data usage) in most environments, as they are unique even in scaling environments. With database-generated integer IDs, collisions can occur more quickly if several instances generate database IDs at the same time.

public class MyEntity
{
    [Key] 
    public Guid Id { get; set; }
}

The representation as a Guid base type is simple, but relatively error-prone as soon as an entity has several Guid properties. It is therefore always advisable to use Strong-Id implementations for Ids. Better Code with custom Strong-Id Types

public class UserEntity
{
    [Key] 
    public UserId Id { get; set; }
}

Contents and Properties

An entity is only meaningful if it also has data. Database languages often speak of attributes, software engineers of properties or contents. In C#, properties is the usual term here because the corresponding language feature class properties are used for the implementation.

Which properties become part of a class is largely defined in the normalization process. In some cases, intentional redundancies can also be part of an entity, e.g. if they are technically or legally necessary (e.g. often the case with invoices).

The amount of properties can therefore also result from the normalization process, whereby it should be noted that there are different recommendations for the maximum number of columns (=properties) depending on the database type. If an entity is to be used with different databases, it is advisable to stay below the lowest maximum column recommendation. Most database engines have a general recommendation for the maximum number of columns, but also a technical limit.

public class UserEntity
{
    [Key] 
    public UserId Id { get; set; }

    public string UserName {get;set;}

    public string EMail {get;set;}

    public DateOnly DateOfBirth { get;set; }

    public string TwitterHandle { get;set; }

    public string LinkedInUrl { get;set; }

    public string YouTubeUrl { get;set; }
}

Navigations

Navigations are the relations to other entities. These also result from the normalization process and help to ensure that data is not stored redundantly if this is not explicitly intended. Relations are represented in the code by collections and, in the case of explicit notation, which is recommended, by the keys - at database level they are simply columns with corresponding values and an index to the other table. They result in their own tables and therefore also their own entities.

public class UserEntity
{
    [Key] 
    public UserId Id { get; set; }

    public string UserName {get;set;}

    public string EMail {get;set;}

        // Navigation
    public UserProfileEntity Profile {get;set;}
}

public class UserProfileEntity

    // Navigation
    public UserEntity User { get;set; }
    [Key] 
    public UserId Id { get; set; } // in this case its FK and PK at the same time

    public DateOnly? DateOfBirth { get;set; }

    public string? TwitterHandle { get;set; }

    public string? LinkedInUrl { get;set; }

    public string? YouTubeUrl { get;set; }
}

Proxy-Entites

Proxy entities are entities that are implemented purely for technical reasons and have no data themselves. The idea is to distribute the write load across several tables so that read operations are not blocked.
This very often relieves the write and thus also the lock situations of a database and results in massively higher read performance.

public class UserEntity // Our Proxy Entity
{
    [Key] 
    public UserId Id { get; set; }

    // Navigation
    public UserProfileEntity Profile {get;set;}
    public UserCredentialsEntity Credentials {get;set;}
}

public class UserCredentialsEntity
{
    [Key] 
    public UserId Id { get; set; } // in this case its FK and PK at the same time

    // Navigation
    public UserEntity User { get;set; }

    // Data

    public string UserName {get;set;}

    public string EMail {get;set;}
}

public class UserProfileEntity

    [Key] 
    public UserId Id { get; set; } // in this case its FK and PK at the same time

    // Navigation
    public UserEntity User { get;set; }

    // Data

    public DateOnly? DateOfBirth { get;set; }

    public string? TwitterHandle { get;set; }

    public string? LinkedInUrl { get;set; }

    public string? YouTubeUrl { get;set; }
}

Annotations and Configurations

Annotations are characteristics of properties, e.g. whether they are mandatory fields or have a certain length. In the case of entity frameworks, this can be implemented in two ways

Annotations

Annotations are attributes that are set directly on the properties. This is the simplest and fastest option, but it also involves the greatest dependency on the entity framework.

public class UserProfileEntity

    [Key] 
    public UserId Id { get; set; } // in this case its FK and PK at the same time

    // Navigation
    public UserEntity User { get;set; }

    // Data
    
    [Required(false)]
    public DateOnly? DateOfBirth { get;set; }
    
    [Unicode(true)]
    [Required(false)]
    [MaxLength(50)]
    public string? TwitterHandle { get;set; }
    
    [Unicode(true)]
    [Required(false)]
    [MaxLength(250)]
    public string? LinkedInUrl { get;set; }

    [Unicode(false)]
    [Required(false)]
    [MaxLength(100)]
    public string? YouTubeUrl { get;set; }
}

As the annotations are implemented directly in the entity, they are also valid for all databases in which the entity is used.

Configurations

Configurations ultimately do the same thing, but since the restrictions are declared outside the entity, they can be implemented differently for each supported database. This makes it possible to maximize the use of different database engine support.

public class UserProfileEntitySqlServerConfig
    : IEntityTypeConfiguration<UserProfileEntity> // SQL Server
{
    public void Configure(EntityTypeBuilder<UserProfileEntity> b)
    {
        b.ToTable("UserProfiles");

        b.HasKey(e => e.Id);

        b.Property(e => e.TwitterHandle)
            .IsUnicode(true).IsRequired(false).HasMaxLength(50);

        b.Property(e => e.LinkedInUrl)
            .IsUnicode(true).IsRequired(false).HasMaxLength(250);

        b.Property(e => e.YouTubeUrl)
            .IsUnicode(false).IsRequired(false).HasMaxLength(100);
    }
}

public class UserProfileEntityPostgreServerConfig
    : IEntityTypeConfiguration<UserProfileEntity> // Postgre Server
{
    public void Configure(EntityTypeBuilder<UserProfileEntity> b)
    {
        b.ToTable("UserProfiles");

        b.HasKey(e => e.Id);

        b.Property(e => e.TwitterHandle)
            .IsUnicode(true).IsRequired(false).HasMaxLength(50);

        b.Property(e => e.LinkedInUrl)
            .IsUnicode(true).IsRequired(false).HasMaxLength(250);

        b.Property(e => e.YouTubeUrl)
            .IsUnicode(false).IsRequired(false).HasMaxLength(100);
    }
}

Another advantage of this variant is that it is much easier to implement your own extension methods and therefore reuse them in different places than with the attribute variant.

public class UserProfileEntitySqlServerConfig
: IEntityTypeConfiguration<UserProfileEntity> // SQL Server
{
    public void Configure(EntityTypeBuilder<UserProfileEntity> b)
    {
        b.ToTable("UserProfiles");

        b.HasKey(e => e.Id);

        b.Property(e => e.TwitterHandle).IsSocialMediaColumn(isUnicode: true, 50);
        b.Property(e => e.LinkedInUrl).IsSocialMediaColumn(isUnicode: true, 250);
        b.Property(e => e.YouTubeUrl).IsSocialMediaColumn(isUnicode: false, 100);
    }
}

Personally, I very rarely have the case that I have different values for constraints, which is why I generally prefer a mixture of both variants in all projects in order to use the maximum of both possibilities. This means that structural definitions (table, keys, index) are implemented as IEntityTypeConfiguration and all content definitions (lengths, Unicode, requirements...) are attached to the entity as attributes. Therefore, this implementation is the one that I use myself, as well as the one that I recommend in my projects and to my customers.

Conclusion

A well-structured entity therefore has the following characteristics:

• A Strong-Id primary key
• Unique data in the form of properties
• Attributes that describe the properties and use them efficiently (e.g. deactivate Unicode if not desired)
• Navigations to other entities incl. Strong-Id of the foreign keys
• Use of proxy entities in appropriate places to save performance
• Specific and optimized configurations for the database, including indexes for more efficient queries