Geek Answers Handbook

Query in LINQ with self-connect

I have a table that contains columns among others, such as Id as the primary key and LastMeterReadingId (a foreign key that refers to the same table) - something like reading the parent meter.

That's how it's looks like

I would like to get all rows that are not yet used as Parent. I would like to avoid a situation where the counter is parental for reading more than one meter.

I know how to join the same table, but I have no idea how to select only those records that are no longer parents. The way the request looks without a 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 ideas how to achieve this in an effective way?

Sincerely.

+4
4

, Parent

, , . , parent - a join b on a.Id equals b.ParentId, a , b .

, 3 : ( ):

(1) !Any(...), SQL NOT EXISTS(...):

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

(2) 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) :

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.

+6

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

, . uow.MeterReadingReadWriteRepository.Query().

/ - ? - .

0
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();
0
source

Thanks @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; }
}

What most value properties look like. Maybe I should use the T-SQL query with the JOIN to CTE, which was mentioned earlier in the condition statement? I will try your solution as soon as possible.

0
source

More articles:


All Articles