Handle CreatedBy and UpdatedBy foreign keys in User table - Seed the first user with either SQL or Entity Framework Core

Question

We have a business rule that says that every update to an entity should be traceable to a User. Given GDPR, other privacy rules and the fact that a user could change their username we should only store a reference and not an actual username or anything else. We are using Entity Framework Core 3.1 and have solved it like this:

public interface IEntity
{
    public DateTime SysStartTime { get; set; }

    public DateTime SysEndTime { get; set; }
    
    public int CreatedById { get; set; }
    
    public User CreatedBy { get; set; }

    public int UpdatedById { get; set; }

    public User UpdatedBy { get; set; }
}

DbContext:

public override int SaveChanges()
{
    //First seed will crash here
    var user = User.Single(x => x.ExternalId == _currentUserExternalId);

    AddCreatedByOrUpdatedBy(user);

    return base.SaveChanges();
}

public void AddCreatedByOrUpdatedBy(User user)
{
    foreach (var changedEntity in ChangeTracker.Entries())
    {
        if (changedEntity.Entity is IEntity entity)
        {
            switch (changedEntity.State)
            {
                case EntityState.Added:
                    entity.CreatedBy = user;
                    entity.UpdatedBy = user;
                    break;
                case EntityState.Modified:
                    Entry(entity).Property(x => x.CreatedBy).IsModified = false;
                    entity.UpdatedBy = user;
                    break;
            }
        }
    }
}

User:

public class User : IEntity
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public DateTime SysStartTime { get; set; }

    public DateTime SysEndTime { get; set; }

    public int CreatedById { get; set; }

    public User CreatedBy { get; set; }

    public int UpdatedById { get; set; }

    public User UpdatedBy { get; set; }

    [Required]
    public string ExternalId { get; set; }
}

More information here:

https://stackoverflow.com/a/64824067/3850405

This works for every scenario except adding the first user.

I know a User can be added via plain SQL like this in a migration:

INSERT INTO [dbo].[User] (ExternalId, [CreatedById], [UpdatedById]) VALUES ('system', 1, 1)

I would however not like to hard code 1, 1 but instead pick it up from SQL similar to this:

INSERT INTO [dbo].[User] (ExternalId, [CreatedById], [UpdatedById]) VALUES ('system', SCOPE_IDENTITY(), SCOPE_IDENTITY())

Cannot insert the value NULL into column 'CreatedById', table 'MyProject.dbo.User'; column does not allow nulls. INSERT fails.

INSERT INTO [dbo].[User] (ExternalId, [CreatedById], [UpdatedById]) VALUES ('system', ident_current('[User]') + 1, ident_current('[User]') + 1)

Msg 547, Level 16, State 0, Line 5

The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_User_User_CreatedById". The conflict occurred in database "MyProject", table "dbo.User", column 'Id'.

I know nullable foreign keys would work as well for the User table but that is not what I want to use since every entity should inherit from IEntity.

score 0 · Accepted Answer · answered Nov 14 '20 at 00:10

Solved it like this:

DECLARE @currentId int
SET @currentId = ident_current('[dbo].[User]') + 1;  
INSERT INTO [dbo].[User] (ExternalId, [CreatedById], [UpdatedById]) VALUES ('system',  @currentId, @currentId)

Final migration looked like this:

public partial class Addsystemuser : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        string statement = $@"DECLARE @currentId int
                              SET @currentId = ident_current('[dbo].[User]') + 1;  
                              INSERT INTO [dbo].[User] (ExternalId, [CreatedById], [UpdatedById]) VALUES ('system',  @currentId, @currentId)";
        migrationBuilder.Sql(statement);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        string statement = $@"DELETE FROM [dbo].[User] WHERE [ExternalId] = 'system'";
        migrationBuilder.Sql(statement);
    }
}

Handle CreatedBy and UpdatedBy foreign keys in User table - Seed the first user with either SQL or Entity Framework Core

1 Answers1