0

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; }

}
wwarby
  • 1,873
  • 1
  • 21
  • 37
  • One of you're guid's should be `Guid?` which would denote it's not the required side... – Erik Philips Jan 18 '18 at 16:25
  • *..which is essentially a collection..* Then why you added reference navigation property (one-to-one) instead of **collection** property (one-to-many)? – Ivan Stoev Jan 18 '18 at 16:33
  • @erik: The User side is the one that has an optional FeeEarner, but User doesn't have a foreign key guid property (e.g. FeeEarnerId) to make nullable. Are you suggesting I should add one? I would be concerned that doing so would alter the database schema. – wwarby Jan 19 '18 at 09:36
  • @IvanStoev: there is no collection involved in this relationship (at least not in the logical sense). A user may have zero or one FeeEarners, and a FeeEarner must have exactly one User. I am loathed to describe the relationship as a collection of FeeEarner on User, since that would imply to the development team that there can be more than one, which isn't true. Are you suggesting that is the only way to create a navigation property from User to FeeEarner is to describe it as a collection? – wwarby Jan 19 '18 at 09:43
  • No, I was just referring to your explanation of the domain model which logically conflicts with the desired relationship type. If you want one-to-one, that's ok, I was just asking if you really want that :) – Ivan Stoev Jan 19 '18 at 09:46
  • 1
    But note that one-to-one relationships have limitations in EF6. I have answered many questions like this, so don't want to repeat myself again. See for instance https://stackoverflow.com/questions/45772713/how-to-configure-one-to-zero-and-one-to-one-relationship-in-fluent-api-with-diff/45773052#45773052 or https://stackoverflow.com/questions/48316803/ef6-one-to-one-relationship-with-separate-id-property/48317416#48317416 or https://stackoverflow.com/questions/43053021/how-do-i-specify-the-foreign-key-in-a-one-to-one-zero-relationship/43053532#43053532 etc. – Ivan Stoev Jan 19 '18 at 09:50
  • @wwarby FeeEarner on user can never be populated by EF without a FK. So you either need to add it as `Guid?` or remove the property. – Erik Philips Jan 19 '18 at 16:31
  • Thanks all. @Ivan - I got a working solution to my problem using one of the other questions you referenced. I ended up using an ICollection on User, which means I have to query for the FeeEarner in a slightly unnatural way, but it works. Happy to give you credit if you post those links as the answer to this question. – wwarby Jan 22 '18 at 10:39
  • You are welcome, glad it helped solving your issue :) Appreciate the suggestion to earn some points, but link only or duplicate question answers are not tolerated here, so I'll stay with comments. You can delete the question or post self answer if you wish. Happy coding! – Ivan Stoev Jan 22 '18 at 12:57

0 Answers0