c# - 如何使用 Linq To SQL 显示所有记录？

Question

我有 2 个表要加入查询，如下所示:

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});

在表 AmenitesContact_TBLs 中只有 3 条记录。在表 Amenites_TBLs 中有 300 条记录，但查询结果仅给出 3 条记录，其他 297 行为空，但字段 AmenitiesCode 和 CountryCode 在数据库中不为空(他们得到一个值)。

如何修改我的查询以显示所有 300 条记录？

Answer 1

试试这个:

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 });