CODEWITHSUNDEEP
c#sql-serverentity-frameworkidentity
RegularNormalDayGuy
RegularNormalDayGuy

Reputation: 735

Entity Framework and insert with an Identity column

I'm using Entity Framework for a Windows Forms project. My database has two tables, Table A and Table B.

Table A has an identity column PkId. Table B also has an identity column (PkId) and also FkIdA which is a foreign key to table A.

If I add a new row in table A, it gets a PkId of 0. I will then add a new row in table B, that points to that new row in table A. That is expected.

Now if the user adds another row in table A, it will get the same Id (0). And then he will add more rows in table B pointing to the same Id. Once I call DbContext.SaveChanges(), the application will crash because Table A.PkId is not unique (makes sense).

I've worked with Dataset before, and it gave local Ids, starting from 0 or -1 and going decremental (so -1, -2, -3, etc.). How can I make sure Entity follows the same rule ?

The reason I don't call SaveChanges between each insert in order to retrieve the true PkId given by the database is because I want to offer the possibility to delete the changes done to the user (and thus, once he commits, he should not be able to revert what has been done).

Edit

I added a picture of my model. I also went and checked the PkId column of table A (Transaction) and its stored generated pattern is Identity, as expected.

enter image description here

Edit 2

The auto-generated model from entity contains a reference to payments within Transaction

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Payment> Payments { get; set; }

Now the way I add a new WholeTransaction, which is basically a Transaction with 2 payments is like this:

var newWholeTransaction = new AddNewTransaction();
newWholeTransaction.ShowDialog();

if (!newWholeTransaction.WasSaved)
    return;

var transaction = DbContext.Transactions.Add(newWholeTransaction.WholeTransaction.Transaction);

newWholeTransaction.WholeTransaction.PaymentJeanne.FkIdTransaction = transaction.Id;
newWholeTransaction.WholeTransaction.PaymentLuis.FkIdTransaction = transaction.Id;

DbContext.Payments.Add(newWholeTransaction.WholeTransaction.PaymentJeanne);
DbContext.Payments.Add(newWholeTransaction.WholeTransaction.PaymentLuis);

Upvotes: 3

Views: 2236

Answers (2)

mehmetx
mehmetx

Reputation: 860

Entity Framework manages PkIds, not you.

var transaction1 = new Transaction();
transaction1.Name = "tr1";

var transaction2 = new Transaction();
transaction2.Name = "tr2";

var payment1 = new Payment();
payment1.Amount = 1;
payment1.Transaction = transaction1;

var payment2 = new Payment();
payment2.Amount = 1;
payment2.Transaction = transaction2;

context.Transactions.Insert(transaction1);
context.Transactions.Insert(transaction2);
context.Payments.Insert(payment1);
context.Payments.Insert(payment2);

context.SaveChanges();

another way;

transaction1.Payments.Add(payment1);
transaction2.Payments.Add(payment2);

Upvotes: 3

Lana
Lana

Reputation: 1044

As usual I use Navigation properties for these things

public class MyEntity
{
    [Key]
    public int Id { get; set; }

    public int OtherId { get; set; }
    public OtherEntity Other { get; set; }
}

Upvotes: 1

Related Questions