public IHttpActionResult GetTables()
{
WeatherDataDbContext wd = new WeatherDataDbContext();
List <Date> ldates = wd.Dates.ToList();
List <Enviornment> lenviornments = wd.Enviornments.ToList();
List <Temperature> ltemperatures = wd.Temperatures.ToList();
List <Humidity> lhumidities = wd.Humidities.ToList();
List <Mold> lmolds = wd.Molds.ToList();
var query = from d in ldates
join en in lenviornments on d.Id equals en.DateId into table1
from en in table1.DefaultIfEmpty()
join tp in ltemperatures on en.Id equals tp.EnviornmentId into table2
from tp in table2.DefaultIfEmpty()
select new JoinTables {
GetDates = d, GetEnviornments = en, GetTemperatures = tp
};
return((IHttpActionResult)Ok(query));
}
public JoinTablesController(WeatherDataDbContext context)
{
_context = context;
}
public EnviornmentsController(WeatherDataDbContext context)
{
_context = context;
}
public DatesController(WeatherDataDbContext context)
{
_context = context;
}
public DataController(WeatherDataDbContext dbContext)
{
DbContext = dbContext;
}
public TemperaturesController(WeatherDataDbContext context)
{
_context = context;
}
public HumiditiesController(WeatherDataDbContext context)
{
_context = context;
}
public static void JoinTables()
{
using (var WeatherDataDbContext = new WeatherDataDbContext())
{
var datesData = WeatherDataDbContext.Dates
.Join(
WeatherDataDbContext.Enviornments,
date => date.Id,
enviornment => enviornment.Id,
(date, enviornment) => new
{
Id = date.Id,
TimeStamp = date.TimeStamp,
Enviornment = enviornment.InsideOrOutside
}
)
.Join(
WeatherDataDbContext.Temperatures,
date => date.Id,
temperature => temperature.Id,
(date, temperature) => new
{
Id = date.Id,
TimeStamp = date.TimeStamp,
Enviornment = date.Enviornment,
Temperature = temperature.Temp
}
)
.ToList();
}
//public static List<JoinTables> TablesColumnsDisplay()
//{
// List<JoinTables> jt = new List<JoinTables>();
// string connection = "Data Source = (localdb)\\MSSQLLocalDB; Initial Catalog = WeatherDataDb; Integrated Security = True";
// using (SqlConnection sqlconn = new SqlConnection(connection))
// {
// using (SqlCommand sqlcomm = new SqlCommand("select Dates.TimeStamp, Enviornments.InsideOrOutside, Temperatures.Temp, Humidities.AirHumidity, Molds.RiskForMold from Enviornments full join Date on Envoirnments.DateId=Date.DateId"))
// {
// using (SqlDataAdapter sda = new SqlDataAdapter())
// {
// sqlcomm.Connection = sqlconn;
// sqlconn.Open();
// sda.SelectCommand = sqlcomm;
// SqlDataReader sdr = sqlcomm.ExecuteReader();
// while (sdr.Read())
// {
// JoinTables jtobj = new JoinTables();
// jtobj.TimeStamp = (DateTime)sdr["TimeStamp"];
// jtobj.InsideOrOutside = sdr["InsideOrOutSide"].ToString();
// jtobj.Temp = (float)sdr["Temp"];
// jtobj.AirHumidity = (int)sdr["AirHumidity"];
// jtobj.RiskForMold = (float)sdr["RiskForMold"];
// jt.Add(jtobj);
// }
// }
// return jt;
// }
// }
//}
}
