Entity Framework Code First existing database mapping relationship

Question

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.

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

Aiska Hendra · Accepted Answer · 2016-06-27 12:09:55Z

1

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; }

}

edited Jun 27, 2016 at 12:09

answered Jun 27, 2016 at 12:01

Aiska Hendra

1451 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Slava Utesinov · Accepted Answer · 2016-06-28 13:51:48Z

1

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; }
}

edited Jun 28, 2016 at 13:51

answered Jun 27, 2016 at 11:44

Slava Utesinov

13.5k2 gold badges21 silver badges26 bronze badges

5 Comments

Martin Over a year ago

Thanks Slava. I found this still gives an error, but adding the annotation [ForeignKey("Author")] in addition fixes this (see below).

Aiska Hendra Over a year ago

i think you should add annotation [ForeignKey("Author")] in AuthorID property. to prevent redundant

Slava Utesinov Over a year ago

It is strange, at my case all working correct and attribute not needed due to names conventions: AuthorID = Author + ID. May be it depends on version of EF, in case of EF6 it is works, what is your version?

Martin Over a year ago

I am using version 4

Slava Utesinov Over a year ago

That is anwer - for EF6 you can skip this attribute.

Martin · Accepted Answer · 2016-06-27 11:46:32Z

0

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; }

}

answered Jun 27, 2016 at 11:46

Martin

2931 gold badge4 silver badges15 bronze badges

Collectives™ on Stack Overflow

Entity Framework Code First existing database mapping relationship

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related