CODEWITHSUNDEEP
asp.net-mvclinqentity-framework
MehmetF
MehmetF

Reputation: 71

Entity Framework n-to-n query, get last records from navigation table

I am using entityframework in my project.

I have 3 tables which are navigated with many to many relationship. This is my diagram.

enter image description here

I want to select all my counters id which have last approve status == 15.

I wrote query like this;

var sayacOnayDurumlari =
                db.CounterApproveStatus
                    .Where(x => x.ApproveStatusId == 15).OrderByDescending(x=>x.Id)
                    .GroupBy(x => x.CountersId)
                    .Select(e => e.FirstOrDefault());

but it takes my older records which are ID == 15

Upvotes: 2

Views: 537

Answers (2)

MehmetF
MehmetF

Reputation: 71

 var son =
            db.Counters.Where(
                x => x.CounterApproveStatus.OrderByDescending(t => t.Id).FirstOrDefault().ApproveStatusId == 15)
                .ToList();

I tried this and I supposed I achieved it. Is it a good query?

Upvotes: 1

wonderbell
wonderbell

Reputation: 1126

You need group first, then find if the latest Id in that group has desired statusId. Following syntax may not be exactly right, but you could get idea.

var sayacOnayDurumlari =
                db.CounterApproveStatus
                .GroupBy(x => x.CountersId)
                .Select(g => new {
                    CountersId = g.Key, 
                    LatestRecord = g.OrderByDescending(x=> x.Id)
                                  .FirstOrDefault()
                   })
                .Where(g=> g.LatestRecord.ApproveStatusId == 15)                  
                .Select(g => g.CountersId).ToList();

Upvotes: 0

Related Questions