CODEWITHSUNDEEP
c#asp.net.netentity-framework
Deathspike
Deathspike

Reputation: 8770

Entity Framework Code First double relation to same class

I have a Page class that is to be associated with similar pages. Each association has additional information about the association. This is the class that has been defined as the representation of a Page.

public class Page {
    [Key]
    public virtual int Id { get; protected set; }
    [Required]
    [StringLength(32)]
    public virtual string Name { get; set; }
    [InverseProperty("Page")]
    public virtual ICollection<Association> Associations { get; set; }
}

Each page has can be associated with any number of other pages. This is to be defined as a source and a target page. I do not mind if the association is unidirectional or bidirectional, either will be justified in my particular scenario (I prefer bidirectional associations, tough). This is the association class..

public class Association {
    [Key, Column(Order = 0)]
    public virtual int PageId { get; protected set; } // SOURCE
    [Required]
    public virtual Page Page { get; set; } // SOURCE
    [Key, Column(Order = 1)]
    public virtual int TargetId { get; protected set; } // TARGET
    [Required]
    public virtual Page Target { get; set; } // TARGET
    [Required]
    [StringLength(32)]
    public virtual string InformationAboutTheAssociation { get; set; }
}

Now I have the following context ...

public class DbCtx : DbContext {
    public DbSet<Association> Associations { get; set; }
    public DbSet<Page> Pages { get; set; }
}

And the issue is that SQL Express is complaining about possible cyclic references (which is not the case, but it is over-protective). How do I solve the scheme that is generated to accept the described scenario?

Introducing FOREIGN KEY constraint 'FK_dbo.Associations_dbo.Pages_TargetId' on table 'Associations' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Thanks!

Upvotes: 4

Views: 2486

Answers (1)

amb
amb

Reputation: 1609

RESPONSE

Do what the error tells you. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION. Since you have two foreign keys to the same entity, and model first (probably) inserts by default on delete cascade, you must disable this, because when a page is deleted, the association will try to be deleted by the first foreign key but will fail because of the second foreign key.

Modify 3

public class DbCtx : DbContext {
public DbSet<Association> Associations { get; set; }
public DbSet<Page> Pages { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        modelBuilder.Entity<Association>()
            .HasRequired(x => x.Page)
            .WithMany(x => x.Associations)
            .WillCascadeOnDelete(false);
    }

}

I think this should work.

Deleting a Page entity

Because you don't have on delete cascade on the Association if you try and delete a Page it will fail if you have Association entities that depend on the Page entity you want to delete.

So when you want to delete a Page you must first delete all the associations referencing the Page. You have a foreign constraint (two of them actually) from the Association to the Page. Use only one of them and be consistent (either Page or Target).

Since you have asked this question I can only conclude you don't really know how SQL works so it is better if you also read a book about designing a database and using SQL.

I haven't used code first in EF but considering an Entity Relation diagram, Page has "1 to many" with Association and Association has two "1 to 1" with Page.

Isn't this cyclic because it seems like and it's a bad design.

Normalize the database.

If you would design the database (database first), you wouldn't need the relation "1 to many" between the Page and Association because you already have the relation "1 to 1" (two of them) from Association to Page. So if you want to look for a specific association knowing the pagId you can have a select * from association where pageId or targetId = the id you are looking for.

If you would use EF with database first, you would have a back reference from page to association (what you are trying to do here) but not in the actual Entity Relation model of the database itself. They are virtual references.

EDIT:

I reread the question and the problem is the second reference from the Association to the Page. It is strange since the database first works fine with this case.

I will look into it.

Upvotes: 4

Related Questions