We are working on MVC4 application, using Linq to Entities in that. I have a sql query which I need to get using Linq.
SELECT Id,Date,TotalWood,EBUnits,TotalFeedWater,avgFeedWater,totCondesate,STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours + theMinutes,
'19000101'), 8), 1, 2, CAST((theHours + theMinutes) / 3600 AS VARCHAR(12)))as Total_IdFanHrs
FROM (
SELECT ISNULL(Max(A.Id),0)as Id,B.Date as Date,SUM(A.EB_Units) as EBUnits,SUM(A.Boiler_Feed_Water_Qty)as TotalFeedWater,SUM(A.Condensative_Recovery)as totCondesate,SUM(A.Total_Wood_Qty)as TotalWood,
AVG(cast(A.Feed_Water_Temp as decimal(10,2))) as avgFeedWater,ABS(SUM(CASE CHARINDEX(':', A.ID_Fan_Hrs) WHEN 0 THEN 0 ELSE 3600 *
LEFT(A.ID_Fan_Hrs, CHARINDEX(':', A.ID_Fan_Hrs) - 1) END)) AS theHours,
ABS(SUM(CASE CHARINDEX(':', A.ID_Fan_Hrs) WHEN 0 THEN 0 ELSE 60 *
SUBSTRING(A.ID_Fan_Hrs, CHARINDEX(':', A.ID_Fan_Hrs) + 1, 2) END)) AS theMinutes
FROM hfl_boilers_details A Inner join hfl_boiler B on A.SecBoilerId =B.Id where B.SubmitFlag='True' and B.DeleteFlag='false' group by B.Date
) AS d
so in this query I am trying get sum of values in few columns of table grouped by Date. The problem I could not put it in linq is because of the columns
"avgFeedWater" and "Total_IdFanHrs " both are varchar columns . I have to convert them to decimal and do Average calculation on one column and Total time taken calculation on other column
Please Suggest me how can we do this using Linq, all our team are new to Linq to Entities.