CODEWITHSUNDEEP
entity-frameworkef-code-first
Martin
Martin

Reputation: 293

Entity Framework Code First existing database mapping relationship

I have an existing database which I would like to use Entity Framework Code First against in the most simple way possible. It is only a small database.

I have created simple POCO classes which mirror the database tables:

e.g.

enter image description here

public class Author
{
    [Key]
    public int AuthorID { get; set; }
    public string AuthorName { get; set; }
    public virtual ICollection<Books> Books { get; set; }
}

public class Books
{
    [Key]
    public int BookID { get; set; }
    public string BookName { get; set; }
    public int AuthorID { get; set; }

}

And a DbContext as follows:

public class Entities : DbContext
{
    public Entities(string connString)
    : base(connString)
{
}
    public DbSet<Author> Authors { get; set; }
    public DbSet<Books> Books { get; set; }

When I run my application, and select the first Author from my database, the AuthorID and AuthorName properties are populated correctly. However, the collection of Books is not populated. Instead there is an exception of type 'System.Data.EntityCommandExecutionException', and an inner exception of: 'Invalid column name 'Author_AuthorID'.

How can I establish correctly the link between Author and Books? (i.e. one to many, one Author can have many Books). I have created the Code First very simply - no migrations or auto-generation in any way, and would like to keep it as simple as this.

Many thanks for any help, Martin

Upvotes: 0

Views: 90

Answers (3)

Slava Utesinov
Slava Utesinov

Reputation: 13488

Add Author property (without it single AuthorID property not matter at context of relation with Authors table.):

public class Books
{
    [Key]
    public int BookID { get; set; }
    public string BookName { get; set; }
    public int AuthorID { get; set; }

    //add this(attribute is not needed if you use EF6 or higher):
    [ForeignKey("AuthorID")]
    public virtual Author Author  { get; set; }
}

Upvotes: 1

Aiska Hendra
Aiska Hendra

Reputation: 145

Fluent API Approach :

modelBuilder.Entity<Author>().HasMany(a => a.Books).WithRequired().HasForeignKey(b => b.AuthorID);

update : with this fluent API you don't have to add property Author in Class Books

if you want to set Books to not required, you must set property AuthorID to int? 

    modelBuilder.Entity<Author>().HasMany(a => a.Books).HasForeignKey(b => b.AuthorID);

Books class :

public class Books
{
    [Key]
    public int BookID { get; set; }
    public string BookName { get; set; }
    public int? AuthorID { get; set; }

}

Upvotes: 1

Martin
Martin

Reputation: 293

Well, this is embarassing, I found an answer minutes after my post. I will post the answer, rather than deleting my question in case it helps anyone else:

public class Books
{
    [Key]
    public int BookID { get; set; }
    public string BookName { get; set; }

    [ForeignKey("Author")]
    public int AuthorID { get; set; }     
    public Author Author { get; set; }

}

Upvotes: 0

Related Questions