public IEnumerable <FlatCounterDataViewModel> GetTodayFlats() { List <FlatCounterDataViewModel> dataList = new List <FlatCounterDataViewModel>(); using (SqlConnection conn = new SqlConnection(connString)) { string sql = "select f.Id as flatID,f.XpathName,c.Id as CounterId, c.SerialNumber, cd.Value,cd.MeasureDateTime, " + // "s.Name as Street,hn.Name as HouseNumber,f.FlatNumber " + // "from flats f,counters c, CounterData cd, streets s, houseNumbers hn " + // "where f.CounterId = c.Id and cd.CounterId = c.Id " + // "and s.Id = hn.StreetId " + "and hn.Id=f.HouseNumberId " + "and cd.Id = (select dbo.GetLastCounterDataID(f.Id))"; SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { FlatCounterDataViewModel fcd = new FlatCounterDataViewModel(); // flat fcd.Flat.Id = Convert.ToInt32(rdr["flatID"]); fcd.Flat.XpathName = rdr["XpathName"].ToString(); fcd.Flat.Street = rdr["Street"].ToString(); fcd.Flat.HouseNumber = rdr["HouseNumber"].ToString(); fcd.Flat.FlatNumber = Convert.ToInt32(rdr["flatID"]); // Counter fcd.Counter.Id = Convert.ToInt32(rdr["CounterId"]); fcd.Counter.SerialNumber = rdr["SerialNumber"].ToString(); // Counter Data fcd.CounterData.Value = Convert.ToInt32(rdr["Value"]); fcd.CounterData.MeasureDateTime = Convert.ToDateTime(rdr["MeasureDateTime"]); fcd.Counters = new SelectList(GetCounters().ToList(), "Id", "SerialNumber", fcd.Counter.Id); // dataList.Add(fcd); } conn.Close(); } return(dataList); }