Why sql generated for the LINQ query is not optimum in LINQPad?
I have a table called Trn_Tb_PlannedShifts and i just want the hour part of a datetime column
from ps in Trn_Tb_PlannedShifts
select ps.ShiftEndTime.Value.TimeOfDay.Hours
When i clicked on the SQL part in the LINQPad it generated the query in this format.
Query 1
SELECT CONVERT(Int,(CONVERT(BigInt,(CONVERT(BigInt,((CONVERT(BigInt,DATEPART(HOUR, [t0].[ShiftEndTime]))) * 36000000000) + ((CONVERT(BigInt,DATEPART(MINUTE, [t0].[ShiftEndTime]))) * 600000000) + ((CONVERT(BigInt,DATEPART(SECOND, [t0].[ShiftEndTime]))) * 10000000) + ((CONVERT(BigInt,DATEPART(MILLISECOND, [t0].[ShiftEndTime]))) * 10000))) / 36000000000)) % 24) AS [value]
FROM [trn].[Tb_PlannedShifts] AS [t0]
But the optimal query should have been
Query 2
SELECT DATEPART(HOUR, [t0].[ShiftEndTime]) AS [value]
FROM [trn].[Tb_PlannedShifts] AS [t0]
Why the Query 1 was created instead of Query 2.
My question is does LINQ generate the query in this format and is my query not optimum?
from ps in Trn_Tb_PlannedShifts
select ps.ShiftEndTime.Value.TimeOfDay.Hours
When i clicked on the SQL part in the LINQPad it generated the query in this format.
Query 1
SELECT CONVERT(Int,(CONVERT(BigInt,(CONVERT(BigInt,((CONVERT(BigInt,DATEPART(HOUR, [t0].[ShiftEndTime]))) * 36000000000) + ((CONVERT(BigInt,DATEPART(MINUTE, [t0].[ShiftEndTime]))) * 600000000) + ((CONVERT(BigInt,DATEPART(SECOND, [t0].[ShiftEndTime]))) * 10000000) + ((CONVERT(BigInt,DATEPART(MILLISECOND, [t0].[ShiftEndTime]))) * 10000))) / 36000000000)) % 24) AS [value]
FROM [trn].[Tb_PlannedShifts] AS [t0]
But the optimal query should have been
Query 2
SELECT DATEPART(HOUR, [t0].[ShiftEndTime]) AS [value]
FROM [trn].[Tb_PlannedShifts] AS [t0]
Why the Query 1 was created instead of Query 2.
My question is does LINQ generate the query in this format and is my query not optimum?
Comments