How do I get an identity value with Entity Framework(v5) before I save the record

Question

I am new to entity framework and I have been searching a while for an answer to this question and I can't find anything that directally addresses this.

Here is the problem. I have a table in Oracle. In this table there are 2 fields(there are more but not important to this question). Card_Transaction_Id and Parent_Card_Transaction_ID. The Parent_Card_Transaction_Id field is constrained by the Card_Transaction_Id field and I am using a Oracle sequence via a trigger to populate the Card_Transaction_Id field.

In my code, I am using Entity Framework(Version 5) to connect using the Code First Approach.

The issue is when I try to create a new record. I need to know what the next sequence value is in order to populate the Parent_Card_Transaction_Id. My mapping for card transactions:

public class CardTransactionMap : EntityTypeConfiguration<CardTransaction>
{
    public CardTransactionMap(string schema)
    {

        ToTable("CARD_TRANSACTION", schema);

        // Mappings & Properties

        // Primary Key
        HasKey(t => t.CardTransactionId);
        Property(t => t.CardTransactionId)
            .HasColumnName("CARD_TRANSACTION_ID")
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        Property(t => t.ParentCardTransactionId)
            .HasColumnName("PARENT_CARD_TRANSACTION_ID");

        Property(t => t.CardProfileId)
            .HasColumnName("CARD_PROFILE_ID");
    }
}

The question is - is there any way to get the next sequence number before I save the record?

My current work arround is to use the following method:

    public static decimal GetNextCardTransactionSequenceValue()
    {
        using (var context = new Context(new OracleConnectionFactory().GetConnection()))
        {

            var sequence = context.Database.SqlQuery<int>("select card_transaction_id from card_transaction").ToList();

            return sequence[0];

        }   

    }

Using that method, I get the next value and then just populate my table. This works but I don't like doing it this way. I feel that there must be a better way to do it.

Thanks in advance.

Answer 1

You have to do this by navigation properties.

By fetching the next value from a sequence before actually using it in the same session you create yourself a concurrency issue: another user can increment the index (by an insert) in the time between drawing its next value and assigning it to the child record. Now the child will belong to the other user's record!

If your CardTransaction class has a parent reference like this:

int ParentCardTransaction { get; set; }

[ForeignKey("ParentCardTransaction")]
CardTransaction ParentCardTransaction { get; set; }

you can create a parent and child in one go and call SaveChanges without worrying about setting FK values yourself:

var parent = new CardTransaction { ... };
var child = new CardTransaction { ParentCardTransaction = parent, ... };
SaveChanges();

Now EF wil fetch the new CardTransactionId from the parent and assign it to the FK of the child. So generating and getting the parent Id happens all in one session, so it is guaranteed to be the same value.

Apart from preventing concurrency issues, of course it is much easier anyway to let EF do the heavy lifting of getting and assiging key values.

Answer 2

Change your table definition to this :

CREATE TABLE t1 (c1 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, 
               c2 VARCHAR2(10));

as per the information in the link i provided in the comment. after the update ef will automatically query the value for the id that is inserted, there is no need to fill in the id before the insert. ef will generate an insert sql query without id.

Answer 3

Create a Stored Procedure or Query that will return you the next Value from the Table here is an Example

SELECT NVL(MAX(card_transaction_id + 1), 0) AS MAX_VAL
FROM card_transaction T;

or Create a Trigger - for OracleDB