KyloRen KyloRen - 1 year ago 58
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

to update the
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

I tried deleting the
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;

It is like the list is overwriting the values after being added only leaving the last dataset in the

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download