public List <CityWeatherViewModel> GetWeather() { //SELECT w.Id ,c.name, w.currentTemp, w.date // FROM weathersystem.weatheritems w // JOIN weathersystem.cities c on(w.cityId = c.Id) // WHERE(cityId, date) // in( // SELECT cityId, MAX(date) // FROM weathersystem.weatheritems // GROUP BY cityId // ) List <CityWeatherViewModel> vm = new List <CityWeatherViewModel>(); var weatherItems = db.weatherItems .GroupBy(t => t.cityId) .Select(ig => ig.OrderByDescending(t => t.Date).First()).ToList(); foreach (CityWeather weatherItem in weatherItems) { var city = db.cities.FirstOrDefault(c => c.Id == weatherItem.cityId); CityWeatherViewModel vmItem = new CityWeatherViewModel { Id = weatherItem.Id, city = city.name, CurrentTemp = weatherItem.CurrentTemp, Date = weatherItem.Date }; vm.Add(vmItem); } return(vm); }
public CityWeatherViewModel GetWeatherByCityId(int id) { var city = db.cities.FirstOrDefault(c => c.Id == id); var weatherItem = db.weatherItems.Where(c => c.cityId == id).OrderByDescending(d => d.Date).First(); CityWeatherViewModel vmItem = new CityWeatherViewModel { Id = weatherItem.Id, city = city.name, CurrentTemp = weatherItem.CurrentTemp, Date = weatherItem.Date }; return(vmItem); }