Mysterious disappearing entity association

Question

I have a long running function that goes over my Company records and gets matching CompaniesHouseRecords from an API with my CompanySearch() method. This method makes a Json API call and returns a list of detatched CompaniesHouseRecords (the CompanySearch() method does not create any database context).

I then check to see if the CompaniesHouseRecord exists. If it does I instantiate it, if it doesn't I add it to the database so either way it gets an ID and is tracked by the context.

I then check to see if the Company record contains this CompaniesHouseRecord and if it doesn't I add the association.

Everything appears to work fine. Typically each search returns 20 CompaniesHouseRecord. I noticed after running a while that the records at the start had lost their CompaniesHouseRecords.

I reset the flags and run the loop again. All the CompaniesHouseRecords still exist but on this pass it is re-associating all the missing ones. After a while the ones at the start lost their associations again.

I guessed that what happens is a company with a similar name contains the same CompaniesHouseRecord in it's search results and when it was assigned to one it removed it from the other. I added a fluent api many-to-many mapping but the problem still occurs. (there is a CompanyCompaniesHouseRecord table in database)

I wrote a test function that tries to associate a CompaniesHouseRecord with multiple Companies and it works fine. I also ran a query on the database to bring back all CompaniesHouseRecords that have more than one Company record and I can see there are planty with multiple relations so it seems like there is no problem adding multiple references but for some reason each time the process runs, after a while the associations of the records at the beginning start to disappear.

It must be a problem with my logic but I can't see it. The process takes a long time to run so this has taken up days of my time trying to figure out.

Here is the method, can anyone see why the associations would be disappearing?

public static void CheckCompanyRecords()
{
    int count = 0;
    int total = 0;

    using (var db = new PlaceDBContext())
    {

        total = db.Companies
            .Where(x => x.CheckedCompaniesHouse == false)
            .Count();
    }
    while (count < total)
    {
        using (var db = new PlaceDBContext())
        {
            var toCheck = db.Companies

                .Include(x => x.CompaniesHouseRecords)
                .Where(x => x.CheckedCompaniesHouse == false)
                .OrderBy(x => x.ID)
                .Skip(count)
                .Take(10)
                .ToList();

            foreach (var company in toCheck)
            {
                Messages.Output("Searching for: " + company.Name);
                List<CompaniesHouseRecord> chrl = CompanySearch(company.Name).Result;
                Messages.Output("Found        : " + chrl.Count().ToString());

                int added = 0;
                int exist = 0;
                int assigned = 0;

                if (chrl.Count() > 0)
                {
                    foreach (var chr in chrl)
                    {
                        //initiate chr record
                        CompaniesHouseRecord foundCompany;
                        if (db.CompaniesHouseRecords.Any(x => x.CompanyNumber == chr.CompanyNumber))
                        {
                            foundCompany = db.CompaniesHouseRecords.Single(x => x.CompanyNumber == chr.CompanyNumber);
                            exist++;
                        }
                        else
                        {
                            foundCompany = chr;
                            db.CompaniesHouseRecords.Add(foundCompany);
                            added++;
                        }

                        //add and then save if its not already associated
                        if (!company.CompaniesHouseRecords.Any(x => x.ID == foundCompany.ID))
                        {
                            company.CompaniesHouseRecords.Add(foundCompany);
                            assigned++;
                        }
                    }
                }
                company.CheckedCompaniesHouse = true;
                db.SaveChanges();
                count++;
                Messages.Output("Added New    : " + added.ToString());
                Messages.Output("Already Exist: " + exist.ToString());
                Messages.Output("Assigned     : " + assigned.ToString());
                Messages.Output("");

            }

        }
    }
}

Edit:

So I looked at the IDs that are being re-assigned and they are very high like 600,000 id but they should have a low id as they were the first ones to be added.

This seems to say that at some point the existing CompaniesHouseRecord gets deleted and a new record gets created but without its existing association but I can see nothing in my code that would do that.

The only thing I can think of is that the CompanyNumber is not unique but I am enforcing this in the entity class like this:

[StringLength(100)]
[Index(IsUnique = true)]
public string CompanyNumber { get; set; }

edit:

I left process running in debugging for a few hours and noticed Microsoft.VsHub.Server.HttpHostx64.exe was taking up 2gb of ram. My debgging session either throws a timeout or outofmemory exception if i leave it running too long.

I am wondering if this causes the unexpected behaviour somehow or if it is only happening in the debugging session?

Answer 1

Since newly added foundCompany entries don't have their ID set until db.SaveChanges() is called, the following could happen:

1st time, !company.CompaniesHouseRecords.Any(x => x.ID == foundCompany.ID) should evaluate to true for each company. But all following occasions would evaluate with a 0 or null as ID on both sides of the comparison and therefore they would not be added.

Make sure to use appropriate comparison criteria for not-yet-saved entities or just save intermediate results before using their ID (seems you already tried that with success after my comment). An alternative to the many saves might be to move company.CompaniesHouseRecords.Add(foundCompany); unconditionally into the else block and nest the conditional Add into the if-then block where the existing foundCompany entry is retrieved.