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

  • 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
    Wednesday, November 30, 2011 11:35 AM

Answers

  • 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.
    • Proposed as answer by Alan_chen Thursday, December 1, 2011 8:18 AM
    • Marked as answer by Alan_chen Wednesday, December 7, 2011 8:27 AM
    Thursday, December 1, 2011 6:49 AM
  • 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.

    • Proposed as answer by Alan_chen Wednesday, December 7, 2011 6:50 AM
    • Marked as answer by Richc12345 Wednesday, December 7, 2011 8:13 AM
    Thursday, December 1, 2011 8:36 AM

All replies

  • 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.
    • Proposed as answer by Alan_chen Thursday, December 1, 2011 8:18 AM
    • Marked as answer by Alan_chen Wednesday, December 7, 2011 8:27 AM
    Thursday, December 1, 2011 6:49 AM
  • 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.

    • Proposed as answer by Alan_chen Wednesday, December 7, 2011 6:50 AM
    • Marked as answer by Richc12345 Wednesday, December 7, 2011 8:13 AM
    Thursday, December 1, 2011 8:36 AM
  • 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.

    Monday, December 5, 2011 8:42 AM
  • add

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

    public int AgentID{get;set;}     

       public virtual Agent Agent { get; set; }
        }

    Thursday, January 5, 2012 7:09 PM