Can LINQ To SQL determine where a table break is?

Question

I have two tables that I use to populate the gridview. The tables have a common field called RangeActivityID. My problem is that the database is very old, and some of the old records do not match the identifiers between the tables, so I cannot add a relationship between them in the database.

I don't care about old data that doesn't match, so in my .dbml file I manually created an association to select good data from both tables. This is the LINQ query that I have:

var collective = from p in rangeConnection.RangeActivities orderby p.RangeActivityID select new { TestName = p.TestName, DateTime = p.ActivityDateTime, Notes = p.Notes, //RoundSerialNumber = p.RoundFire.RoundSerialNumber, //RoundType = p.RoundFire.RoundType, //LotStockNumber = p.RoundFire.LotNumber }; 

I can set the grid data source to “collective”, and everything works, but if I uncomment the three lines with comments, the query does not return results, because the tables contain data that does not meet the association criteria. Is there a way for a LINQ query to ignore results that do not match?

Thanks in advance!

Answer 1

Try adding criteria where p.RoundFire != null .

Answer 2

Suggest join instead and emulate SQL LEFT JOIN .

  var q = from p in rangeConnection.RangeActivities join r in rangeConnection.RoundFires on p.RangeActivityID equals r.RangeActivityID into sr from x in sr.DefaultIfEmpty() select new { TestName = p.TestName, DateTime = p.ActivityDateTime, Notes = p.Notes, RoundSerialNumber = x.RoundSerialNumber, RoundType = x.RoundType, LotStockNumber = x.LotNumber //consider checking for string.IsNullOrEmpty() //for the RoundFires properties }; 

The syntax for your objects may not be accurate, but please edit my answer if it helps you find a solution.