Problem with getting inserted Id from database

Question

I am trying to get the last inserted Id from database via Entity Framework, however my problem is somehow unique and I am not able to find any solution, but to rewrite my whole infrastructure and business layer, so all my Ids are Guids, which I am able to create manually, or get last record with another database call after commit.

Here is the problem. I have a three-layered architecture, where I am using UoW, repository, services and facades. I will show my code from top to bottom, so you can understand.

Here is my facade, where uow.Commit is calling SaveChanges()

public async Task<int> RegisterUserAsync(UserCreateDto user)
{
        using (var uow = UnitOfWorkProvider.Create())
        {
            var id = _userService.Create(user);
            await uow.Commit();
            return id;
        }
}

As you can see I am sending only my DTO into the service, where I process is like this, also I am mapping inside service

public virtual int Create(TCreateDto entityDto)
{
        var entity = Mapper.Map<TEntity>(entityDto);
        Repository.Create(entity);
        return entity.Id;
}

and finally my repository looks like this

public TKey Create(TEntity entity)
{
        Context.Set<TEntity>().Add(entity);
        return entity.Id;
}

Is there some elegant solution to this? Like I said my only idea is to switch all Ids to Guid or second call for Id after commit, which I find as not very good solution, because when I want to connect two or more tables in one transaction it would be impossible.

Answer 1

EF Core solution of the problem is simple - auto generated PK of an entity instance is available after calling SaveChanges[Async]. e.g.

var entity = Mapper.Map<TEntity>(entityDto);
Context.Add(entity);
// Here entity.Id contains auto-generated temporary value
// Other stuff...
Context.SaveChanges();
// Here entity.Id contains actual auto-generated value from the db

So the problem is more in the design of your infrastructure - all these (unnecessary) UoW, repository, services and facades simply hide that functionality.

The only relatively simple and elegant solution I see with your architecture is to change the service return type from int to Func<int>, e.g.

public virtual Func<int> Create(TCreateDto entityDto)
{
    var entity = Mapper.Map<TEntity>(entityDto);
    Repository.Create(entity);
    return () => entity.Id; // <--
}

Then in your façade you could use

public async Task<int> RegisterUserAsync(UserCreateDto user)
{
    using (var uow = UnitOfWorkProvider.Create())
    {
        var id = _userService.Create(user);
        await uow.Commit();
        return id(); // <-- the actual id!
    }
}

---

Edit:

Actually EF Core provides another option which would allow you to keep your current design intact - the HiLo key generation strategy, but only if the database provider supports it. I can say for sure Microsoft.EntityFrameworkCore.SqlServer and Npgsql.EntityFrameworkCore.PostgreSQL do support it with respectively ForSqlServerUseSequenceHiLo and ForNpgsqlUseSequenceHiLo fluent APIs.