Entity framework code first generate database incorrect

Question

I think entity framework has problem while generating database in my project. That's strange that it only happens in one case. This is one to many relationship between "User" and "Playlist". One User has many Playlists.

Here is my code, I used some abstract classes in my project. Core code

Playlist class

public virtual User User { get; set; }

User class

public virtual ICollection<Playlist> Playlists { get; set; }

The full code:

Generic class:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;

namespace xxx.Areas.admin.Models
{
    public abstract class Generic
    {
        [Display(Name = "Ngày tạo")]
        public DateTime? Created { get; set; }
        [Display(Name = "Lần sửa cuối")]
        public DateTime? Modified { get; set; }
        [Display(Name = "Trạng thái")]
        public bool? IsActive { get; set; }
    }
}

Post class:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;

namespace xxx.Areas.admin.Models
{
    public abstract class Post : Generic
    {
        public string Title { get; set; }
        public string Slug { get; set; }
        public string Content { get; set; }
        public string Image { get; set; }
        public int Views { get; set; }
        public bool? AllowComment { get; set; }

        public User ModifiedBy { get; set; }
        public virtual ICollection<Media> Medias { get; set; }
    }
}

AlbumBase class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using xxx.Areas.admin.Models.SongBase;

namespace xxx.Areas.admin.Models.AlbumBase
{
    public abstract class AlbumBase : Post
    {
        public bool IsPublic { get; set; }
        public bool IsFeatured { get; set; }

        public int OldID { get; set; }
        public string OldSlug { get; set; }

        public virtual ICollection<Comment> Comments { get; set; }
    }
}

Playlist class:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
using xxx.Areas.admin.Models.SongBase;

namespace xxx.Areas.admin.Models.AlbumBase
{
    public class Playlist : AlbumBase
    {
        [Key]
        public int PlaylistID { get; set; }

        public virtual ICollection<Song> Songs { get; set; }
        public virtual ICollection<Folk> Folks { get; set; }
        public virtual ICollection<Instrumental> Instrumentals { get; set; }
        public virtual User User { get; set; }

        public Playlist()
        { }

        public Playlist(string name)
        {
            Title = name;
            Slug = Functions.URLFriendly(Title);
            Views = 0;
            OldID = 0;
            AllowComment = true;
            IsActive = true;
            IsPublic = false;
            IsFeatured = false;
            Created = DateTime.Now;
        }
    }
}

and User class:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
using baicadicungnamthang.Areas.admin.Models.AlbumBase;
using baicadicungnamthang.Areas.admin.Models.Social;
using baicadicungnamthang.DAL;
using ICB;

namespace xxx.Areas.admin.Models
{
    public class User : Generic
    {
        [Key]
        public int UserID { get; set; }
        [Required(ErrorMessage = "Bạn phải nhập tên tài khoản"), StringLength(50)]
        public string UserName { get; set; }
        public string Password { get; set; }
        public string HashPassword { get; set; }
        [Required(ErrorMessage = "Bạn phải nhập địa chỉ email"), EmailAddress(ErrorMessage = "Địa chỉ email không hợp lệ")]
        public string Email { get; set; }
        [StringLength(50)]
        public string NickName { get; set; }
        public string FullName { get; set; }
        public string Slug { get; set; }
        public string Title { get; set; }
        public string Phone { get; set; }
        public string Avatar { get; set; }
        public DateTime? DOB { get; set; }
        [StringLength(1)]
        public string Gender { get; set; }
        public string Address { get; set; }
        public int TotalLikes { get; set; }
        public int TotalComments { get; set; }
        public int Views { get; set; }
        public string ActivationKey { get; set; }
        public string RecoverKey { get; set; }
        public DateTime? LastLogin { get; set; }
        public int OldID { get; set; }

        public virtual Role Role { get; set; }
        public virtual ICollection<Comment> Comments { get; set; }
        public virtual ICollection<Comment> RateComments { get; set; }
        public virtual ICollection<Playlist> Playlists { get; set; }
        public virtual ICollection<User> Friends { get; set; }
        public virtual ICollection<Message> MessagesSent { get; set; }
        public virtual ICollection<Message> MessagesReceived { get; set; }

        public User()
        {
            Created = DateTime.Now;
            IsActive = false;
            TotalLikes = 0;
            Views = 0;
            OldID = 0;
        }

        public string getAvatar(int w, int h)
        {
            return Functions.getAvatarThumb(UserName, w, h);
        }

        public int getAge()
        {
            if (DOB == null)
            {
                return 0;
            }
            else
            {
                DateTime now = DateTime.Now;
                int age = now.Year - DOB.Value.Year;
                return age;
            }
        }

        public string getGender()
        {
            if (Gender == "M")
            {
                return "Nam";
            }
            else if (Gender == "F")
            {
                return "Nữ";
            }
            else return "";
        }
    }
}

And this is Playlist table generating from code first: Entity framework has generated two foreign key column of one key

As you see, entity framework has generated two columns: User_UserID and User_UserID1 from primary key UserID of User table.

I say that because when I uncomment the line //public virtual User User { get; set; } and rebuild the project, the two column User_UserID and User_UserID1 has disappeared too.

The problem only happens with User and Playlist relationship. With other one-to-many scenarios (User-Comments), system work well.

Can anyone give me a suggestion?

Peter Hansen Peter Hansen · Accepted Answer · 2013-05-01T15:27:26

The problem is that you have multiple relationships between the same entities.

Playlist has 2 references to User (one in the Playlist class and one in its base class Post).

This confuses Entity Framework, because it doesn't know how to map the relationships, which is why it creates too many foreign keys in the database.

To fix this you can use the InverseProperty attribute to tell it how to map the navigation properties:

public class Playlist : AlbumBase
{
    [Key]
    public int PlaylistID { get; set; }

    [InverseProperty("Playlists")]
    public virtual User User { get; set; }
    ......

Entity framework code first generate database incorrect

1 Answers