Entity Framework Code First Self Join, 'Multiplicity is not valid in Role'

Question

I am stuck at this error and can not find the solution to this. I have tried several things but could not come up with a solution.

Here is my problem:

Code:

namespace ProjectTracker.Database.Entities
{
    [DataContract]
    public class User
    {
        [DataMember]
        public int Id { get; set; }

        [Required]
        [MaxLength(50)]
        [DataMember]
        public string UserName { get; set; }

        [Required]
        [MaxLength(100)]
        [DataType(DataType.Password)]
        [DataMember]
        public string Password { get; set; }

        [DataMember]
        public bool IsPasswordExpired { get; set; }

        [Required]
        [DataMember]
        public DateTime CreatedDate { get; set; }

        [Required]
        [ForeignKey("CreatedBy")]
        [DataMember]
        public int CreatedByUserId { get; set; }

        [DataMember]
        public virtual User CreatedBy { get; set; }

        [Required]
        [DataMember]
        public DateTime LastUpdatedDate { get; set; }

        [ForeignKey("LastUpdatedBy")]
        [DataMember]
        public int? LastUpdatedByUserId { get; set; }

        [DataMember]
        public virtual User LastUpdatedBy { get; set; }
    }
}

and Here are the exception details that I am getting while calling it from a web service:

Request Error The server encountered an error processing the request. The exception message is 'One or more validation errors were detected during model generation: \tSystem.Data.Entity.Edm.EdmAssociationEnd: : Multiplicity is not valid in Role 'User_LastUpdatedBy_Source' in relationship 'User_LastUpdatedBy'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be '*'. '. See server logs for more details. The exception stack trace is:

at System.Data.Entity.DbModelBuilder.Build(DbProviderManifest providerManifest, DbProviderInfo providerInfo) at System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection) at System.Data.Entity.Internal.LazyInternalContext.CreateModel(LazyInternalContext internalContext) at System.Data.Entity.Internal.RetryLazy2.GetValue(TInput input) at System.Data.Entity.Internal.LazyInternalContext.InitializeContext() at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) at System.Data.Entity.Internal.Linq.InternalSet1.Initialize() at System.Data.Entity.Internal.Linq.InternalSet1.get_InternalContext() at System.Data.Entity.Infrastructure.DbQuery1.System.Linq.IQueryable.get_Provider() at System.Linq.Queryable.Where[TSource](IQueryable1 source, Expression1 predicate) at ProjectTracker.Database.DataAccess.DLAccess.DoesUserExist(String userName) in e:\My Own\Projects\ProjectTracker\Database\ProjectTracker.Database.DataAccess\DLAccess.cs:line 31 at ProjectTracker.Business.BLAccess.BLAccess.DoesUserExists(String userName) in e:\My Own\Projects\ProjectTracker\Business\ProjectTracker.Business.BLAccess\BLAccess.cs:line 37 at ProjectTracker.UI.Web.WS.WebAccess.DoesUserExist(String userName) in e:\My Own\Projects\ProjectTracker\UI\ProjectTracker.UI.Web\WS\WebAccess.svc.cs:line 12 at SyncInvokeDoesUserExist(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc& rpc) at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

Please advise me, what I am doing wrong here...

Answer 1

EF mapping conventions try to infer a one-to-one relationship between User.CreatedBy and User.LastUpdatedBy. This fails because both navigation properties are represented with foreign keys that are not primary keys at the same time and because EF only supports shared primary key one-to-one relationships.

Anyway, this is not important because you don't want a one-to-one relationship but actually two one-to-many relationships: A user can create many other users and a user can modify many other users.

To achieve this you must override the conventions by explicitly defining the two relationships with Fluent API:

modelBuilder.Entity<User>()
    .HasRequired(u => u.CreatedBy)  // this could be a problem, see below
    .WithMany()
    .HasForeignKey(u => u.CreatedByUserId);

modelBuilder.Entity<User>()
    .HasOptional(u => u.LastUpdatedBy)
    .WithMany()
    .HasForeignKey(u => u.LastUpdatedByUserId);

It might be necessary to make CreatedBy optional, i.e. CreatedByUserId must be of type int? and in the mapping above you must replace HasRequired by HasOptional, because otherwise you could not create the very first user without violating the FK constraint.

Possibly you could apply a trick, like creating the first user directly in the DB with CreatedByUserId allowing NULL values, assigning this user then as his own creator and then changing the DB schema so that NULL is forbidden.

Edit

More details about "EF mapping conventions try to infer a one-to-one relationship between User.CreatedBy and User.LastUpdatedBy.":

When EF analyzes your model classes during startup it uses

1. your configuration with Fluent API,
2. your applied data annotations,
3. a set of certain conventions to infer relationships from navigation properties and property names.

Those set of cenventions gets applied unless otherwise explitly specified with Fluent API or data annotations. You can find the full set of conventions here: http://msdn.microsoft.com/en-us/library/system.data.entity.modelconfiguration.conventions(v=vs.103).aspx

In case of your original User class it is the AssociationInverseDiscoveryConvention that is applied for your model and detects a one-to-one relationship. The documentation says:

Convention to detect navigation properties to be inverses of each other when only one pair of navigation properties exists between the related types.

The "only one" pair of navigation properties is CreatedBy in User that refers to User - and in User is a second navigation property LastUpdatedBy that refers back to User. (It's just a bit confusing because the "related types" are the same - User and User, but the convention applies here the same way as between different types.) Because both are references (and not collections) EF assumes that the relationship must be one-to-one (and not one-to-many or many-to-many).

Sometimes conventions don't infer the relationship as you want it. Then you must override the mapping conventions with Fluent API or data annotations. (Annotations are not sufficient in your example, you must use Fluent API here.)