How I can show all records with Linq To SQL?

I have 2 tables to join in a query as follows:

var query = (from c in Amenites_TBLs
                 join p in AmenitesContact_TBLs on c.AmenitesCodeID
                 equals p.AmenitesCodeID
                 // group c by p.AmenitesCodeID
                 into g
                 from cc in g.DefaultIfEmpty()
                 select new
                 {
                     AmenitiesCode = Amenites_TBLs.SingleOrDefault(a => a.AmenitesCodeID == cc.AmenitesCodeID).AmenitesCode,
                     CountryCode = Amenites_TBLs.SingleOrDefault(a => a.AmenitesCodeID == cc.AmenitesCodeID).CountryCode,
                     Director = AmenitesContact_TBLs.Where(a => a.TypeOfContact.StartsWith("Dir")).FirstOrDefault(a => a.AmenitesCodeID == cc.AmenitesCodeID).ContactName});

In the Table AmenitesContact_TBLs there are just 3 records. In the table Amenites_TBLs there are 开发者_如何学JAVA300 records but the result of the query gives only 3 records and the other 297 rows are null but the fields AmenitiesCode and CountryCode are not null in the database (they get a value).

How can I modify my query to show all 300 records?

Try this:

Amenites_TBLs.Join(AmenitesContact_TBLs , c => c.AmenitesCodeID , p => p.AmenitesCodeID,(p,o) =>
new{ AmenitiesCode = c.AmenitesCode,CountryCode = c.CountryCode,Director = p.Where(a => a.TypeOfContact.StartsWith("Dir")).ContactName });