Given the following model classes:
Public class User
{
public int UserId { get; set; }
public string Name { get; set; }
public List<Rule> Rules { get; set; }
}
public class Rule
{
public int RuleId { get; set; }
public string RuleName { get; set; }
public Owner Owner { get; set; }
public Manager Manager { get; set; }
}
public class Owner
{
public int OwnerId { get; set; }
public string Code { get; set; }
}
public class Manager
{
public int ManagerId { get; set; }
public string Code { get; set; }
}
With Entity Framework 6, I receive a list of users from an API call which need to be added.
After mapping into the dbContext type, I attempt to add the list of users via context.Users.Add(users) essentially I do not want to add the navigation properties i.e Owner and manager, I'm only interested in these for Foreign key reference purposes but EF attempts to add these into the database even though they already exist and then throws an exception.
I got this working by newing up instances of what I need to add and then ignore the navigational properties. However what I'd like is to do the insert in one SaveChanges() whilst ignoring the Owner and Manager properties except their ids which serves and a FK value in the Rules table.
I assume there is a much better way to work around this than the recursively new up items to be added.
References and tracking are important factors when working with EF entities. When you add a new entity to EF that contains navigation properties to entities that this DbContext instance is not tracking, it will treat these entities as new ones that need to be added whether a record exists in the DB or not. This causes problems when you assume it should be able to work out that you mean to reference an existing Manager or Owner.
So say you want to insert a new user with a set of new Rules. Each rule will reference an existing Owner and Manager. What we pass in can either be Owner and Manager entities, or we could just pass a DTO for the Rule that has an OwnerId and ManagerId.
Option 1: With an Owner and Manager "entity". We want EF to recognize these entities as pointing to existing rows. The simple way to do this is to Attach them. However there is a caveat that the DbContext might already be tracking an instance with those IDs, so we need to check the local tracking cache and if we find an instance with the same ID, replace the reference before we save our new entitiy:
foreach (var rule in user.Rules)
{
var existingOwner = _context.Owners.Local
.FirstOrDefault(x => x.OwnerId == rule.Owner.OwnerId);
if (existingOwner != null)
rule.Owner = existingOwner;
else
_context.Attach(rule.Owner);
var existingManager = _context.Managers.Local
.FirstOrDefault(x => x.ManagerId == rule.Manager.ManagerId);
if (existingManager != null)
rule.Manager = existingManager;
else
_context.Attach(rule.Manager);
}
_context.Users.Add(user);
_context.SaveChanges();
This checks the Context's Local caches for tracked references. If we find one, we replace the reference on our new User Rule before saving. If we don't, we can safely attach it. (Assuming that owner or manager actually exist in the DB) The downsides of this approach is what happens if we pass an Owner or Manager that doesn't exist in the DB. An exception would be raised on SaveChanges(). This option also poses issues if the entity for the Owner or Manager wasn't "complete" and we attach it. For instance if an Owner has additional properties or navigation properties that weren't populated since we only really needed the ID. When we Attach it, for the remainder of the request on that DbContext, any calls to get that Owner would return that incomplete instance. If you are ever attaching stubbed, incomplete entities, or ones containing data that might not be accurate/up-to-date then after saving your changes it is a good idea to detach any of those stubs. That way if the DbContext is asked for that Owner, it would go to the DB rather than serve up an incomplete stub.
Option 2: With an OwnerId and ManagerId. In this case if we are passing a DTO or such rather than entities and constructing a new User and Rules to add, we can read our Owners and Managers from the DbContext. In this case we pass around a DTO or ViewModel for the user & rules where the rules just have IDs for the Manager and Owner.
var user = new User(userDTO); // copy values from DTO.
var ownerIds = userDTO.Rules.Select(x => x.OwnerId).ToList();
var managerIds = userDTO.Rules.Select(x => x.ManagerId).ToList();
var owners = _context.Owners
.Where(x => ownerIds.Contains(x.OwnerId))
.ToList();
var managers = _context.Managers
.Where(x => managerIds.Contains(x.ManagerId))
.ToList();
foreach (var ruleDTO in userDTO.Rules)
{
var owner = owners.First(x => x.OwnerId == ruleDTO.OwnerId);
var manager = managers.First(x => x.ManagerId == ruleDTO.ManagerId);
user.Rules.Add(new Rule(ruleDTO, owner, manager));
}
_context.Users.Add(user);
_context.SaveChanges();
This code accepts a DTO containing fields for the new User and Rules which the rule contains just the IDs for the existing Owner and Manager to reference. We get those Ids so that we can read all of the referenced Owners and Managers in one DB call for each set, then as we go through each RuleDTO to add a new Rule to our new User, we locate the matching, tracked Owner and Manager reference to associate to our new Rule. The calls to First here will throw an exception if we happened to get an OwnerId or ManagerId that didn't exist in the DB. The exception in that case will happen in a meaningful spot to identify what is missing rather than on SaveChanges. The constructors for the entities in this case are just accepting the DTO to copy values from. You could manually fill them out in-line or use something like Automapper to construct them from the DTO. In the case of the Rule constructor we pass the DTO for values for the rule and the references to the found Owner and Manager. When The DbContext adds the User and Rules as new entities, these rules will have references to known, tracked Owners and Managers.