Guerrilla Guerrilla - 1 month ago 12
C# Question

Mysterious disappearing entity association

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

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.