使用自连接进行LINQ查询

Question

我有一张表，其中包含Id和LastMeterReadingId等列，其中Id是主键，而LastMeterReadingId是一个外键，它引用了同一张表，类似于父亲表读数。

我想获取所有未被使用的行，就像父母一样。我想避免读数成为超过一个读数的父亲的情况。

我知道如何连接到同一张表，但我不知道如何仅选择那些尚未成为父亲的记录。这是没有条件语句的查询的样子。

return (from m in uow.MeterReadingReadWriteRepository.Query()
                join parent in uow.MeterReadingReadWriteRepository.Query() on m.Id equals parent.LastMeterReadingId

                select new MeterReadingDto()
                {
                    (...)
                }).ToList();

你有没有什么好的方法以高效的方式实现这个目标呢？

祝好。

- Bear

---

通常像这样的情况，我会编写一个递归方法来枚举表格。Linq对于仅涉及父子连接的情况还可以，但是对于多层级的情况，LINQ的结果并不理想。 - jdweng

首先，我认为这违反了正常形式。因为一个计量读数是否是另一个信息的子级可以通过按计量读数日期排序来获取，这不是很简单吗？而且如果将它们链接在一起，如果有人删除链中间的记录，那么你会陷入麻烦，对吧？ - Bradley Thomas

Answer 1

我希望获取所有未被使用的行，即没有子行的行。请注意，您查询中的变量名parent有误导性-当您执行a join b on a.Id equals b.ParentId时，a是父行，b是子行。

无论如何，从现代数据库查询优化器的角度来看，至少有三种方法可以实现您的目标（应该具有相同的效率）：

(1) 使用!Any(...)，它等价于SQL NOT EXISTS(...):

from m in uow.MeterReadingReadWriteRepository.Query()
where !uow.MeterReadingReadWriteRepository.Query().Any(child => m.Id == child.LastMeterReadingId)
select ...

(2) 使用组联接：

from m in uow.MeterReadingReadWriteRepository.Query()
join child in uow.MeterReadingReadWriteRepository.Query()
on m.Id equals child.LastMeterReadingId into children
where !children.Any()
select ...

(3) 使用左外反连接：

from m in uow.MeterReadingReadWriteRepository.Query()
join child in uow.MeterReadingReadWriteRepository.Query()
on m.Id equals  child.LastMeterReadingId into children
from child in children.DefaultIfEmpty()
where child == null
select ...

如果这是EF（LINQ to Entities），前两个查询将转换为相同的基于SQL的NOT EXISTS查询。而最后一个查询将转换为基于“传统”SQL的LEFT JOIN ... WHERE right.PK IS NULL查询。

- Ivan Stoev

Answer 2

你可以尝试添加

where !(from child in uow.MeterReadingReadWriteRepository.Query() where child.Id == m.LastMeterReadingId select child).Any()

不确定这样做是否会很聪明地进行优化。最好将uow.MeterReadingReadWriteRepository.Query()提取出来。

你的计量读数实体中没有一个Child关系/集合与外键约束吗？-这将使查询更加简单。

- Ben Jackson

Answer 3

var readings = uow.MeterReadingReadWriteRepository.Query();

var parents = readings
        .Join(readings, child => child.Id, parent => parent.LastMeterReadingId,
                (child, parent) => new {parent.Id})
        .Distinct()
        .ToDictionary(a => a.Id);

var result = (from m in readings
                  where !parents.Contains(m.Id) 
                  select new
                    {
                       Id = m.Id
                    }).ToList();

- Gaurav Gupta

Answer 4

感谢 @Ben Jackson

public class MeterReading : EntityBase
{
    public long PropertyId { get; set; }
    public long? LastMeterReadingId { get; set; }
    public long? PaymentId { get; set; }
    public Property Property { get; set; }
    public MeterReading LastReading { get; set; }
    public Payment Payment { get; set; }
}

这就是大多数值属性的样子。也许我应该使用带有JOIN的T-SQL查询，连接到之前提到的条件语句的CTE中？我会尽快尝试您的解决方案。

- Bear