Query in LINQ with self join

Question

I have a table which contains columns among others like Id as Primary Key and LastMeterReadingId (Foreign Key which references to same table) - something like Parent Meter Reading.

I would like to get all rows which are not already used like Parent. I would like to avoid situation when meter reading is parent for more than one meter reading.

I know how to join to same table, but I have no idea how to choose only those records which aren't parent already. That's how query looks like without condition statement.

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

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

Do you have any idea how to achieve it in efficient way?

Regards.

Answer 1

I would like to get all rows which are not already used like Parent

In other words, you want all rows that have no children. Note that the variable name parent in your query is misleading - when you do a join b on a.Id equals b.ParentId, a is the parent and b is the child.

Anyway, there are at least 3 ways to achieve your goal, IMO being equivalent from nowadays database query optimizers point of view (i.e. should be equally efficient):

(1) Using !Any(...) which is equivalent to SQL NOT EXISTS(...):

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

(2) Using group join:

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) Using left outer antijoin:

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 ...

If this is EF (LINQ to Entities), the first two are translated to one and the same SQL NOT EXISTS based query. While the last is translated to the "traditional" SQL LEFT JOIN ... WHERE right.PK IS NULL based query.