CODEWITHSUNDEEP
c#linq
Yich Lin
Yich Lin

Reputation: 435

Linq to find lasted record in group

I want add a new column to find which is lasted record in group.
Can I write subquery in Select() method?
I have try this 

var test = DailyPeriods.Where(x => x.BookingDate == "2016/12/30")
    .Select(x =>
        new
        {
            PERIOD_GROUP_ID = x.PeriodGroupID,
            PERIOD_NAME = x.PeriodName,
            New_Column = DailyPeriods
                            .Where(z => z.BookingDate == "2016/12/30")
                            .Select(a =>
                                new
                                {
                                    PeriodGroupID = a.PeriodGroupID,
                                    period_name = a.PeriodName
                                }
                            )
                            .GroupBy(b => b.period_name)
                            .Select(g => g.Last().PeriodGroupID)
                            .Contains(x.PeriodName)
        })

But will occur this error
"column not in scope: A:2211708.C(BOOKING_DATE)"

enter image description here

Upvotes: 0

Views: 47

Answers (1)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

Try this..

var lastRecords = periodList.GroupBy(l => l.PeriodName)
                            .Select(x => new { PeriodName = x.Key,
                                               PeriodGroupId = x.OrderBy(l => l.PeriodGroupId).Last().PeriodGroupId});

var result = from period in periodList
             from lastRec in lastRecords.Where(r => r.PeriodGroupId == period.PeriodGroupId 
                                                    && r.PeriodName == period.PeriodName)
                                        .DefaultIfEmpty()
             select new { period.PeriodGroupId,period.PeriodName, New_Column=lastRec==null?false:true };

Upvotes: 1

Related Questions