KyloRen KyloRen - 18 days ago 5
C# Question

Insert All only inserts last record? LINQ to SQL

No matter what I try I cannot get this to work.

It is a similar question to this one InsertAllOnSubmit only inserts first data record where in my case it only inserts the last record set.

At first I though it was the primary key was not being set as the manual setting using

DateTime.UtcNow.Ticks
to update the
ID
column. But after changing the setting in SQL server I was able to manually set the primary key.

I though this would fix the issue, but now it updates the primary key ,but only inserts the last record in the
foreach
loop.

I tried deleting the
Dbml
and recreating it, but that did nothing.

This is the code I am using,

List<Tenant_Bills_TBL> addNewData = new List<Tenant_Bills_TBL>();
Tenant_Bills_TBL addBill = new Tenant_Bills_TBL();
var recordsForTenant = Database.GetBillsRecordsForTenant(Database.DataContext, SelectedTenant.Code);
foreach (var item in recordsForTenant)
{
addBill.ID = DateTime.UtcNow.Ticks;
addBill.Tenant_Code = SelectedTenant.Code;
addBill.Year_Data = DateFilter.Year;
addBill.Month_Data = DateFilter.Month;
addBill.Tenant_Bill = item.Tenant_Bill;
addBill.Bill_Amount = item.Bill_Amount;
addBill.Bill_Quantity = item.Bill_Quantity;
addNewData.Add(addBill);
}
Database.DataContext.Tenant_Bills_TBLs.InsertAllOnSubmit(addNewData);
Database.DataContext.SubmitChanges();


It is like the list is overwriting the values after being added only leaving the last dataset in the
List<T>


Is there a setting on the server side that I need to change or is it something else?

Answer

The row

Tenant_Bills_TBL addBill = new Tenant_Bills_TBL(); 

should be inside the foreach loop.

The code in the question instantiate a single object and override it's properties with every iteration.

Also, having DateTime.UtcNow.Ticks as a primary key is not really recommended. You better use the database auto-increment buit in mechanism (most databases have that option). As you found out yourself, a foreach loop is simply too fast for the ticks to change between each iteration, and if you are even thinking of multy threaded inserts you can clearly see that even having Thread.Sleep inside the loop is not good enough.