Filter with Linq or Lambda using Join and Group By

Asked

Viewed 670 times

1

Good Afternoon

In a table where it is always inserted new records may occur of having the same Date and the same Ratecode, however I want to get a list of this table but for each Date group, Ratecode would like to return me the last inserted record, ensuring that it will always be the last line inserted, as I am using Entity Framework I can not run the query to return me the result, I believe it is better to use lambda or LINQ to filter the data, however, I do not have much experience in complex uses and so far I am not able to implement logic, in SQL would filter this way in the simplest way.

select a.* 
  from DailyRates a
  join (select Max(Id) as Id, Date, RateCode
          from DailyRates
         where RoomTypeId = 79
         group by Date, RateCode) b on a.id = b.id
c# entity-framework linq lambda-expressions

  • I didn’t quite understand what you want to get. It would be the last record inserted, this?

  • Yes, a list, for each pair Date, Rate code the last record inserted, for example, I have 2 lines, Id = 1, Date = '11/05/2017', Ratecode= 1, Id = 2, Date = '11/05/2017', Ratecode= 1 in the list will have duplicate dates because only new records are entered and never deleted, however I need to ensure that I will always take the last record so I have to group and capture the line with the largest record, however can come N lines.

1 answer

1


Well I managed to solve with LINQ, first list captured from the bank with the filters and duplicated lines, in the second filter only the most recent lines with the most current records inserted.

List<DailyRate> dailyRates = oDailyDao.All().Where(x => x.RoomTypeId == dailyRate.RoomTypeId && x.RoomType.Company.SeriesNumber == sSeriesNumber).ToList();

List<DailyRate> dailyRatesFiltro = (from a in dailyRates
                                    join b in (from x in dailyRates
                                              group x by new { x.Date, x.RateCode } into z
                                              select new { Id = z.Max(a => a.Id) }) on  a.Id equals b.Id
                                    select a).ToList();

Browser other questions tagged c# entity-framework linq lambda-expressions

You are not signed in. Login or sign up in order to post.