System.Data.SqlClient.SqlException: Invalid column name 'GenreId'

Question

I am new in ASP.NET and I am learning MVC and EF. I could not match any existing answer to my question so please help. Code first is used.

I get an error: Error Here is the code for Action method in the Controller:

public ActionResult Index()
    {
        var book = _context.Books.Include(b => b.Genre).ToList();
        return View(book);
    }

Here is the Book.cs Model:

public class Book
{
    public int Id { get; set; }

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

    [Required]
    [StringLength(17)]
    public string ISBN { get; set; }

    [Required]
    public string Author { get; set; }

    [Required]
    public string Publisher { get; set; }

    public Genre Genre { get; set; }

    [Required]
    [Display(Name = "Genre")]
    public byte GenreId { get; set; }

    [Display(Name = "Published Year")]
    public DateTime PublishedYear { get; set; }

    [Display(Name = "Release Date")]
    public DateTime ReleaseDate { get; set; }

    [Display(Name = "Number in Stock")]
    [Range(1, 20, ErrorMessage = "The field Number in Stock must be between 1 and 20")]
    public byte NumberInStock { get; set; }

Here are DbSets:

 public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Book> Books { get; set; }
    public DbSet<MembershipType> MembershipTypes { get; set; }
    public DbSet<Genre> Genres { get; set; }

And here is the table: Columns in table

So when i debug the project here is the exception: Error = The function evaluation requires all threads to run. Debug

Thank you. I tried both suggestions but no luck. It seems to me that the Book.dbo is not connected to the context, because when I add a new Book directly in the table, the same is not listed in the Index form. and I get an error: "The specified cast from a materialized 'System.Int32' type to the 'System.Byte' type is not valid." I checked the type of the parameters. Type of the parameter "Genre Id" in Book class matches with the type of "ID" in Genre class. The Book table exist in the data base. — JasnaP, Nov 07 '18 at 09:01

Ashiquzzaman · Answer 1 · 2018-11-07T03:53:38.857

the problem in the relation between Book and Genre. Please read this article.

The navigational property (Genre) in the Book class returns the reference to the Genre object.On the other hand the navigational property in Genre class returns the books collection.

The book model created the Foreign Key Genre_Id in the book Table. You can include the foreign Key Property in the dependent class (Book).

One to many Relationship Using Data Annotations:

[ForeignKey("GenreId ")]
public Genre Genre { get; set; }
[Required]
[Display(Name = "Genre")]
public byte GenreId { get; set; }

One to many Rrelationship Using Fluent API:

modelBuilder.Entity<Book>()
            .HasRequired(e => e.Genre)
            .WithMany(d => d.books);

Add this in OnModelCreating method in your ApplicationDbContext.

and also add below line in Genre class for One-to-Many Relationships (if you not added):

public ICollection<Book> Books { get; set; }

score -1 · Answer 2 · answered Nov 05 '18 at 22:00

-1

Add the following annotaion to public Genre Genre { get; set; }

[ForeignKey("Genre_Id")]

This should help, because EF generated a wrong foreignkey name in the database. By adding the annotation you tell EF how the column is named in the database.

answered Nov 05 '18 at 22:00

Willie

352
1
3
15

System.Data.SqlClient.SqlException: Invalid column name 'GenreId'

2 Answers2