Entity Framework - persisting foreign key referencing new record
I have a problem persisting my data in Entity Framework.
I have implemented a function that overrides saveChanges of my dbContext because I am using it to set some audit data and other things. In one case, I have big problems.
I need to insert for every new record some records in another table, referencing the new one using a foreign key. If there is only one new entry, everything is fine. But if the entry is 2 or more then I get errors.
I have for loops on all new entries like this:
var addedAuditedEntities = ChangeTracker.Entries()
.Where(p => p.State == EntityState.Added)
.Select(p => p.Entity);
foreach (var added in addedAuditedEntities)
{
.....
}
I am writing my attempts.
1) so it gives an error in the second new record saying that there is unique data in the TabAssVeicoliTerminali tab (it is a table with a unique index on the IDTER-IDTEV pair), I think because it inserts temporary all records with IDTEV = 0
else if (added.GetType() == typeof(TabTessereVeicoli))
{
TabTessereVeicoli i = (TabTessereVeicoli)added;
i.DATA_INS = now;
i.USER_INS = mdlImpostazioni.p.UserName;
var listaterV = new List<TabAssVeicoliTerminali>();
foreach (var ter in MainWindow.dbContext.TabTerminali)
{
var tt = new TabAssVeicoliTerminali();
tt.MODIFICATO = true;
tt.ABILITATO = true;
tt.IDTER = ter.ID;
tt.IDTEV = i.ID;
tt.DATA_INS = now;
tt.USER_INS = mdlImpostazioni.p.UserName;
listaterV.Add(tt);
}
MainWindow.dbContext.BulkInsert(listaterV);
}
2) So I was trying to save earlier to try and set the id of the new record (this is an auto-increment id) and use something! = 0 in the external key IDTEV. But it saves everything as well as other new entries. This way it correctly stores fk in another table, but only for the first new record:
else if (added.GetType() == typeof(TabTessereVeicoli))
{
TabTessereVeicoli i = (TabTessereVeicoli)added;
i.DATA_INS = now;
i.USER_INS = mdlImpostazioni.p.UserName;
var listaterV = new List<TabAssVeicoliTerminali>();
base.SaveChanges();
foreach (var ter in MainWindow.dbContext.TabTerminali)
{
var tt = new TabAssVeicoliTerminali();
tt.MODIFICATO = true;
tt.ABILITATO = true;
tt.IDTER = ter.ID;
tt.IDTEV = i.ID;
tt.DATA_INS = now;
tt.USER_INS = mdlImpostazioni.p.UserName;
listaterV.Add(tt);
}
MainWindow.dbContext.BulkInsert(listaterV);
}
3) I also tried without BulkInsert but get the same results.
How can I achieve my goal?
source to share
Starting with a hint of Ben Robinson in the comments on this question, I found a solution.
It works, but this style is the "perfect solution" because I had to comment out the bulk insert part to do this, so it's a little slow with a lot of new entries.
Anyway, the code I posted in the question is now:
else if (added.GetType() == typeof(TabTessereVeicoli))
{
TabTessereVeicoli i = (TabTessereVeicoli)added;
i.DATA_INS = now;
i.USER_INS = mdlImpostazioni.p.UserName;
var listaterV = new List<TabAssVeicoliTerminali>();
foreach (var ter in MainWindow.dbContext.TabTerminali)
{
var tt = new TabAssVeicoliTerminali();
tt.MODIFICATO = true;
tt.ABILITATO = true;
tt.IDTER = ter.ID;
tt.IDTEV = i.ID;
tt.DATA_INS = now;
tt.USER_INS = mdlImpostazioni.p.UserName;
//listaterV.Add(tt);
i.TabAssVeicoliTerminali.Add(tt);
}
//MainWindow.dbContext.BulkInsert(listaterV);
}
Let me know if you know how I can achieve my goal using bulkInsert as well!
source to share