Entity Framework: The Entity with schema was already defined

Question

In my database, I have two tables, namely Book and User, that have a M-to-M relationship table named BookXUser. In the relationship table, there are two fields, which are the foreign keys of the Book and User, and another field named bookShelf. Since using only Entity Framework I couldn't find a way to insert a M-to-M relationship while inserting into the bookShelf at the same time, I decided to make a model for the relationship table, which are defined like this:

        modelBuilder.Entity<Book>()
            .HasMany<UserData>(s => s.user)
            .WithMany(c => c.book)
            .Map(cs =>
            {
                cs.MapLeftKey("bookID");
                cs.MapRightKey("userID");
                cs.ToTable("BookXUser");
            });

        modelBuilder.Entity<BookXUser>()
            .HasRequired<UserData>(s => s.user)
            .WithMany(g => g.bookXuser)
            .HasForeignKey<int>(s => s.userID);

        modelBuilder.Entity<BookXUser>()
            .HasRequired<Book>(s => s.book)
            .WithMany(g => g.bookXuser)
            .HasForeignKey<int>(s => s.bookID);

The problems arrive when I execute the following line:

userDataRepo.FindBy(user => user.email == entry.email).FirstOrDefault()

The EntitySet 'BookUserData' with schema 'dbo' and table 'BookXUser' was already defined. Each EntitySet must refer to a unique schema and table.

How can I solve this error while keeping the relationship table as a model which references the BookXUser? The idea is that I am using lazy loading on the virtual methods found in the model of BookXUser so I can get my books and users more easily, and I also manually insert my data in it so I can populate the bookshelf field.

public class BookXUser : IEntityBase
{ 
    public int ID { get; set; }
    public int bookID { get; set; }
    public virtual Book book { get; set; }
    public int userID { get; set; }
    public virtual UserData user { get; set; }
    public string bookShelf { get; set; }
}

The Book entity

public class Book : IEntityBase
    {
        public int ID { get; set; }
        public string title { get; set; }
        public string isbn { get; set; }
        public int noPage { get; set; }
        public string edition { get; set; }
        public string bLanguage { get; set; }
        public byte[] bookPic { get; set; }
        public string publisherSite { get; set; }
        public string bookFormat { get; set; }
        public DateTime releaseDate { get; set; }
        public DateTime initialReleaseDate { get; set; }
        public string publisher { get; set; }
        public string overview { get; set; }
        public virtual ICollection<Author> author { get; set; }
        public virtual ICollection<Genre> genre { get; set; }
        public virtual ICollection<UserData> user { get; set; }
        public virtual ICollection<Rating> rating { get; set; }
        public virtual ICollection<Review> review { get; set; }
        public virtual ICollection<BookXUser> bookXuser { get; set; }

        public Book()
        {
            this.author = new HashSet<Author>();
            this.genre = new HashSet<Genre>();
            this.user = new HashSet<UserData>();
            this.rating = new HashSet<Rating>();
            this.review = new HashSet<Review>();
            this.bookXuser = new HashSet<BookXUser>();
        }
    }

The UserData enity

public class UserData : IEntityBase
    {
        public int ID { get; set; }
        public string username { get; set; }
        public string userpass { get; set; }
        public string email { get; set; }
        public byte[] userPic { get; set; }
        public string userOverview { get; set; }
        public DateTime joinedDate { get; set; }
        public virtual ICollection<Book> book { get; set; }
        public virtual ICollection<Review> review { get; set; }
        public virtual ICollection<Rating> rating { get; set; }
        public virtual ICollection<UserData> user { get; set; }
        public virtual ICollection<BookXUser> bookXuser { get; set; }

        public UserData()
        {
            this.book = new HashSet<Book>();
            this.review = new HashSet<Review>();
            this.rating = new HashSet<Rating>();
            this.user = new HashSet<UserData>();
            this.bookXuser = new HashSet<BookXUser>();
        }
    }

Answer 1

You defined your Book and UserData entities in a wrong way. As you say in your question you are trying to define a many-to-many relashionship between Book and UserData so you added a join entity BookXUser because that entity hold a data BookShelf. That join entity will use by convention BookXUser as a table name.

Also, you defined two collections:

• ICollection<UserData> user in your Book entity
• ICollection<Book> book in your UserData entity

You also use this fluent configuration in your OnModelCreating method:

modelBuilder.Entity<Book>()
    .HasMany<UserData>(s => s.user)
    .WithMany(c => c.book)
    .Map(cs =>
    {
        cs.MapLeftKey("bookID");
        cs.MapRightKey("userID");
        cs.ToTable("BookXUser"); // <-- this is your error.
    });

Doing it like this, you're adding another many-to-many relashionship between Book and UserData and use BookXUser as the name for the join table which is already used by your entity BookXUser you've created.

To solve your issue you don't need to add the collections and to configure many-to-many relationship fluently like you did. Why? Bacause you have an join entity BookXUser.

So :

• remove ICollection<UserData> user from your Book entity and replace it with ICollection<BookXUser> BookXUser
• remove ICollection<Book> book from your UserData entity and replace it with ICollection<BookXUser> BookXUser
• remove the fluent configuration you added for many-to-many relashionship between UserData and Book.