CODEWITHSUNDEEP
c#sqllinqlinqpad
Krzysztof
Krzysztof

Reputation: 3

My SQL statement vs LinqPad SQL statement. Where is difference?

I try translate SQL query to LINQ and I don't know why the same query(the same for me) returns different results. Someone maybe knows what is wrong? What is a difference between this queries?

-- Region Parameters
DECLARE @p0 DateTime = '2017-08-10 00:00:00.000'
DECLARE @p1 DateTime = '2017-08-11 00:00:00.000'
DECLARE @p2 Int = 100
DECLARE @p3 NVarChar(1000) = 'N02'
DECLARE @p4 NVarChar(1000) = 'VVV'
-- EndRegion
SELECT COUNT(*) AS [Count], [t6].[idComm_Employee] AS [IdEmployee]
FROM (
    SELECT [t4].[idComm_Employee]
    FROM [tabComm_Item] AS [t0]
    INNER JOIN [tabMES_Order] AS [t1] ON [t0].[idComm_Item] = [t1].[idComm_Item]
    INNER JOIN [tabMES_Order_Line] AS [t2] ON ([t1].[idMES_Order] = [t2].[idMES_Order]) AND ([t1].[idMES_Order_Sequence] = [t2].[idMES_Order_Sequence])
    INNER JOIN [tabHIST_Employee_Orders] AS [t3] ON ([t2].[idMES_Order] = [t3].[idMES_Order]) AND ([t2].[idMES_Order_Sequence] = [t3].[idMES_Order_Sequence])
    INNER JOIN [tabHIST_Employee] AS [t4] ON ([t3].[idComm_Employee] = [t4].[idComm_Employee]) AND ([t3].[idHIST_WorkPlace] = [t4].[idHIST_WorkPlace])
    INNER JOIN [tabHIST_WorkPlace] AS [t5] ON [t4].[idHIST_WorkPlace] = [t5].[idHIST_WorkPlace]
    WHERE ([t5].[DateTimeFrom] > @p0) AND ([t5].[DateTimeTo] < @p1) AND ([t1].[idMES_Order_Typ] = @p2) AND ([t2].[idERP_Workcenter] <> @p3) AND ([t2].[idERP_Workcenter] <> @p4)
    GROUP BY [t4].[idComm_Employee], [t0].[MachineTyp]
    ) AS [t6]
GROUP BY [t6].[idComm_Employee]

And my

SELECT RESULT.idComm_Employee, COUNT(RESULT.MachineTyp)
FROM
    (SELECT THE.idComm_Employee, TCI.MachineTyp
    FROM
        tabComm_Item AS TCI
        INNER JOIN tabMES_Order AS TMO
        ON TCI.idComm_Item = TMO.idComm_Item
        INNER JOIN tabMES_Order_Line AS TMOL
        ON TMO.idMES_Order = TMOL.idMES_Order AND TMO.idMES_Order_Sequence = TMOL.idMES_Order_Sequence
        INNER JOIN tabHIST_Employee_Orders AS THEO
        ON TMOL.idMES_Order = THEO.idMES_Order AND TMOL.idMES_Order_Sequence = THEO.idMES_Order_Sequence AND TMOL.idMES_Order_Line = THEO.idMES_Order_Line
        INNER JOIN tabHIST_Employee AS THE
        ON THEO.idComm_Employee = THE.idComm_Employee AND THEO.idHIST_WorkPlace = THE.idHIST_WorkPlace
        INNER JOIN tabHIST_WorkPlace AS THWP
        ON THE.idHIST_WorkPlace = THWP.idHIST_WorkPlace
    WHERE
        THWP.DateTimeFrom > '2017-08-10' AND THWP.DateTimeTo < '2017-08-11' AND TMO.idMES_Order_Typ = 100 AND TMOL.idERP_Workcenter <> 'N02' AND TMOL.idERP_Workcenter <> 'VVV'
    GROUP BY THE.idComm_Employee, TCI.MachineTyp) AS RESULT
GROUP BY RESULT.idComm_Employee

My query returns 122 rows, linq 130. In linq query this expression:

AND ([t2].[idERP_Workcenter] <> @p3) AND ([t2].[idERP_Workcenter] <> @p4)

is ignored

Upvotes: 0

Views: 59

Answers (1)

Diado
Diado

Reputation: 2257

Without seeing example data and table structures it's hard to be sure, however in the second query you are joining to the tabHIST_Employee_Orders table using an additional predicate:

AND         TMOL.idMES_Order_Line   =   THEO.idMES_Order_Line

That predicate isn't present in the first query, which may well cause a difference in results between the two queries.

Upvotes: 1

Related Questions