There are already several related questions on StackOverflow but I'm having trouble making sense of the answers.
I'm using Code First (with annotations, not fluent API) and trying to create a composite primary key on one table, where the primary key is composed of two foreign keys and each foreign key points to a different table.
In a nutshell, I have a Conversations table which has (among other things) columns for EmployeeId and CustomerId. The EmployeeId column references the Employees table and the CustomerId column references the Customers table.
My models look like this (simplified somewhat):
public class Employee
{
[Key]
public Guid Id {get; set;}
[Required]
[MaxLength(100)]
public string FirstName { get; set; }
[Required]
[MaxLength(100)]
public string LastName { get; set; }
[MaxLength(20)]
public string PhoneNumber { get; set; }
}
public class Customer
{
[Key]
public Guid Id { get; set; }
[Required]
[MaxLength(100)]
public string FirstName { get; set; }
[Required]
[MaxLength(100)]
public string LastName { get; set; }
[MaxLength(20)]
public string PhoneNumber { get; set; }
}
public class Conversation
{
public Guid Id { get; set; }
[Key, Column(Order=0)]
public Guid CustomerId {get; set;}
[Key, Column(Order=1)]
public Guid EmployeeId {get; set;}
[ForeignKey("CustomerId")]
public Customer Customer {get; set;}
[ForeignKey("EmployeeId")]
public Employee Employee {get; set;}
// ... snip other unimportant properties
}
Attempting to look up an employee by phone number, like this...
public Employee FindByPhoneNumber(string phoneNumber)
{
return _context.Employees.SingleOrDefault(e => e.PhoneNumber.Equals(phoneNumber));
}
...results in the following error:
System.Data.Entity.Edm.EdmAssociationConstraint: The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical.
What am I missing? I need to ensure that there is only ever one Conversation record between a given employee and customer, and both IDs have to be valid.
