EF Code First: Multiplicity constraint violation

Question

HELP! - I am receiving the following error on my navigational model ArticleType:

A relationship multiplicity constraint violation occurred

Here is the existing database schema:

Here is my code:

public class Article
{
    public int ID { get; set; }
    public virtual Stage Stage { get; set; }
    public virtual ArticleType ArticleType { get; set; } // Causes the violation
}

public class ArticleType
{
    public int ID { get; set; }
    public string Title { get; set; }
}

public class Stage
{
    public int ID { get; set; }
    public string Title { get; set; }
}

I use fluent api for my mapping, heres an excerpt of the association

// This works
modelBuilder.Entity<Article>
    .HasRequired(t => t.Stage)
    .WithMany() // if turned .WithOptional() then will also cause the error.
    .Map(m => m.MapKey("stage_id"));

// This does not work
modelBuilder.Entity<Article>
    .HasRequired(t => t.ArticleType)
    .WithMany()
    .Map(m => m.MapKey("article_type_id"));

My problem is that why is it that the ArticleType is causing the error but the Stage does not, even when both's declaration and mapping are syntactically the same?

EDIT 1

I found out about the exception by examining the Article object by hovering my mouse (not sure the exact term)

Error Detail

System.InvalidOperationException was unhandled by user code
  HResult=-2146233079
  Message=A relationship multiplicity constraint violation occurred: An EntityReference can have no more than one related object, but the query returned more than one related object. This is a non-recoverable error.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.Objects.DataClasses.EntityReference`1.Load(MergeOption mergeOption)
       at System.Data.Objects.DataClasses.RelatedEnd.Load()
       at System.Data.Objects.DataClasses.RelatedEnd.DeferredLoad()
       at System.Data.Objects.Internal.LazyLoadBehavior.LoadProperty[TItem](TItem propertyValue, String relationshipName, String targetRoleName, Boolean mustBeNull, Object wrapperObject)
       at System.Data.Objects.Internal.LazyLoadBehavior.<>c__DisplayClass7`2.<GetInterceptorDelegate>b__2(TProxy proxy, TItem item)
       at System.Data.Entity.DynamicProxies.Package_A18FADC105CCF13C9CD346622D43BD35514E489CCC1E5B1E4A3C78806BDCA0F5.get_ArticleType()
       at AuthorProofing.Service.ReminderService.DeliverDailyReminders() in C:\Users\default.Lenovo-PC\Documents\Visual Studio 2010\Projects\AuthorProofing\AuthorProofing.Service\ReminderService.cs:line 36
       at AuthorProofing.Tests.ReminderServiceTest.DeliverDailyRemindersTest() in C:\Users\default.Lenovo-PC\Documents\Visual Studio 2010\Projects\AuthorProofing\AuthorProofing.Tests\ReminderServiceTest.cs:line 76
  InnerException: 

EDIT 2

I decided to go with explicit foreign key associations.

class ArticleMap : EntityTypeConfiguration<Article>
{
    public ArticleMap()
    {
        // Primary Key
        this.HasKey(t => t.ID);
        this.Property(t => t.ID)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        // Strongly typed FK properties
        this.Property(t => t.StageID).IsRequired();
        this.Property(t => t.ArticleTypeID).IsRequired();

        // Navigation Models
        this.HasRequired(t => t.Stage);
        this.HasRequired(t => t.ArticleType);

        // Table & Column Mappings
        this.ToTable("items");
        this.Property(t => t.ID).HasColumnName("item_id");
        this.Property(t => t.StageID).HasColumnName("stage_id");
        this.Property(t => t.ArticleTypeID).HasColumnName("article_type_id");
    }
}


protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Configurations.Add(new ArticleTypeMap());
    modelBuilder.Configurations.Add(new StageMap());
    modelBuilder.Configurations.Add(new ArticleMap());
}

Still does not work.

Using the new foreign key mapping approach, I tried to switch the keys article_type_id and stage_id.

this.Property(t => t.StageID).HasColumnName("article_type_id"); // <-- Switched
this.Property(t => t.ArticleTypeID).HasColumnName("stage_id");  // <-- Switched

Suddenly, the error disappeared. Currently baffled right now. I think somehow the ArticleType model does not like the foreign key "article_type_id".

EDIT 3

After adding the .HasForeignKey(...) on my navigational model mappings, I received a new error: Unknown column 'ArticleType_ID' in 'field list'

class ArticleMap : EntityTypeConfiguration<Article>
{
    public ArticleMap()
    {
        // Primary Key
        this.HasKey(t => t.ID);
        this.Property(t => t.ID)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        // Foreign Key Properties
        this.Property(t => t.StageID)
            .IsRequired();
        this.Property(t => t.JournalID)
            .IsRequired();
        this.Property(t => t.ArticleTypeID)
            .IsRequired();

        // Navigational Models
        this.HasRequired(t => t.Stage); // This works
        this.HasRequired(t => t.ArticleType)
            .WithMany()
            .HasForeignKey(t => t.ArticleTypeID); // Newly added

        // Table & Column Mappings
        this.ToTable("items");
        this.Property(t => t.ID).HasColumnName("item_id");
        this.Property(t => t.ArticleTypeID).HasColumnName("article_type_id");
        this.Property(t => t.StageID).HasColumnName("stage_id");
    }
}

class ArticleTypeMap : EntityTypeConfiguration<ArticleType>
{
    public ArticleTypeMap()
    {
        // Primary Key
        this.HasKey(t => t.ID);

        // Properties
        this.Property(t => t.ID)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        this.Property(t => t.Title)
            .HasMaxLength(100)
            .IsRequired();

        // Table & Column Mappings
        this.ToTable("article_types");
        this.Property(t => t.ID).HasColumnName("article_type_id"); // <-- Apparently, this is no longer mapped.
        this.Property(t => t.Title).HasColumnName("title");
    }
}

Malta

I advise you to explicilty add the foreign keys to your code. So add an 'ArticleTypeID' and 'StageID' property to the 'Article' class. That said, this relations should be picked up by the EntityFramework without any need for configuration using the Fluent API or otherwise. – Holton

@Holton Thank you. But I was trying to avoid that solution so I posted this question, but I will try that as my final solution. – Malta

Why would you try to avoid that? It will make your life so much easier if you do... – Holton

can you post the full error from the debugger? there are multiple error outputs that can fall under this error category. – Diptych

@Holton - Initially I was going for a more of an object oriented approach with my associations. But after further research on "Foreign Key Associations VS Independent Associations", it looks like I will be abandoning this effort after all. A more detailed explanation: https://mcmap.net/q/121834/-what-are-independent-associations-and-foreign-key-associations-duplicate – Malta

@AndrewCounts - How do I do that? Are you talking about the full stack trace or the full error message? See my edited post. – Malta

Answer 1

When googling for the specific error messagea, the cause of this is making the mistake of configuring a one to many relationship as a one to one relationship by using:

modelBuilder.Entity<Article>
.HasRequired(t => t.ArticleType)
.WithOptional()
.HasForeignKey(...);

While it should be:

modelBuilder.Entity<Article>
.HasRequired(t => t.ArticleType)
.WithMany()
.HasForeignKey(...);

You already pointed this out in your own code example. As the code you have shown for ArticleType and Stage is identical it does not make sense that this code is the cause of the problem. Somewhere else in your code there must be a one to one relation defined between Article and ArticleType. Or in some way you originally had the relation wrong, but the corrected fluent definition is not picked up by the Entity Framework.

Holton

I decided to explicitly declare foreign key properties, the error still exists so the problem does occur somewhere. See my edited post. Thanks! – Malta

After adding the "Foreign Key Properties", I added the .HasForeignKey(...). Suddenly the "article_type_id" failed to get mapped. See my edited post. – Malta

Ok, I just found out that somewhere else, there is a one-to-one association between ArticleType and Recipient (some other class which does not even get called, yet caused the error) – Malta

Answer 2

You can take .edmx file from visual stdio and map you database with that file so that will automatically create relation ship mapping of entity as per Database schema.

Lablab

This doesnt answer the question at all, for one hes using code-first – Ickes