How to Insert Multiple Rows in a foreach command with LINQ?

Question

I wrote these lines:

foreach (var catId in CatIds)
{
    AdCategory.AdId = LastAd.AdID;
    AdCategory.CategoryId = catId;
    EngineDB.Ad_Categories.InsertOnSubmit(AdCategory);
    EngineDB.SubmitChanges();
}

and CatIds is an Integer Array.
this command inserts first element correctly but next loop causes this exception:
"Cannot add an entity that already exists."
How can I fix it.
please help me as soon as posible!

Answer 1

You need to create a new AdCategory in the loop. Otherwise, as the error states, you are tyring to insert the same object again and again.

You should also move

EngineDB.SubmitChanges();

outside of the loop so you only make 1 call to the database.

Answer 2

as Jimmie said you need to create a new AdCategory in the loop. You also probably don't want to call SubmitChanges every time as well, this call uses a transaction to make sure all the items are added at once, or none at all.

Try something like:

foreach (var catId in CatIds)
{
    var AdCategory = new AdCategory()
    {
        AdId = LastAd.AdID,
        CategoryId = catId
    }
    EngineDB.Ad_Categories.InsertOnSubmit(AdCategory);
}

EngineDB.SubmitChanges();