Filter with Linq or Lambda using Join and Group By

Question

Filter with Linq or Lambda using Join and Group By

Navigation

#1 by (1 votes)

1

Good Afternoon

In a table where it is always inserted new records can occur to have 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 me to return the last record inserted, it will always be the last line inserted, as I am using Entity Framework I can not run the query to return 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 applications and so far I am 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

asked by anonymous 11.05.2017 / 19:48

1 answer

Date insertion in PostgreSQL use the mediator Appodeal

score 1 · Accepted Answer

Well, I managed to solve with LINQ, the first captive list of 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();