Can LINQ To SQL detect where a table association breaks?

Question

I have two tables I am using to fill a gridview. The tables have a common field named RangeActivityID. My problem is that the database is very old and some of the older entries do not match up IDs between tables, so I am unable to add an association between them in the database.

I do not care about the old data which doesn't match up, so in my .dbml file, I manually created an association in order to select good data from both tables. This is the LINQ query 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 my grid datasource to 'collective' and everything works, but if I uncomment the three commented lines, the query returns no results because the tables have data that doesn't meet the association criteria. Is there a way to have the LINQ query ignore results that do not match up?

Thanks in advance!

Answer 1

Try to add where p.RoundFire != null criteria.

Answer 2

Suggest a join instead, and emulating a 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 entities may be inaccurate, but please edit my answer if it helps lead you to a solution.