search

dotnet / EntityFramework.Docs

Documentation for Entity Framework Core and Entity Framework 6
https://docs.microsoft.com/ef/
Creative Commons Attribution 4.0 International
1.62k stars 1.96k forks source link

Make it explicit that sequences defined for Hi-Lo are not suitable for "normal" key generation #1542

Open GitMonkey007 opened 5 years ago

GitMonkey007 commented 5 years ago

I'd really appreciate if the documentation on sequences were a bit clearer and address some common usage scenarios...

Perhaps I'm experiencing this because I'm using Oracle with Oracle.EntityFrameworkCore (beta4) or perhaps the docs need to be clearer in what actually happens/how to accomplish the following...

I have a database that already exists, I expect the primary key column to be the next value in a sequence (although not set by a trigger). I think this scenario is pretty common.

A typical insert here would be something like...

insert into Person (personid, ...) values (PersonIdSequence.nextval, ...);

If I define the model as follows:

        modelBuilder
            .Entity<Person>()
            .Property(x => x.Id)
            .ValueGeneratedOnAdd()
            .ForOracleUseSequenceHiLo("PersonIdSequence");

I get the following annotation logged at runtime (note the 10):

Relational:Sequence:.PersonIdSequence: ' PersonIdSequence ', '', '1', '10', '', '', 'Int64', 'False'

If I enter a row with one instance of my application, the sequence is used (lets say I get the number 10 for this insert) If I now open a database session and insert a row

insert into Person (PersonId) values (PersonIdSequence.nextval); commit; -- creates row 11 for this insert

If I enter another row with an instance of my application, I expect the row to be inserted with id=12, but I actually get 11... an exception is throw and no data inserted.

Changing the definition of my model as follows:

        modelBuilder
            .HasSequence(PersonIdSequence)
            .IncrementsBy(1);

        modelBuilder
            .Entity<Person>()
            .Property(x => x.Id)
            .ValueGeneratedOnAdd()
            .ForOracleUseSequenceHiLo(PersonIdSequence);

Results in the following annotation logged at runtime

Relational:Sequence:.PersonIdSequence: ' PersonIdSequence ', '', '1', '1', '', '', 'Int64', 'False'

... and all my inserts appear to work as expected.

I think what is happening is that Entity Framework is generating the next 10 sequence numbers in the first example (maybe?), and always going back to the database to get the next sequence in the second example.

I know this is related to Oracle sequences, but the documentation here on using Sequences in EntityFramework Core is, in my opinion, unclear. Providing more information about how I should define sequence for this scenario would be helpful (I'd have thought using a sequence for a primary key with/without a trigger was a pretty common scenario, but I could be wrong).

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

xenophilios commented 4 years ago

This article is unclear.