Sequential ID per foreign key in EF Core

Question

I am designing a database with Entity Framework Core which should contain two entity types:

1. An entity named "Channel" with a unique ChannelId
2. An entity named "Message" with a foreign key ChannelId and a MessageId

The MessageId must be unique for each channel, and it should be counted starting at 1.

My first try to implement this was to use a composite key for the Message entity with ChannelId and MessageId, but it does not have to stay this way. However I don't how to auto-generate the MessageId with EF Core.

So I tried to get the last MessageId for the current Channel incremented it and tried to insert:

public class DatabaseContext : DbContext
{
    public void AddMessage(Message message)
    {
        long id = Messages
            .Where(m => m.ChannelId == message.ChannelId)
            .Select(m => m.MessageId)
            .OrderByDescending(i => i)
            .FirstOrDefault()
            + 1;

        while (true)
        {
            try
            {
                message.MessageId = id;
                Messages.Add(insert);
                SaveChanges();
                return;
            }
            catch
            {
                id++;
            }
        }
    }
}

This code does not work. After an exception occurred EF Core does not insert the item with the incremented ID. In addition to that it seems to be very inefficient in situation with concurrent inserts.

Is there a more elegant solution to solve this problem when I use an additional ID in the messages table as primary key and maybe some additional tables?

Answer 1

Concept

After long research I found a solution for the problem:

I added a MessageIdCounter row to my Channels table.

Unlike classical code, SQL allows an atomic conditional write. This can be used for optimistic concurrency handling. First we read the counter value and increment it. Then we try to apply the changes:

UPDATE Channels SET MessageIdCounter = $incrementedValue
WHERE ChannelId = $channelId AND MessageIdCounter = $originalValue;

The database server will return the number of changes. If no changes have been made, the MessageIdCounter must have changed in the meantime. Then we have to run the operation again.

Implementation

Entities:

public class Channel
{
    public long ChannelId { get; set; }
    public long MessageIdCounter { get; set; }

    public IEnumerable<Message> Messages { get; set; }
}

public class Message
{
    public long MessageId { get; set; }
    public byte[] Content { get; set; }

    public long ChannelId { get; set; }
    public Channel Channel { get; set; }
}

Database context:

public class DatabaseContext : DbContext
{
    public DbSet<Channel> Channels { get; set; }
    public DbSet<Message> Messages { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        var channel = builder.Entity<Channel>();
        channel.HasKey(c => c.ChannelId);
        channel.Property(c => c.MessageIdCounter).IsConcurrencyToken();

        var message = builder.Entity<Message>();
        message.HasKey(m => new { m.ChannelId, m.MessageId });
        message.HasOne(m => m.Channel).WithMany(c => c.Messages).HasForeignKey(m => m.ChannelId);
    }
}

Utility method:

/// <summary>
/// Call this method to retrieve a MessageId for inserting a Message.
/// </summary>
public long GetNextMessageId(long channelId)
{
    using (DatabaseContext ctx = new DatabaseContext())
    {
        bool saved = false;
        Channel channel = ctx.Channels.Single(c => c.ChannelId == channelId);
        long messageId = ++channel.MessageIdCounter;
        do
        {
            try
            {
                ctx.SaveChanges();
                saved = true;
            }
            catch (DbUpdateConcurrencyException ex)
            {
                var entry = ex.Entries.Single();    
                var proposedValues = entry.CurrentValues;
                var databaseValues = entry.GetDatabaseValues();
                const string name = nameof(Channel.MessageIdCounter);
                proposedValues[name] = messageId = (long)databaseValues[name] + 1;
                entry.OriginalValues.SetValues(databaseValues);
            }
        } while (!saved);
        return messageId;
    }
}

For successfully using EF Core's concurrency tokens I had to set MySQL's transaction isolation at least to READ COMMITTED.

Summary

It is possible to implement an incremental id per foreign key with EF Core. This solution is not perfect because it needs two transactions for one insert and is therefore slower than an auto-increment row. Furthermore it's possible that MessageIds are skipped when the application crashes while inserting a Message.