How to write complex SQL Query using Entity Framework?

Question

I have to write a complex SQL Query using Entity Framework , but I can not figure out how to combine them , but at the same time to have a code with an optimal execution.

Here is my SQL Query:

Select DATEPART(HOUR,Datetime),AVG(temperature1),AVG(temperature2),AVG(humidity1), 
from Table ,
where Day(Datetime)=@param, 
group by DATEPART(HOUR,Datetime),
order by DATEPART(HOUR,Datetime);

And here is what I've tried using Entity Framework :

  List<Greenhouse> greenhouse = context.Greenhouses
  .Where(x => x.Datetime.Day == DtFrom.Day && x.Datetime.Month == DtFrom.Month && x.Datetime.Year == DtFrom.Year).ToList();
  //.GroupBy(x => x.Datetime.Hour).ToList;

Can anyone help me with some ideas?Thanks.

matt-dot-net · Accepted Answer · 2015-08-09 21:00:59Z

2

here's the way to do it with LINQ using an anonymous type:

        var output = context.Greenhouses
            .Where(x => x.Datetime.Date == DtFrom.Date)
            .GroupBy(x => x.Datetime.Hour)
            .Select(y => new
            {
                hour = y.Key,
                temp1 = y.Average(t1 => t1.temperature1),
                temp2 = y.Average(t2 => t2.temperature2),
                humidity = y.Average(h => h.humidity)
            });

        foreach(var avgData in output)
        {
            Console.WriteLine("{0} {1} {2} {3}", avgData.hour, avgData.temp1, avgData.temp2, avgData.humidity);
        }

The above is projecting the grouped values onto a new anonymous type. You could project them onto a strongly typed object if you wish. The point is that when you do GroupBy you create a new collection of IGrouping. You are no longer dealing with a list of your model.

edited Aug 9, 2015 at 21:00

answered Aug 9, 2015 at 20:17

matt-dot-net

4,24423 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Maria Over a year ago

I tried this , but i get the error " Can not convert from List<Anonymous Type> to List<Model.Greenhouse>. I put ToList() at the end of the var output , but the error is still there

matt-dot-net Over a year ago

That's because it's not a Greenhouse type. We have used an anonymous type to hold the averaged values.

Collectives™ on Stack Overflow

How to write complex SQL Query using Entity Framework?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related