How to add objects with Entity Framework without its associated entity duplicated

Question

I have the following models: a Product class, a Category class, a Color class and a Size class; each Product has a Category, Color, and Size.

Information is coming from a CSV file, so before I add products into the database I do A LINQ Select distinct on category, color and a size and insert those into the appropriate tables with EF code first.

Until that point, there is no problem.

Then what I try to do, is I iterate through every line of the CSV file and for each product I get its category, its size and its color, and create the Instance object of each one of those.

  public void InsertProduct(Product product)
        {
            using (var context = new ExamContext())
            {
                ICategoryDao categoryDao = new CategoryDao();
                IColorDao colorDao = new ColorDao();
                ISizeDao sizeDao = new SizeDao();

                var category = categoryDao.GetCategoryByName(product.Category.CategoryName);
                var color = colorDao.GetColorByName(product.Color.ColorName);
                var size = sizeDao.GetSizeByName(product.Size.SizeName);

                var categoryEntity = new CategoryEntity()
                {
                    Id = category.Id,
                    CategoryName = category.CategoryName
                };

                var colorEntity = new ColorEntity()
                {
                    Id = color.Id,
                    ColorName = color.ColorName
                };

                var sizeEntity = new SizeEntity()
                {
                    Id = size.Id,
                    SizeName = size.SizeName
                };

                ProductEntity productEntity = new ProductEntity();
                productEntity.ActionPrice = product.ActionPrice;
                productEntity.ArticleNumber = product.ArticleNumber;
                productEntity.Category = categoryEntity;
                productEntity.Color = colorEntity;
                productEntity.Size = sizeEntity;
                productEntity.DeliveryTime = product.DeliveryTime;
                productEntity.Description = product.Description;
                productEntity.Key = product.Key;
                productEntity.Price = product.Price;
                productEntity.ActionPrice = product.ActionPrice;
                productEntity.Q1 = product.Q1;


                //Had issues with mapper and navigation attributes
                //var entity = Mapper.Map<Product, ProductEntity>(product);
                context.ProductEntities.Add(productEntity);
                context.SaveChanges();

                // update business object with new id
                //product.Id = entity.Id;
            }
        }

Then on the save changes I got a UNIQUE KEY Exception, something expected because category name, color name and size name are unique keys on their own tables. (not on the product table itself).

How can I insert the Product without trying to Insert Categories, Colors and Sizes?

public class SizeEntity
    {
        public int Id { get; set; }
        [Index("SizeName", IsUnique = true)]
        [MaxLength(100)]
        public string SizeName { get; set; }
    }

public class ColorEntity
    {
        public int Id { get; set; }
        [Index("ColorName", IsUnique = true)]
        [MaxLength(100)]
        public string ColorName { get; set; }
    }

public class CategoryEntity
    {
        public int Id { get; set; }
        [Index("CategoryName", IsUnique = true)]
        [MaxLength(100)]
        public string CategoryName { get; set; }
    }

Please note that the ProductDao object gets a Product (from the business object layer), not actually a Product Entity (from data access layer), so thats why I do some kind of Manual Mapping(object instance creation) on that method, which is where something is wrong.

Federico Dipuma · Accepted Answer · 2017-05-03 17:51:07Z

2

You need to fetch every entity from the database before assigning them to the product entity if you do not want to create duplicates, something like:

var categoryEntity = context.CategoryEntities.Find(category.Id);
if(categoryEntity == null)
{
    categoryEntity = new CategoryEntity()
    {
        Id = category.Id,
        CategoryName = category.CategoryName
    };
    context.CategoryEntities.Add(categoryEntity);
}

// same approach for other entities

Assuming each of those entities have Id as primary key, you need to add those entities to the context before invoking Find method.

What you have to know is that this approach is extremely inefficient if you try to execute bulk inserts. My advice is to execute context.SaveChanges() just once, and not after adding each single entity.

edited May 3, 2017 at 17:51

answered May 3, 2017 at 17:42

Federico Dipuma

18.4k4 gold badges44 silver badges58 bronze badges

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

Collectives™ on Stack Overflow

How to add objects with Entity Framework without its associated entity duplicated

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related