I have a MVC site that is accessing a database table, TestRecord, with millions of records. As of now the user can successfully search through all the columns in the table, including those with foreign key relationships. However, the user needs to be able to search by a column that is not in the TestRecord table or connected from the TestRecord table by a foreign key. This table, the Load table, has a foreign key that points to the TestRecord table, but the TestRecord table does not have a key that points to the LoadRecord table. Right now, this is what I am trying. This code takes the string the user selected, example "1", and creates a list based on matching entries from the LoadRecord table. Then the code iterates through this LoadRecord list and attempts to find the matching records from the TestRecord (TestRecord.pkTestRecord) table to display to the users. There should be 327 corresponding results, but it keeps showing 0 matching records. Is there a different way to go about searching for a record that has a backwards foreign/primary key relationship? In a perfect world I would just add a new foreign key column to TestRecord, but the database is not mine to change.
{
var ld = dbModel.LoadRecords.Where(c => c.fkLoad.ToString() == load).ToList();
foreach(var item in ld)
{
records = records.Where(x => x.pkTestRecord.ToString().Equals(item.fkTestRecord.ToString()));
}
}
records is a list of the results from the TestRecord page that match the corresponding user search entries