CODEWITHSUNDEEP
c#sqllinq
rahularyansharma
rahularyansharma

Reputation: 10775

linq query for selecting records which have first rank

I have a table in which i have multiple rows with same PayFrequencyId .

my sql query is which is working for me OK is as follow 

SELECT * 
FROM   (SELECT payfrequencyid, 
               customname, 
               payfromdate, 
               paytodate, 
               checkdate, 
               Rank() 
                 OVER( 
                   partition BY payfrequencyid 
                   ORDER BY payfromdate) AS 'RowRank' 
        FROM   payfrequencycalendar 
        WHERE  ispaid = 0 
               AND companyid = 3) AS T 
WHERE  rowrank = 1

but i have to use this in linq.

I have tried something like this but its not working for me . 

IList<IPayFrequencyCalendar> objPayFrequencyInfo = 
    objPayFrequencyManager.GetAll()
        .Where(x => 
            x.CompanyId == SessionWrapper.CompanyId 
            && x.IsPaid == false)
        .GroupBy(o => o.PayFrequencyId)
        .OrderBy(x => x.PayFromDate).Take(5).ToList();

Upvotes: 1

Views: 360

Answers (1)

Royi Namir
Royi Namir

Reputation: 148684

Try this : 

IList<IPayFrequencyCalendar> objPayFrequencyInfo =
     objPayFrequencyManager.GetAll()
                           .Where(x => x.CompanyId == SessionWrapper.CompanyId 
                                     && x.IsPaid == false)
                           .GroupBy(o => o.PayFrequencyId)
                           .Select(g => g.OrderBy(t => t.PayFromDate).First())

                           .ToList();

Upvotes: 1

Related Questions