Best practices for auto increment a number EF Core

Question

I'm looking for best practices in order to increment some numbers in my DB using EF Core..

I have a DB model called PARENT. In my application, I can have several PARENTS. Each PARENT has several CHILDS.

PARENT1: CHILD1 - CHILD2

PARENT2: CHILD1 - CHILD2 - CHILD3

I want, inside each PARENT, each children be numbered incrementally by a column: int Number. This means,

PARENT1.CHILD1 = 001;

PARENT1.CHILD2 = 002;

PARENT2.CHILD1 = 001;

PARENT2.CHILD2 = 002;

...

So, it has to be only unique and incremented by parent..

Can anyone give me some best practices in order to achieve this in an efficient way?

UPDATE:

public class Bar {
    public Guid BarId {get;set;}
    public ICollection<Client> Clients {get;set;}
}

public class Client {
    public Guid ClientId {get;set;}
    public Guid BarId {get;set;}
    public int ClientNumber {get;set;}
}

public class Context : DBContext {
    entity.HasKey(e => new { e.BarId, e.ClientId }).HasName("PK_CLIENT");
                entity.Property(e => e.ClientId).ValueGeneratedWhenDefault();
    entity.Property(e => e.ClientNumber).ValueGeneratedOnAdd();
    entity.HasIndex(e => new {e.BarId, e.ClientNumber}).IsUnique()
        .HasName("IX_CLIENT_NUMBER");
}

By calling ValueGeneratedOnAdd() I make the number column increment, but I want it to increment for each Bar, so it should allow duplicated client numbers between different bars, but not at the same Bar, and also increment them by Bar, not just by the Client entity.

Answer 1

One way would be to do the following;

1) Use integers as keys, and set ClientNumber property to be nullable.

    public class Bar
    {
        public int BarId { get; set; }
        public ICollection<Client> Clients { get; set; }
    }

    public class Client
    {
        public int ClientId { get; set; }
        public Bar Bar { get; set; }
        public int BarId { get; set; }
        public int? ClientNumber { get; set; }
    }

2) Set ClientId to identity (automatic increment) and ClientNumber to database generated (otherwise EF Core will not re-read the value from database after insert)

entity.Property(e => e.ClientId).ValueGeneratedOnAdd();
entity.Property(e => e.ClientNumber).ValueGeneratedOnAddOrUpdate();

3) Add a trigger to modify the ClientNumber after insert

    create trigger ClientNumber on Clients after insert 
    as
    begin
        set nocount on;
        update c set ClientNumber = n.RowNum from Clients c
        inner join (
            select ClientId, ROW_NUMBER() OVER(PARTITION BY BarId ORDER BY ClientId ASC) as RowNum
            from Clients
        ) n on n.ClientId = c.ClientId
    where c.ClientId in (select ClientId from inserted)
    end

Now all works automatically on insert:

            var bar1 = new Bar() { Clients = new List<Client>() };
            var bar2 = new Bar() { Clients = new List<Client>() };

            bar1.Clients.Add(new Client());
            bar1.Clients.Add(new Client());

            bar2.Clients.Add(new Client());
            bar2.Clients.Add(new Client());

            context.Bars.AddRange(new[] { bar1, bar2 });
            await context.SaveChangesAsync();

            bar1.Clients.Add(new Client());
            bar2.Clients.Add(new Client());
            bar1.Clients.Add(new Client());
            await context.SaveChangesAsync();

Will render

ClientId    BarId   ClientNumber
1   1   1
2   1   2
6   1   3
7   1   4
3   2   1
4   2   2
5   2   3

Downside is that you cannot use the unique index IX_CLIENT_NUMBER since ClientNumber will be NULL until the trigger executes.

Answer 2

Maybe it is not what you want, but since I'm writing some code, it does not fit as a comment.

To set the ClientNumber in child compositions, I would write some code like this:

Bar bar = new Bar();
int seq = 1;
bar.Clients.Add(new Client() { ClientNumber = seq++; });
bar.Clients.Add(new Client() { ClientNumber = seq++; });
dbContext.Update(bar);
dbContext.SaveChanges();

It works fine when you are creating a new Bar. If it can be edited and clients can be added or removed from the bar - like a CRUD -, then you have to change somethings. But it shows what I was talking about in the comment (setting the ClientNumber).

Another approach would be to intercept the SaveChanges, which I would avoid in this case, because it is kind of unclear for unadvised developers:

public class DbContextWithBlackMage : DbContext
{
    public override int SaveChanges(bool acceptAllChangesOnSuccess)
    {
        OnBeforeSaving();
        return base.SaveChanges(acceptAllChangesOnSuccess);
    }

    private void OnBeforeSaving()
    {
        var entries = ChangeTracker.Entries();
        foreach (var entry in entries)
        {
            //TODO black mage to fill the ClientNumber very secretly and obscurely
            //TODO Matheus will be angry when he finds out muahahahahaha!
        }
    }
}

Anyways... it is probably not what you want, but maybe it will inspire will about how to solve it (or how do not).

Answer 3

I solved this with an AutoMapper IValueResolver...

public class PublishedEntityVersionResolver : IValueResolver<CreatePublishedEntityCommand, PublishedEntity, int>
{
    private readonly IApplicationDbContext _dbContext;

    public PublishedEntityVersionResolver(IApplicationDbContext dbContext)
    {
        _dbContext = dbContext ?? throw new ArgumentNullException(nameof(dbContext));
    }

    public int Resolve(
        CreatePublishedEntityCommand source, PublishedEntity destination, int destinationMember, ResolutionContext context)
    {
        // If this EntityId & TenantId has been previously published, get it's version and increment otherwise start afresh...
        var previousVersion = _dbContext.PublishedEntities
            .SingleOrDefault(entity => entity.EntityId == source.EntityId && entity.TenantId == source.TenantId);

        return previousVersion?.Version + 1 ?? 1;
    }
}

 public void Mapping(Profile profile)
        => profile.CreateMap<CreatePublishedEntityCommand, PublishedEntity>()
            .ForMember(
                entity => entity.Version,
                options => options.MapFrom<PublishedEntityVersionResolver>());