none
Entity Framework trying to add navigational children of parent class twice. RRS feed

  • Question

  • I have a C# app which connects to a Web API which feeds my app some XML data for estate agency listings and agents.


    The XML looks something along these lines:

        <Snapshot>
              <Agents>
                <Agent id="838388" firstName="John" surname="Smith"/>
                <Agent id="838389" firstName="Jane" surname="Doe"/>
                <Agent id="838390" firstName="Mary" surname="Appleton"/>
                <Agent id="838391" firstName="Peter" surname="Gill"/>
              </Agents>
    
              <Listings>
                <Listing id="1737672" officeId="801948" agencyName="Century 21">
                  <Agents>
                    <AgentRef id="838388" />
                    <AgentRef id="838391" />
                  </Agents>
                </Listing>
    
                <Listing id="1737673" officeId="801949" agencyName="Remax">
                  <Agents>
                    <AgentRef id="838390" />
                    <AgentRef id="838389" />
                  </Agents>
                </Listing>
              </Listings>
            </Snapshot>

    I have decided to use using Entity Framework 6.2, code-first approach. So I created these two classes:

    public class Agent
    {
                [Key]
                [DatabaseGenerated(DatabaseGeneratedOption.None)]
                public int AgentId { get; set; }
                public string FirstName { get; set; }
                public string Surname { get; set; }
                public virtual ICollection<Listing> Listings { get; set; }
    }

    and

    public class Listing
    {
                [Key]
                [DatabaseGenerated(DatabaseGeneratedOption.None)]
                public int ListingId { get; set; }
                public int OfficeId { get; set; }
                public int AgencyName { get; set; }
                public virtual ICollection<Agent> Agents { get; set; }
    }

    As you can see, it's a many-to-many relationship between Agents and Listings. So one Agent can have zero or more listings associated to him, and one listing can have zero or more agents associated to it.
    So, I have a routine that parses the XML and returns a Snapshot class. I then iterate through the Agents and Listings of this Snapshot class, and insert each one into the dbContext:

    public class Snapshot
    {
        public ICollection<Listing> Listings;
        public ICollection<Agent> Agents;  
    }
    
    public void DoEverything()
    {
        Snapshot snapshot = ParseXml(@"C:\data.xml");
    
        foreach (Agent agent in snapshot.Agents)
            dbContext.Agents.Add(agent);
    
        foreach (Listing listing in snapshot.Listings)
            dbContext.Listings.Add(listing);
    
        dbContext.SaveChanges();
    }

    This gives a PRIMARY KEY violation error, as it's initially added the agents in the first loop, and then in the second loop, it's trying to add the same agents again, because the Listing class has a collection of Agents.

    I was tempted to change the Agents property on the Listings class from:

    public virtual ICollection<Agent> Agents { get; set; }

    to

    public virtual ICollection<int> AgentRefs { get; set; }

    Because I think that would better reflect the XML? But I don't think EF would then create the Agents-Listings bridging table?


    Any ideas how I can tackle this?

    Fabricio Rodriguez - Pretoria, South Africa


    • Edited by Fabs1977 Thursday, February 16, 2017 2:23 PM
    Thursday, February 16, 2017 2:14 PM

Answers

  • Hi Fabs1977,

    Based on your xml file, it seems that it is a one-to-many relationship. but based on your entities class, it is a many-to-many relationship.

    >>This gives a PRIMARY KEY violation error, as it's initially added the agents in the first loop, and then in the second loop, it's trying to add the same agents again, because the Listing class has a collection of Agents.

    At the second loop, I would suggest that you could retrieve related agents from agents table, if the agent is exist, you could only insert the records into bridging table.

    >>Because I think that would better reflect the XML? But I don't think EF would then create the Agents-Listings bridging table?

    If you use many-to-many relationship. you could Configure Many-to-Many relationship using Fluent API, which could create the Agents-Listings bridging, For more information, please refer to:

    http://www.entityframeworktutorial.net/code-first/configure-many-to-many-relationship-in-code-first.aspx

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Fabs1977 Monday, October 30, 2017 7:18 AM
    Friday, February 17, 2017 8:05 AM
    Moderator

All replies

  • >This gives a PRIMARY KEY violation error, as it's initially added the agents in the first loop

    Probably not.  Nothing gets added to the database until SaveChanges.  Check the database to ensure that the agents aren't already there.

    David


    Microsoft Technology Center - Dallas <p></p> <a href="http://blogs.msdn.com/dbrowne">My Blog</a>

    Thursday, February 16, 2017 2:53 PM
  • Thanks for the reply David. The database is definitely blank before I start this... in fact, the database doesn't exist - EF creates it for me (and I drop the database before each run)

    Fabricio Rodriguez - Pretoria, South Africa

    Thursday, February 16, 2017 2:54 PM
  • Hi Fabs1977,

    Based on your xml file, it seems that it is a one-to-many relationship. but based on your entities class, it is a many-to-many relationship.

    >>This gives a PRIMARY KEY violation error, as it's initially added the agents in the first loop, and then in the second loop, it's trying to add the same agents again, because the Listing class has a collection of Agents.

    At the second loop, I would suggest that you could retrieve related agents from agents table, if the agent is exist, you could only insert the records into bridging table.

    >>Because I think that would better reflect the XML? But I don't think EF would then create the Agents-Listings bridging table?

    If you use many-to-many relationship. you could Configure Many-to-Many relationship using Fluent API, which could create the Agents-Listings bridging, For more information, please refer to:

    http://www.entityframeworktutorial.net/code-first/configure-many-to-many-relationship-in-code-first.aspx

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Fabs1977 Monday, October 30, 2017 7:18 AM
    Friday, February 17, 2017 8:05 AM
    Moderator
  • Thanks for the reply Cole.

    Sorry, I know the snippet of XML code I supplied makes it look like a one-to-many relationship between Agents and Listings, but it actually is a many-to-many relationship - a listing can contain many agents, and an agent can belong to many listings.

    When you say "At the second loop, I would suggest that you could retrieve related agents from agents table, if the agent is exist, you could only insert the records into bridging table" - the thing is that by the time my code reaches the second loop (i.e. the <Listings> tag in the XML), all agents will already exist in the Agents table, because they were all inserted in the first loop. So when you say I should check first to see if the Agent exists before populating the bridge table... well... all agents will already exist...
    I don't think the problem is the population of the bridge table. I think the problem is that, in the second loop, when the Listings and Bridge table get populated, EF is trying to once again populate the Agents table (and therefore trying to create duplicate agents).
    You see, in the XML, the first <Agents> element contains a list of ALL agents (all these agents will get inserted into the Agents table). Then, the <Listing> element contains a list of all Listings, with their corresponding Agent. I think EF is treating this "corresponding agent" as a new agent. i.e. Adding it to both the bridge table, AND the Agents table again.

    Perhaps I should run the second loop first (this will populate the Listings, Agents and bridge tables). Then I should run the first loop, performing an update on existing Agents if they exist, else inserting if they don't?

    Fabricio Rodriguez - Pretoria, South Africa

    Friday, February 17, 2017 8:24 AM
  • This doesn't repro for me.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ef6test2
    {
        public class Agent
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.None)]
            public int AgentId { get; set; }
            public string FirstName { get; set; }
            public string Surname { get; set; }
            public virtual ICollection<Listing> Listings { get; set; } = new HashSet<Listing>();
        }
        public class Listing
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.None)]
            public int ListingId { get; set; }
            public int OfficeId { get; set; }
            public string AgencyName { get; set; }
            public virtual ICollection<Agent> Agents { get; set; } = new HashSet<Agent>();
        }
        public class Db : DbContext
        {
            public DbSet<Agent> Agents { get; set; }
            public DbSet<Listing> Listings { get; set; }
    
        }
        public class Snapshot
        {
            public ICollection<Listing> Listings;
            public ICollection<Agent> Agents;
        }
    
    
    
        class Program
        {
            static void Main(string[] args)
            {
                Database.SetInitializer(new DropCreateDatabaseAlways<Db>());
    
                DoEverything();
    
                using (var dbContext = new Db())
                {
                    foreach (var agent in dbContext.Agents.ToList())
                    {
                        Console.WriteLine($"Agent {agent} {agent.FirstName} {agent.Surname}");
                        foreach( var listing in agent.Listings)
                        {
                            Console.WriteLine($"  Listing {listing.ListingId}");
                        }
                    }
                }
                Console.WriteLine("Hit any key to exit");
                Console.ReadKey();
    
            }
            public static void DoEverything()
            {
                using (var dbContext = new Db())
                {
                    /*
                     * 
                        <Snapshot>
                              <Agents>
                                <Agent id="838388" firstName="John" surname="Smith"/>
                                <Agent id="838389" firstName="Jane" surname="Doe"/>
                                <Agent id="838390" firstName="Mary" surname="Appleton"/>
                                <Agent id="838391" firstName="Peter" surname="Gill"/>
                              </Agents>
    
                              <Listings>
                                <Listing id="1737672" officeId="801948" agencyName="Century 21">
                                  <Agents>
                                    <AgentRef id="838388" />
                                    <AgentRef id="838391" />
                                  </Agents>
                                </Listing>
    
                                <Listing id="1737673" officeId="801949" agencyName="Remax">
                                  <Agents>
                                    <AgentRef id="838390" />
                                    <AgentRef id="838389" />
                                  </Agents>
                                </Listing>
                              </Listings>
                            </Snapshot>
                     * 
                     * */
    
                    var Agents = new List<Agent>()
                {
                    new Agent() {AgentId=838388, FirstName="John", Surname = "Smith" },
                    new Agent() {AgentId=838389, FirstName="Jane", Surname = "Doe" },
                    new Agent() {AgentId=838390, FirstName="Mary", Surname = "Appelton" },
                    new Agent() {AgentId=838391, FirstName="Peter", Surname = "Gill" }
    
                };
                    var AgentsById = Agents.ToDictionary(a => a.AgentId);
                    var Listings = new List<Listing>()
                {
                    new Listing() { ListingId=1737672,
                                    OfficeId =801948,
                                    AgencyName ="Century 21",
                                    Agents = new List<Agent>() { AgentsById[838388], AgentsById[838391] }
                                   },
                    new Listing() { ListingId=1737673,
                                    OfficeId = 801949,
                                    AgencyName ="Remax",
                                    Agents = new List<Agent>() { AgentsById[838390], AgentsById[838389] }
                                    },
    
                };
                    Snapshot snapshot = new Snapshot() { Agents = Agents, Listings = Listings };
    
                    foreach (Agent agent in snapshot.Agents)
                        dbContext.Agents.Add(agent);
    
                    foreach (Listing listing in snapshot.Listings)
                        dbContext.Listings.Add(listing);
    
                    dbContext.SaveChanges();
                }
            }
        }
    }
    

    David


    Microsoft Technology Center - Dallas
    My blog

    Friday, February 17, 2017 3:45 PM