How do i add more than one objects to a database using for each

Question

i need to add an object to a database several times with a different Sku code.

I have the Sku code changing in the for each loop but i don't know how to add the products to the database in the same method without getting this error, i think i might need to make it async but not sure how.

here is the code

public static PetersContext db = new PetersContext();
    static void Main(string[] args)
    {
        var sizeList = from ProductSizes in db.Sizes
                       where ProductSizes.SizeScale == 1//product.SizeScale.SizeScaleId
                       select (ProductSizes.SizeDesc);

        var products = from Product in db.Products
                       select Product;

        Product p1 = new Product()
        {
            ProductBrand = 1,
            ProductCode = "Ts102",
            CostPrice = 1,
            SellPrice = 2,
            ProductDescription = "Ted Smith Shirt",
            ProductSeason = 1,
            ProductType = 1,
        };
        foreach (var size in sizeList)
        {
            p1.ProductSkus = (p1.ProductCode + p1.ProductBrand.ToString() + p1.ProductColour.ToString() + size.ToString());

            Console.WriteLine(p1.ProductSkus);
            db.Products.Add(p1);
            db.SaveChanges();
        }

        Console.ReadLine();
    }
}

Answer 1

Thanks for yer help people, I created a new object in the for each, changed the sku in the new object, i forgot i need to give the products a size so i did that in a linq query in the same loop and added it to a product list, the product was then looped through in another for each and added to the db, it could do with a refactoring but it will do for now, thanks again guys

public static PetersContext db = new PetersContext();
    static void Main(string[] args)
    {
        var sizeList = from ProductSizes in db.Sizes
                       where ProductSizes.SizeScale == 1//product.SizeScale.SizeScaleId
                       select (ProductSizes.SizeDesc);

        var sizeIdList = from ProductSizes in db.Sizes
                         where ProductSizes.SizeScale == 1
                         select (ProductSizes.SizeId);

        var products = from Product in db.Products
                       select Product;

        Product p1 = new Product()
        {
            ProductBrand = 1,
            ProductCode = "Ts102",
            CostPrice = 27,
            SellPrice = 79,
            ProductDescription = "Ted Smith Shirt",
            ProductSeason = 1,
            ProductType = 1,
            ProductColour=1
        };
        IList<Product> newProductList = new List<Product>();
        foreach (var size in sizeList)
        {

            string newSku = (p1.ProductCode + p1.ProductBrand.ToString() + p1.ProductColour.ToString() + (size.ToString()));
            Product newProduct = new Product()
            {
                ProductBrand = p1.ProductBrand,
                ProductCode = p1.ProductCode,
                CostPrice = p1.CostPrice,
                SellPrice = p1.SellPrice,
                ProductDescription = p1.ProductDescription,
                ProductSeason = p1.ProductSeason,
                ProductType = p1.ProductType,
                ProductColour = p1.ProductColour,
                ProductSkus= newSku,
            };
                newProduct.ProductSkus = newSku;

                var SizeId =(from ProductSize in db.Sizes
                                         where ProductSize.SizeDesc == size
                                          select ProductSize.SizeId).First();
            newProduct.ProductSize = SizeId;

            newProductList.Add(newProduct);
        }
        foreach (var product in newProductList)
        {

            db.Products.Add(product);
            db.SaveChanges();
        }
        Console.ReadLine();

Answer 2

There are other changes you could make, but the the big one I'm seeing is there's only one product reference. That same product object is added to the product collection several times in a loop. Each time, the loop also sets a new Sku... but since they are all the same object, the references from prior iterations of the loop reflect the new data.

To fix this, you need a new product object each time through the loop. You can offset that performance by moving the db.SaveChanges() call to after the loop.

Answer 3

I think your issue is the fact your foreach loop is evaluating the query during the loop when you are trying to call Savechanges() which wants to generate another transaction.

If you just change your SizeList and Product queries to have .ToList() at the end, this will force evaluation and you will then be using lists in your foreach, not a transactional query.

Updated to reflect the comments:

Looking at your code it looks like the ProductSku is a property of Product - you should perhaps consider making SKU a separate table so that you don't need to repeat all the standard product properties. However, to give what I think you are asking for you need something like this;

static void Main(string[] args)
{
    using (PetersContext db = new PetersContext()) {
        var sizeList = from ProductSizes in db.Sizes
                       where ProductSizes.SizeScale == 1//product.SizeScale.SizeScaleId
                       select (ProductSizes.SizeDesc);

        var products = from Product in db.Products
                       select Product;

        foreach (var size in sizeList)
        {
            foreach (var product in products)
            {
                Product newProduct = new Product()
                {
                    ProductSkus = (product.ProductCode + product.ProductBrand.ToString() + product.ProductColour.ToString() + size.ToString()),
                    ProductBrand = product.ProductBrand,
                    ProductCode = product.ProductCode,
                    CostPrice = product.CostPrice,
                    SellPrice = product.SellPrice,
                    ProductDescription = produce.ProductDescription,
                    ProductSeason = product.ProductSeason,
                    ProductType = product.ProductType
                };
                Console.WriteLine(p1.ProductSkus);
                db.Products.Add(newProduct);
            }
        }
        db.SaveChanges();

        Console.ReadLine();
    } 
}

Answer 4

There are three things I would do differently in your approach.

1. You should create your context within a using statement.

2. Move the save changes function outside your for loop.

3. Create your p1 within the loop.

See the changes below:

using(var db = new PetersContext()) //open connection
{
    var sizeList = from ProductSizes in db.Sizes
                   where ProductSizes.SizeScale == 1//product.SizeScale.SizeScaleId
                   select (ProductSizes.SizeDesc);

    var products = from Product in db.Products
                   select Product;


    foreach (var size in sizeList)
    {
        Product p1 = new Product() //Moving to inside of loop creates new instance every time
        {
            ProductBrand = 1,
            ProductCode = "Ts102",
            CostPrice = 1,
            SellPrice = 2,
            ProductDescription = "Ted Smith Shirt",
            ProductSeason = 1,
            ProductType = 1,
        };
        p1.ProductSkus = (p1.ProductCode + p1.ProductBrand.ToString() + p1.ProductColour.ToString() + size.ToString());

        Console.WriteLine(p1.ProductSkus);
        db.Products.Add(p1);
    }
    db.SaveChanges(); //save changes after everything is done.
}                                                          
Console.ReadLine();