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);
}