inserting multiple lines to a database table using LINQ query

Question

I'm trying to insert all the ith values to table called AB_Product_vs_Field but its only insert last i values only

this is that LINQ query

for (int i = 0; i < product.ListProductFields.Count; i++)
{
   insertproductvalue.Product_ID = product.Product_ID;
   insertproductvalue.Field_ID = product.ListProductFields[i].ProductFieldID;
   insertproductvalue.Field_Value_EN = product.ListProductFields[i].Field_Value_EN;                         
};

db.AB_Product_vs_Field.Add(insertproductvalue);
db.SaveChanges();

How to insert all of those rows one by one

Put db.AB_Product_vs_Field.Add(insertproductvalue); inside the for loop (your currently only adding the last one). And you really should initialize a new instance of insertproductvalue inside the for loop as well. — user3559349
– user3559349, Commented Nov 16, 2015 at 8:58
@StephenMuecke then I'm getting this SystemInvalidOperationException: {"The property 'Field_ID' is part of the object's key information and cannot be modified. "} — kez
– kez, Commented Nov 16, 2015 at 9:03
Then if its the PK field, remove insertproductvalue.Product_ID = product.Product_ID; (it will be generated automatically when you save) - or are you editing existing objects? — user3559349
– user3559349, Commented Nov 16, 2015 at 9:05
Product_ID and Field_ID are the composite primary key here, those are varchar fields, user have to insert Product_ID using frond end view page , Field_ID im taking using following line in view page @Html.HiddenFor(m => m.ListProductFields[i].ProductFieldID) — kez
– kez, Commented Nov 16, 2015 at 9:11

teo van kot · Accepted Answer · 2015-11-16 09:15:08Z

3

I can't see how you create your insertproductvalue but i suppose that your code should looks like this:

var listInsertproductvalue = new List<AB_Product_vs_Field>(); //you creating list of your entities
for (int i = 0; i < product.ListProductFields.Count; i++)
{
   listInsertproductvalue.Add(new AB_Product_vs_Field 
   {
      Product_ID = product.Product_ID, 
      Field_Value_EN = product.ListProductFields[i].Field_Value_EN
   }); //here you create your entity and add it to List
};
db.AB_Product_vs_Field.AddRange(listInsertproductvalue); //here you add all new entities to context
db.SaveChanges();

Here you create empty List of your entities, fill it and then add list to your Context with AddRange() method.

edited Nov 16, 2015 at 9:15

answered Nov 16, 2015 at 9:00

teo van kot

12.5k10 gold badges41 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kez Over a year ago

once I use your solution its saving last ith value , I checked it twice, with DB , what should I do

teo van kot Over a year ago

@kez i updated answer. You always can create list of entities and then use AddRange.

kez Over a year ago

@teovankot in this code snippet I have defined insertproductvalue object like below AB_Product_vs_Field insertproductvalue = new AB_Product_vs_Field(); . can I use that object instead of var listInsertproductvalue = new List<AB_Product_vs_Field>(); here ?

OrcusZ · Accepted Answer · 2015-11-16 08:59:12Z

0

You juste have to put that line :

db.AB_Product_vs_Field.Add(insertproductvalue);

In the for and keep the save outside the for.

answered Nov 16, 2015 at 8:59

OrcusZ

3,6822 gold badges33 silver badges50 bronze badges

2 Comments

kez Over a year ago

once I use your solution its saving last ith value

OrcusZ Over a year ago

try to saveChange on the for. Could solve the problem.

Kaushal B · Accepted Answer · 2015-11-16 09:31:30Z

0

I am assuming few thing here, but it has a very short and sweet way of doing it. I am a little bit blind as I need more information on the code, that is variable declarations

Lets say product.ListProductFields is your List of some object which has all the rows to be inserted:

First Way :

product.ListProductFields.Foreach(objEach=>{
db.AB_Product_vs_Field.Add((objEach); /* First we can get, .productId, .FeildId etc inside the function*/
// OR 
// db.AB_Product_vs_Field.Add((objEach.Product_ID, objEach.ProductFieldID, objEach.Field_Value_EN;);

});
db.SaveChanges();

There is one more easier way that is to have a List<<<insertproductvalue>>> where it is list of your custom class object where each object will be added with properties and the object will be added to the list of you custom class object and then again we can use the upper code to insert the rows into db.

Second Way :

var lstinsertproductvalue=new List<<*CLASSTYPE*>>();
for (int i = 0; i < product.ListProductFields.Count; i++)
            {
insertproductvalue=new <<*CLASSTYPE*>>();
                insertproductvalue.Product_ID = product.Product_ID;
                insertproductvalue.Field_ID = product.ListProductFields[i].ProductFieldID;
                insertproductvalue.Field_Value_EN = product.ListProductFields[i].Field_Value_EN;
lstinsertproductvalue.Add(insertproductvalue); 
// Here you have created a list your-self assuming that product.ListProductFields is not a list. 
            };

lstinsertproductvalue.Foreach(objEach=>{
db.AB_Product_vs_Field.Add((objEach); /* First we can get, .productId, .FeildId etc inside the function*/
// OR 
// db.AB_Product_vs_Field.Add((objEach.Product_ID, objEach.ProductFieldID, objEach.Field_Value_EN;);    
});
db.SaveChanges();

edited Nov 16, 2015 at 9:31

answered Nov 16, 2015 at 9:21

Kaushal B

467 bronze badges

5 Comments

teo van kot Over a year ago

that's not very good idea call db.SaveChanges(); on each entity add.

kez Over a year ago

getting this error

'IList<AB_ProductTypeCategoryField>' does not contain a definition for 'Foreach' and no extension method 'Foreach' accepting a first argument of type 'IList<AB_ProductTypeCategoryField>' could be found

Kaushal B Over a year ago

@kez ; Can you please give me the complete related code that is the variable declaration too, please as it will really help me to update you properly.

Kaushal B Over a year ago

@teovankot ; Thanks I had forgotten to take it outside anyways have updated the code accordingly.

Kaushal B Over a year ago

@kez : You can try the second method, I was having a feeling that this error might occur, so only had edited the post and kept the second way too.

Collectives™ on Stack Overflow

inserting multiple lines to a database table using LINQ query

3 Answers 3

3 Comments

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related