CODEWITHSUNDEEP
c#entity-framework
Edi G.
Edi G.

Reputation: 2420

Entity Framework add new item with related child item

I getting an error when I try to add an item with an child item.

Cannot insert explicit value for identity column in table 'Category' when IDENTITY_INSERT is set to OFF.

My classes:

public class Product
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public Category Category { get; set; }
}

public class Category
{
    public long Id { get; set; }
    public string CatName { get; set; }
}

When I add a new Product like this:

var p = new Product();
p.Name = "test";
p.Description = "test 1";

var c = new Category();
c.Id = 2;
c.CatName = "Demo";
p.Category = c;

_context.Product.Add(p);

The category (CatName = Demo, Id = 2) already exists in the database.

I get the error:

Cannot insert explicit value for identity column in table 'Category' when IDENTITY_INSERT is set to OFF.

I don't want to add a new Category. When I read the Category from the database, it works fine...

var p = new Product();
p.Name = "test";
p.Description = "test 1";
p.Category = _context.Category.Where(c => c.Id.Equals(2)).FirstOrDefault();

_context.Product.Add(p);

But I don't want to read all (there are more) child properties.

What am I doing wrong?

Upvotes: 0

Views: 1155

Answers (1)

Steve Py
Steve Py

Reputation: 34793

When you hand a context an object reference it isn't tracking as part of an Add operation it treats every object reference that it isn't tracking as an Added entity. References are everything in EF.

The context isn't tracking your Category, so how would it know that a Category object with an ID of 2 is meant to point at an existing Category or a new one?

If you can guarantee that the categories and every other reference you want will exist in the database, then you can check the context's local cache for instances (using those if found) otherwise attach the category. For example:

var c = _context.Categories.Local.SingleOrDefault(c => c.Id == 2); // Checks cache, doesn't go to DB.
if(c == null)
{
    c = new Category { Id = 2 }; // Only need PK to associate an entity.
    _context.Categories.Attach(c);
}

var p = new Product()
{ 
    Name = "test",
    Description = "test 1",
    Category = c
};

_context.Product.Add(p);

Either way you need to check the Context in the event that one or more referenced entities may be tracked. Trying to shortcut things by just creating and attaching references can result in intermittent bugs especially when dealing with larger operations or sets of entities. For example you might test or typically deal with several entities that use different categories and that works fine, however if called with 2 products that share the same category, the second product add would fail as the DbContext is already tracking a category with the same ID.

Going to the context to load related entities is not an expensive burden, and it helps ensure that your proposed updates are valid. Fetching references by ID is quite fast, and the DbContext will look through it's local cache before going to the database anyways. Even in cases where you might to update a number of products to different categories this can be optimized. For example if I have a list of product view models that have a category to associate: (Some may have the same category, or any number of different ones)

class ProductViewModel
{
     string Name { get; set; }
     string Description { get; set; }
     int CategoryId { get; set; } 
     // ...
}

var categoryIds = productViewModels.Select(p => p.CategoryId).Distinct();
var categories = _context.Categories.Where(c => categoryIds.Contains(c.Id)).ToList();

foreach(var productVM in productViewModels)
{
    var c = categories.Single(c => c.Id == productVM.CategoryId);
    var p = new Product()
    { 
        Name = productVM.Name,
        Description = productVM.Description,
        Category = c
    };
    
    _context.Product.Add(p);
}

We don't have to fetch data one by one inside a loop or such. It can be loaded in one call to the DB by inspecting what data we might need and getting the IDs we will be interested in. It is recommended though that when dealing with references you expect to find to leverage methods like Single rather than SingleOrDefault or FirstOrDefault so-as to be explicit about where you expect to find an entity. Single says "I expect to find 1 record that matches this condition" so if it doesn't find a record, or happens to find more than one record you get a meaningful exception at that point. FirstOrDefault wouldn't throw an exception if more than one row was found, it'd just pick the first one it came across, and it wouldn't throw an exception if no record was found. An exception would likely happen later where the code assumed a reference was found and tries to access a property on #null. SingleOrDefault was applicable in the first example when checking the local cache because I want to handle the possibility that a record is tracked in the cache or explicitly not yet tracked.

Upvotes: 1

Related Questions