I have a simple contact app that uses sqllite as database. I use EF code first to design it's database. When I save the data, It's Id produces in database as expected (Auto Increment), but the SaveChanges method always return 1 for Id.

How can I get the latest table Id?

DbContext :

public class ApplicationDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        string dbPath = DbPathGenerator.GetDbPath();

        options.UseSqlite($"Data Source={dbPath}/LiteContactsDb.db");
    }

    public DbSet<Person> Persons { get; set; }
    public DbSet<Address> Addresses { get; set; }
    public DbSet<Phone> Phones { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>()
            .HasMany<Address>()
            .WithOne(a => a.Person)
            .HasForeignKey(a => a.PersonId);

        modelBuilder.Entity<Person>()
            .HasMany<Phone>()
            .WithOne(a => a.Person)
            .HasForeignKey(a => a.PersonId);


        base.OnModelCreating(modelBuilder);
    }
}

Save Method :

    public bool SaveContact(PersonViewModel personViewModel, PhoneViewModel phoneViewModel, AddressViewModel addressViewModel)
    {
        bool result = false;

        try
        {
            Person person = new Person();

            person.FirstName = personViewModel.FirstName;
            person.LastName = personViewModel.LastName;
            person.Email = personViewModel.Email;

            _context.Persons.Add(person);
            var personId = _context.SaveChanges();

            Phone phone = new Phone();

            phone.PersonId = personId;
            phone.PhoneTitle = phoneViewModel.PhoneTitle;
            phone.PhoneNumber = phoneViewModel.PhoneNumber;

            Address address = new Address();

            address.PersonId = personId;
            address.AddressTitle = addressViewModel.AddressTitle;
            address.AddressString = addressViewModel.AddressString;
            address.PostalCode = addressViewModel.PostalCode;

            _context.Phones.Add(phone);
            _context.Addresses.Add(address);

            _context.SaveChanges();

            result = true;
        }
        catch (Exception ex)
        {
            result = false;
        }

        return result;
    }

_context.SaveChanges(); always returns 1.

Thanks

CodePudding user response：

The return value of SaveChanges is not the id of the newly created record, but the number of changes. So if you insert a single item, the return value will always be 1.

If you want to access the id of the newly inserted object, check out the id-property of the object. It is updated after the insert.

CodePudding user response：

First, SaveChanges returns the number of affected rows, not any IDs generated when storing objects. Second, SaveChanges persists all pending changes, not just a single object.

DbContext is a disconnected Unit-of-Work, not a database connection. It doesn't even keep a connection open until it has to either load data or save the changes. SaveChanges is called to persist all changes, not just the latest. When that happens a new connection opens and all changes are persisted in a single internal transaction.

This means that rolling back the Unit of Work needs no extra effort - just don't call SaveChanges before disposing the DbContext.

Finally, there's no need to add objects individually. Calling .Add(person) will add all objects accessible through it in the Added state. When the changes are peristed DbContext will execute the INSERT operations in the correct order and fix up the FK properties automatically.

This means that adding a person, phone and address in a single consistent "transaction" only needs this :

var person = new Person {
    FirstName = personViewModel.FirstName,
    LastName = personViewModel.LastName,
    Email = personViewModel.Email
   
    Phone = new Phone {
        PhoneTitle = phoneViewModel.PhoneTitle,
        PhoneNumber = phoneViewModel.PhoneNumber
    },
    
    Address = new Address {
        AddressTitle = addressViewModel.AddressTitle,
        AddressString = addressViewModel.AddressString,
        PostalCode = addressViewModel.PostalCode
     }
}
_context.Persons.Add(person);

...
_context.SaveChanges();