ef 4 with code first parent child relationship "Invalid column" error

Question

Using ef 4 with code first I have a parent child object Agent and contacts but the parent child relationship does not seem to be working

  public partial class Agent
    {

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

        public string Name { get; set; }
        public virtual List<AgentContact> Contacts { get; set; }
    }

    public class AgentContact
    {
        [Key]
        public int AgentContactID { get; set; }

        public virtual Agent Agent { get; set; }
    }

the Tables in the DB are

[Agents](
 [AgentID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](100) NOT NULL,
 )

[AgentContacts](
 [AgentContactID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](50) NOT NULL
 [Agent_AgentID] [int] NOT NULL,
 )
when I try to load the collection of Contacts of an Agent I get
InnerException = {"Invalid column name 'Agent_AgentID1'.\r\nInvalid column name 'Agent_AgentID1'.\r\nInvalid column name 'Agent_AgentID1'."}

If I then add "Agent_AgentID1" to the contacts table and populate it with the values from "Agent_AgentID" everything is works. This is obviusly not right
Ive tried explicity addting the  [ForeignKey("Agent")] or[ForeignKey("Agent_AgentID")] attributes to the contacts object with no luck :(
Please what am I missing? this seems so simple

---

rc

Answer 1

Hi,

If you don't want to have an AgentId column in your AgentContact you need to use fluent API to connect the tables.

You can read a bit about it here:

http://msdn.microsoft.com/en-us/library/hh295843(v=vs.103).aspx

There are also several other ways to do this, so I recommend you to search the net for help on it.

The simplest solution however is to add a AgentId column to your AgentContact table, this should automatically be used as the foreign key column.

---

--Rune

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.

Answer 2

Hi rc,

Welcome!

Try to add Fluent API as following:

  protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<AgentContact>().HasRequired(v => v.Agent).WithMany(e => e.Contacts).Map(e => e.MapKey("Agent_AgentID"));
        }

Have a nice day. 

---

Alan Chen[MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

Hi,

I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions?

If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

Have a nice day.

---

Alan Chen[MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

add

public class AgentContact
    {
        [Key]
        public int AgentContactID { get; set; }

public int AgentID{get;set;}     

   public virtual Agent Agent { get; set; }
    }