CODEWITHSUNDEEP
c#asp.net.netlinqlinq-to-sql
TechGuy
TechGuy

Reputation: 4560

Get TOP5 records from each status using Linq C#

I have a VacancyApply table and that table consist of Status Id's,So i need Top5 data from each Status.I want to get top 5 records of each status.Status is int like 1,2,3

My Query

 var result = (from ui in _context.VacancyApply
              join s in _context.UserProfile on ui.UserId equals s.UserId
              join x in _context.Vacancy on ui.VacancyId equals x.VacancyId
              join st in _context.Status on ui.StatusId equals st.StatusId
              where ui.UserId == userId && ui.IsActive == true
              orderby ui.StatusId
              select new VacancyApply
              {
                  VacancyApplyId = ui.VacancyApplyId,
                  VacancyId = ui.VacancyId,
                  UserId = ui.UserId,
                  StatusId = ui.StatusId,
                  VacancyName = x.VacancyName,
                  VacancyStack = x.VacancyStack,
                  VacancyEndDate = x.VacancyEndDate,
                  StatusName = st.StatusName,
                  UserName = s.FirstName

              }).ToList();

Upvotes: 1

Views: 141

Answers (1)

Arsen Khachaturyan
Arsen Khachaturyan

Reputation: 8330

Now what I can see from the output is that it contains One VacancyId and One VendorId. I have a feeling that you have Many to Many relationships between Vacancy and Status tables.

But nevertheless, the answer is very simple: you need to use LINQ Take extension method (maybe it will be good to make it follow after the OrderBy because just taking the last items doesn't make sense without some logic):

var output = (logic to join, filter, etc.).OrderBy(lambda).Take(N); // N is the number of
                                                               // items you want to select

Now if you want Generally to take the last items from Vacancy and only after join it with Status do this:

var output = Vacancy.OrderBy(lambda).Take(N).(now join, filter, etc. with other tables);

However, if you want to Group all similar Statuses in conjunction with Vacancies and only after taking the Top items, use GroupBy:

var output = (logic to join, filter, etc.).GroupBy(st => st.StausId).
                                          .Select(group => group.OrderBy(lambda).Take(N));

Upvotes: 1

Related Questions