How to insert data into a table with composite key from Multiple Tables in ASP.NET MVC

Question

Answer to this question is found at here

Having three tables:

Database diagram is here

Book class:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Spatial;

public partial class Books
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Books()
    {
        UserBookComments = new HashSet<UserBookComments>();
    }

    [Key]
    public int BookID { get; set; }

    [Required]
    [StringLength(255)]
    public string Title { get; set; }

    [Required]
    [StringLength(255)]
    public string Category { get; set; }

    [Column(TypeName = "date")]
    public DateTime PublishDate { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<UserBookComments> UserBookComments { get; set; }
}

User class:

public partial class Users
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Users()
    {
        UserBookComments = new HashSet<UserBookComments>();
    }

    [Key]
    public int UserID { get; set; }

    [Required]
    [StringLength(255)]
    public string UserName { get; set; }

    [Required]
    [StringLength(255)]
    public string Password { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<UserBookComments> UserBookComments { get; set; }
}

And the UserBookComments class:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Spatial;

public partial class UserBookComments
{
    [Key]
    [Column(Order = 0)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int UserID { get; set; }

    [Key]
    [Column(Order = 1)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int BookID { get; set; }

    public int? Comments { get; set; }

    public virtual Bookss Bookss { get; set; }

    public virtual Users Users { get; set; }
} 

The table "Books" is an already saved database. Each user can comment for each book and I want a view model that holds all the data from books with their comments.

The primary key on UserBookComment would be composite, on UserID and BookID.

I used EF Code First and my DBModel context class looks so:

using System.Data.Entity;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;

public partial class DbModel : DbContext
{
    public DbModel()
        : base("name=DbModel")
    {
    }

    public virtual DbSet<Books> Books { get; set; }
    public virtual DbSet<UserBookComments> UserBookComments { get; set; }
    public virtual DbSet<Users> Users { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Books>()
            .Property(e => e.Category)
            .IsUnicode(false);

        modelBuilder.Entity<Books>()
            .HasMany(e => e.UserBookComments)
            .WithRequired(e => e.Books)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Users>()
            .HasMany(e => e.UserBookComments)
            .WithRequired(e => e.Users)
            .WillCascadeOnDelete(false);
    }
}

• I wonder how to save comments and display whole list of [title, category, publish date and comments] using a ViewModel class?

Answer 1

As you asked in the comments, I have provided the way to insert a record into the UserBookComment table by adding a method into the BookEntities class.

public partial class BooksEntities : DbContext
{
    public virtual DbSet<Books> Books { get; set; }
    public virtual DbSet<UserBookComment> UserBookComments { get; set; }
    public virtual DbSet<Users> Users { get; set; }

    public void AddComment(int userId, int bookId, string comment)
    {
        var userBookComment = new UserBookComment()
        {
            UserId = userId,
            BookId = bookId,
            Comment = comment
        };

        this.AddComment(userBookComment);
    }

    public void AddComment(UserBookComment userBookComment)
    {
        this.UserBookComment.Add(userBookComment);
        this.UserBookComment.SaveChanges();
    }
}

I assumed based on your provided information that your UserBookComment class looked like this

public class UserBookComment
{
    public int UserId { get; set; }
    public int BookId { get; set; }
    public string Comment { get; set; }
}

Answer 2

Looks like you are using entity framework. To create a composite key, just at the Key attribute on both properties. To control the key order use the column atribute. Referencing should be fixed automaticly using the names in the model, or try googling on entity framework foreign key. I'm sorry I don't have the link right now.

For the viewmodel, just don't foget to use include in the statement.