EF 6 add object with existing foreign key

Question

I'm using Entity Framework, and I have table Employee and Position. Employee has reference to Position.

Table("Employee")]
public partial class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    public string Phone { get; set; }
    public int Position_Id { get; set; }
    public decimal Salary { get; set; }

    public virtual Position Position { get; set; }

and

     [Table("Position")]
public partial class Position
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Position()
    {
        Employee = new HashSet<Employee>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Employee> Employee { get; set; }
}

In Position I already have items and I get them to dropdown. All I want to do is create new record of Employee with item of Position, which Id I have from dropdown. I don't want create a new Position. I tried this:

PositionId is a property and ToEmployee() is a method in viewModel

public Data.Models.Employee ToEmployee()
    {
        return new Data.Models.Employee
        {
            Name = Name,
            Surname = Surname,
            Phone = Phone,
            Salary = Salary.HasValue ? Salary.Value : 0,
            Position = new Data.Models.Position { Id = PositionId } 
        };
    }

adding in controller:

var toModel = viewModel.ToEmployee();
_employeeService.Create(toModel);

But this create new Employee and new Position, but I want only have new Empoloyee and relationship between Employee and Position by foreing key. How to do it in EF?

When I tried this in ToEmployee():

Position_Id = PositionId

returns error InvalidColumn.

Thanks for help!

Answer 1

In my case i needed to change my query tracking behavior from NoTracking to TrackAll

builder.Services.AddDbContext<DataContext>(opt =>
        opt.UseSqlServer(builder.Configuration.GetConnectionString("connectionstring"))
        .EnableSensitiveDataLogging()
        .UseQueryTrackingBehavior(QueryTrackingBehavior.TrackAll)); // <- Here

Answer 2

Apparently a Position has zero or more Employees and every Employee belongs to exactly one Position. A true one-to-many relation..

A see that you chose not to follow the entity framework naming conventions. The one that I think causes the problem is that you didn't use the default naming for the foreign key to the Position. You used Position_Id instead of PositionId.

The following would have been standard. Because this standard follows the naming conventions there is no need for fluent API nor any attributes

class Position
{
    public int Id {get; set;}
    // a Position has zero or more Employees:
    public virtual ICollection<Employee> Employees {get; set;}
    ...
}
class Employee
{
    public int Id {get; set;}
    // every Employee belongs to exactly one Position using foreign key:
    public int PositionId {get; set;}
    public virtual Position Position {get; set;}
    ... 
}

If you really have good reasons to use a different identifier for your foreign key you'll have to tell entity framework that which identifier holds the foreign key. This is done in DbContext.OnModelCreating

public override void OnModelCreating(...)
{
    modelBuilder.Entity<Position>()
         // a position has zero or more Employees via property Employees
         .HasMany(position => position.Employees)
         // every Employee has exactly one Position
         .WithRequired(employee => employee.Position)
         // using foreign key Position_id
         .HasForeignKey(employee => employee.Position_Id);
}

Now whenever you have an existing Position, with a valid PositionId, and you want to add an Employee that has this Position, you can either chose to fill the PositionId, or the Position:

Position existingPosition = ...;

Employee Employee1 = myDbContext.Employees.Add(new Employee()
{
    Position = existingPosition;
    ...
}
Employee Employee2 = myDbContext.Employees.Add(new Employee()
{
    PositionId = existingPosition.Id;
    ...
}

Both will work. Entity Framework is smart enough to use only the Position.Id in the SQL statement.

Similarly you can add a new Position and Employee in one statement:

Employee Employee1 = myDbContext.Employees.Add(new Employee()
{
    Position = new Position() {...}
    ...
}

Or add a new Position with several new Employees:

Position addedPosition = new Position()
{
    Employees = new List<Employee>()
    {
        new Employee() {...},
        new Employee() {...},
        new Employee() {...},
    }
    ...
}

There are several other deviations. They probably won't cause the problems, but if there is no reason for it, why deviate from the standards?

• The property that holds your ICollection<Employee> is not named in plural Employees.
• You let a constructor create a HashSet, which is not used in queries

A DbSet repsesents a table in your database. Are you sure this table has a HashSet? If you perform a query for a Position with its Employees and you check in the debugger the actual class of the Employee collection, you'll see that it is not a HashSet. So why create one?

Answer 3

Look up the Position object in the DB and pass that to ToEmployee() method

    public Data.Models.Employee ToEmployee(Data.Models.Position position)
    {
        return new Data.Models.Employee
        {
            Name = Name,
            Surname = Surname,
            Phone = Phone,
            Salary = Salary.HasValue ? Salary.Value : 0,
            Position = position 
        };
    }