Entity Framework, strange behaviour with required reference, lazy loading

Question

This goes for both Entity Framework 4 (4.3.1) and 5.

I have a User class (to go with my Entity Framework MembershipProvider). I've removed some of the properties to simplify. The actual User is from the MVCBootstrap project, so it's not part of the same assembly as the other classes.

public class User {
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }
    [Required]
    [StringLength(256)]
    public String Username { get; set; }
}

And then I have this class:

public class NewsItem {
    public Int32 Id { get; set; }
    [Required]
    [StringLength(100)]
    public String Headline { get; set; }
    [Required]
    public virtual User Author { get; set; }
    [Required]
    public virtual User LastEditor { get; set; }
}

Then I create the database context (The DbSet for the user is in the MembershipDbContext):

public class MyContext : MVCBootstrap.EntityFramework.MembershipDbContext {
    public MyContext(String connectString) : base(connectString) { }
    public DbSet<NewsItem> NewsItems { get; set; }
}

Running this code will give me this exception when the database is being created:

Introducing FOREIGN KEY constraint 'FK_dbo.WebShop_dbo.User_LastEditor_Id' on table 'WebShop' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors.

So I change the database context:

public class MyContext : MVCBootstrap.EntityFramework.MembershipDbContext {
    public MyContext(String connectString) : base(connectString) { }
    public DbSet<NewsItem> NewsItems { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Configurations.Add(new NewsItemConfiguration());
    }
}

And this configuration:

public class NewsItemConfiguration : EntityTypeConfiguration<NewsItem> {
    public NewsItemConfiguration() {
        HasRequired(n => n.Author).WithOptional();
        HasRequired(n => n.LastEditor).WithOptional();
    }
}

Or is this wrong?

Anyway, when I run the code, the database get's created, and the database seems okay (looking at foreign key constraints etc.).

But, then I get the 10 latest NewsItems from the context, and start loading them into view models, part of this is accessing the Author property on the NewsItem. The controller doing this takes forever to load, and fails after a long, long time. When running in debug mode, I get an exception in this piece of code: this.AuthorId = newsItem.Author.Id;, then exception I get is this:

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.

It's probably something simple and stupid I'm doing wrong, I'm sure I've get similar code running on several sites, so .. what is causing this? Are my models wrong, is it the database context, or?

score 0 · Answer 1 · edited May 23 '17 at 12:21

This part

Introducing FOREIGN KEY constraint 'FK_dbo.WebShop_dbo.User_LastEditor_Id' on table 'WebShop' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors.

is actually a SQL Server issue (and an issue of many other RDBMS's). It is a complex issue resolving multiple cascade paths, and SQL Server decides just to punt and not try. See

Foreign key constraint may cause cycles or multiple cascade paths?

You were trying to configure your model to delete the child Author and LastEditor objects when the NewsItem is deleted. SQL Server won't do that.

Come to think of it... is that what you want? It seems you would want to disassociate the Author and LastEditor from the NewsItem, not delete them from the database.

Your object model requires a 1:1 relationship between NewsItem and Author, and between NewsItem and LastEditor. I'm not sure what this refers to in the code

this.AuthorId = newsItem.Author.Id;

but it seems to me, you should be making the assignment the other way around, e.g.

newsItem.Author = myAuthorInstance;

or if you include foreign key properties in your model and if you have previously saved your author instance and have an Id:

newsItem.AuthorId = myAuthorInstance.Id;

If you share the generated DB schema (relevant parts) that would make it easier to diagnose the issue.

The code snippet `this.AuthorId = newsItem.Author.Id;` is in the view model, not the POCO, so this is to store the author id in the view model. I wouldn't want to delete the author when I delete the NewsItem. — Steen Tøttrup, Sep 21 '12 at 07:32
If that were in the view model, there's no way that you should get a database-related error on that line... — Eric J., Sep 23 '12 at 08:06

score 0 · Answer 2 · answered Sep 21 '12 at 07:09

0

User can be an author of several news items. Also, user can be editor of several news items.
Hence, relationship have to be "one-to-many":

public class NewsItemConfiguration : EntityTypeConfiguration<NewsItem> {
    public NewsItemConfiguration() {
        HasRequired(n => n.Author).WithMany();
        HasRequired(n => n.LastEditor).WithMany();
    }
}

answered Sep 21 '12 at 07:09

Dennis

37,026
10
82
150

Yes, of course, unfortunately I still get the same error with "Introducing FOREIGN KEY constraint" etc. – Steen Tøttrup Sep 21 '12 at 13:03

Entity Framework, strange behaviour with required reference, lazy loading

2 Answers2