Entity Framework - 3 tables have relationships with each other

Question

I have 3 tables as follows:

ApplicationUser:

public class ApplicationUser : IdentityUser
{
    ..some basic properties..

    // navigation properties
    public virtual ICollection<Post> Posts { get; set; }
    public virtual ICollection<Album> Albums { get; set; }
}

Post:

public class Post
{
        public long Id { get; set; }    
        public string Content { get; set; }

        public int? AlbumId { get; set; }
        public string UserId { get; set; }

        public virtual ApplicationUser User { get; set; }
        public virtual Album Album { get; set; }
}

Album:

public class Album
{
        public int Id { get; set; }
        public string Name { get; set; }
        public string UserId { get; set; }

        public virtual ApplicationUser User { get; set; }
        public virtual ICollection<Post> Posts { get; set; }
}

and finally ApplicationDbContext:

modelBuilder.Entity<ApplicationUser>()
                .HasMany(a=>a.Posts)
                .WithRequired(a=>a.User)
                .HasForeignKey(a=>a.UserId)
                .WillCascadeOnDelete(false);
modelBuilder.Entity<Post>()
                .HasKey(p => p.Id);

modelBuilder.Entity<Album>()
                .HasKey(a => a.Id);

modelBuilder.Entity<ApplicationUser>()
                .HasMany(u=>u.Albums)
                .WithOptional()
                .HasForeignKey(a=>a.UserId)
                .WillCascadeOnDelete();

modelBuilder.Entity<Album>()
                .HasMany(a=>a.Posts)
                .WithRequired()
                .HasForeignKey(p=>p.AlbumId)
                .WillCascadeOnDelete();

When I run the migration and update database, I get an error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Posts_dbo.Albums_AlbumId". The conflict occurred in database "aspnet-Link-20161012104217", table "dbo.Albums", column 'Id'.

Could anyone tell my why they conflict? It seems pretty legit to me.

Answer 1

In your code you set AlbumId as nullable but in configuration defined WithRequeired():

public class Post
{
    public long Id { get; set; }    
    public string Content { get; set; }

    public int? AlbumId { get; set; }       //<-- this one
    public string UserId { get; set; }

    public virtual ApplicationUser User { get; set; }
    public virtual Album Album { get; set; }
}

modelBuilder.Entity<Album>()
            .HasMany(a=>a.Posts)
            .WithRequired() //<-- this one
            .HasForeignKey(p=>p.AlbumId)
            .WillCascadeOnDelete();

If AlbumId is nullable you should change the configuration:

//Ef by default conventions set the AlbumId as foreign key
modelBuilder.Entity<Album>()
            .HasMany(a=>a.Posts)
            .WithOptional(a=>a.Album);

and if AlbumId isn't nullable change the property:

public class Post
{
    public long Id { get; set; }    
    public string Content { get; set; }

    public int AlbumId { get; set; }     
    public string UserId { get; set; }

    public virtual ApplicationUser User { get; set; }
    public virtual Album Album { get; set; }
}

and use following configuration:

//Ef by default conventions set the AlbumId as foreign key
modelBuilder.Entity<Album>()
            .HasMany(a=>a.Posts)
            .WithRequired(a=>a.Album)
            .WillCascadeOnDelete();