CODEWITHSUNDEEP
c#sql-serverlinqlinq-to-sql
KyloRen
KyloRen

Reputation: 2741

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?

Upvotes: 0

Views: 423

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82504

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.

Upvotes: 3

Alexander Taran
Alexander Taran

Reputation: 6725

You are getting this behavior because you have the same object added over and over. As the comment said - you should move the instantiation of a new object inside the loop. Even better - you can make it a LINQ statement:

var newData = Database
           .GetBillsRecordsForTenant(Database.DataContext, SelectedTenant.Code)
           .Select(item => new Tenant_Bills_TBL{
                ID = DateTime.UtcNow.Ticks;
                Tenant_Code = SelectedTenant.Code;
                Year_Data = DateFilter.Year;
                Month_Data = DateFilter.Month;
                Tenant_Bill = item.Tenant_Bill;
                Bill_Amount = item.Bill_Amount;
                Bill_Quantity = item.Bill_Quantity;                                          
                })
           .ToList();
    Database.DataContext.Tenant_Bills_TBLs.InsertAllOnSubmit(addNewData);

Upvotes: 1

Related Questions