In my project I have a User entity, and a FeeEarner entity which is essentially a collection of additional properties that some types of users have. All FeeEarner instances are linked to a User, but not all Users are linked to a FeeEarner.
I have a virtual property on FeeEarner to navigate to the User, which works. However I have a scenario where I really need to be able to navigate from the user to the FeeEarner (if it exists) from the user. Essentially I want to do something like this:
context.Users.Where(x => x.FeeEarner == null || x.FeeEarner.FirmId == somevalue);
... which I would expect to generate SQL something like this (which works):
select [User].* from [User]
left join FeeEarner on [User].id = FeeEarner.UserId
where FeeEarner.FirmId IS NULL OR FeeEarner.FirmId = 'somevalue'
These entities are very heavily used in an already released product so I cannot modify the database structure without a lot of pain. I know I could write the query the other way around (starting with the FeeEarner) but for various reasons that would not fit very well with the user case I am aiming for here.
When I add a navigation property to the User entity for FeeEarner I get:
Unable to determine the principal end of an association between the types 'FeeEarner' and 'User'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.
I've tried various DataAnnotation and FluentAPI solutions to coerce EF to understand what I'm trying to do, such as:
modelBuilder.Entity<FeeEarner>().HasRequired(t => t.User);
modelBuilder.Entity<User>().HasOptional(t => t.FeeEarner).WithOptionalPrincipal(t => t.User);
But this results in:
FeeEarner_User_Source: : Multiplicity is not valid in Role 'FeeEarner_User_Source' in relationship 'FeeEarner_User'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be '*'.
Here are my entity classes (simplified for clarity):
public class User {
[Key]
public Guid Id { get; set; }
public virtual FeeEarner FeeEarner { get; set; }
}
public class FeeEarner {
[Key]
public Guid Id { get; set; }
public Guid FirmId { get; set; }
public Guid UserId { get; set; }
[ForeignKey(nameof(UserId))]
public virtual User User { get; private set; }
}