Extremely long SQL statement for fairly simple query
Posted by: Karl Developer
Date: November 26, 2014 09:13AM

Hi, I have the latest connector and am using MySQL and EF6. I am trying to request an entity with some of it's children (so trying to force loading).

My LINQ query is

return await (from b in db.PermissionGateways
where b.UserId == UserID
orderby b.ID
select b)
.Include(I => I.Gateway.Statuses)
.Include(I => I.Gateway.Locations)
.ToListAsync();

However I get the error

-- Failed in 921 ms with error: Unknown column 'Join2.GatewayId' in 'where clause'

However I do not see Join2 defined anywhere... Any help please!!!


Below is the SQL generated

SELECT
`UnionAll1`.`ID` AS `C1`,
`UnionAll1`.`ID1` AS `C2`,
`UnionAll1`.`ID2` AS `C3`,
`UnionAll1`.`ID3` AS `C4`,
`UnionAll1`.`UserId` AS `C5`,
`UnionAll1`.`AccessLevel` AS `C6`,
`UnionAll1`.`GatewayId` AS `C7`,
`UnionAll1`.`UTCCreated` AS `C8`,
`UnionAll1`.`UTCModified` AS `C9`,
`UnionAll1`.`ID4` AS `C10`,
`UnionAll1`.`Discriminator` AS `C11`,
`UnionAll1`.`ID5` AS `C12`,
`UnionAll1`.`SerialNumber` AS `C13`,
`UnionAll1`.`UTCCreated1` AS `C14`,
`UnionAll1`.`UTCModified1` AS `C15`,
`UnionAll1`.`ProductName` AS `C16`,
`UnionAll1`.`HardwareVer` AS `C17`,
`UnionAll1`.`IMEI` AS `C18`,
`UnionAll1`.`OS` AS `C19`,
`UnionAll1`.`GatewayJobsLastRun_ID` AS `C20`,
`UnionAll1`.`C1` AS `C21`,
`UnionAll1`.`Discriminator1` AS `C22`,
`UnionAll1`.`ID6` AS `C23`,
`UnionAll1`.`GatewayId1` AS `C24`,
`UnionAll1`.`GatewayVer` AS `C25`,
`UnionAll1`.`GatewayClock` AS `C26`,
`UnionAll1`.`UTCCreated2` AS `C27`,
`UnionAll1`.`UTCModified2` AS `C28`,
`UnionAll1`.`SIMCCID` AS `C29`,
`UnionAll1`.`SignalDbm` AS `C30`,
`UnionAll1`.`C2` AS `C31`,
`UnionAll1`.`C3` AS `C32`,
`UnionAll1`.`C4` AS `C33`,
`UnionAll1`.`C5` AS `C34`,
`UnionAll1`.`C6` AS `C35`,
`UnionAll1`.`C7` AS `C36`,
`UnionAll1`.`C8` AS `C37`
FROM
((SELECT
CASE
WHEN (`Apply1`.`ID` IS NULL) THEN (NULL)
ELSE (1)
END AS `C1`,
`Apply1`.`ID`,
`Apply1`.`ID2` AS `ID1`,
`Apply1`.`ID` AS `ID2`,
`Apply1`.`ID` AS `ID3`,
`Apply1`.`UserId`,
`Apply1`.`AccessLevel`,
`Apply1`.`GatewayId`,
`Apply1`.`UTCCreated`,
`Apply1`.`UTCModified`,
`Apply1`.`ID` AS `ID4`,
`Apply1`.`Discriminator`,
`Apply1`.`ID1` AS `ID5`,
`Apply1`.`SerialNumber`,
`Apply1`.`UTCCreated1`,
`Apply1`.`UTCModified1`,
`Apply1`.`ProductName`,
`Apply1`.`HardwareVer`,
`Apply1`.`IMEI`,
`Apply1`.`OS`,
`Apply1`.`GatewayJobsLastRun_ID`,
`Apply1`.`Discriminator` AS `Discriminator1`,
`Apply1`.`ID` AS `ID6`,
`Apply1`.`GatewayId` AS `GatewayId1`,
`Apply1`.`GatewayVer`,
`Apply1`.`GatewayClock`,
`Apply1`.`UTCCreated` AS `UTCCreated2`,
`Apply1`.`UTCModified` AS `UTCModified2`,
`Apply1`.`SIMCCID`,
`Apply1`.`SignalDbm`,
NULL AS `C2`,
NULL AS `C3`,
NULL AS `C4`,
NULL AS `C5`,
NULL AS `C6`,
NULL AS `C7`,
NULL AS `C8`
FROM
(SELECT
`Project1`.`ID`,
`Project1`.`UserId`,
`Project1`.`AccessLevel`,
`Project1`.`GatewayId`,
`Project1`.`UTCCreated`,
`Project1`.`UTCModified`,
`Project1`.`ID1`,
`Project1`.`SerialNumber`,
`Project1`.`UTCCreated1`,
`Project1`.`UTCModified1`,
`Project1`.`ProductName`,
`Project1`.`HardwareVer`,
`Project1`.`IMEI`,
`Project1`.`OS`,
`Project1`.`Discriminator`,
`Project1`.`ID2`,
`Project1`.`GatewayJobsLastRun_ID`,
(SELECT
`Filter2`.`ID`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `ID3`,
(SELECT
`Filter2`.`GatewayId`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `GATEWAYID1`,
(SELECT
`Filter2`.`GatewayVer`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `GatewayVer`,
(SELECT
`Filter2`.`GatewayClock`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `GatewayClock`,
(SELECT
`Filter2`.`UTCCreated`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `UTCCREATED2`,
(SELECT
`Filter2`.`UTCModified`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `UTCMODIFIED2`,
(SELECT
`Filter2`.`SIMCCID`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `SIMCCID`,
(SELECT
`Filter2`.`SignalDbm`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `SignalDbm`,
(SELECT
`Filter2`.`Discriminator`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `DISCRIMINATOR1`,
(SELECT
`Filter2`.`ID1`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `ID11`,
(SELECT
`Filter2`.`SerialNumber`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `SERIALNUMBER1`,
(SELECT
`Filter2`.`UTCCREATED1`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `UTCCREATED11`,
(SELECT
`Filter2`.`UTCMODIFIED1`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `UTCMODIFIED11`,
(SELECT
`Filter2`.`ProductName`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `PRODUCTNAME1`,
(SELECT
`Filter2`.`HardwareVer`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `HARDWAREVER1`,
(SELECT
`Filter2`.`IMEI`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `IMEI1`,
(SELECT
`Filter2`.`OS`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `OS1`,
(SELECT
`Filter2`.`DISCRIMINATOR1`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `DISCRIMINATOR11`,
(SELECT
`Filter2`.`GatewayJobsLastRun_ID`
FROM
(SELECT
`Extent4`.`ID`,
`Extent4`.`GatewayId`,
`Extent4`.`GatewayVer`,
`Extent4`.`GatewayClock`,
`Extent4`.`UTCCreated`,
`Extent4`.`UTCModified`,
`Extent4`.`SIMCCID`,
`Extent4`.`SignalDbm`,
`Extent4`.`Discriminator`,
`Extent5`.`ID` AS `ID1`,
`Extent5`.`SerialNumber`,
`Extent5`.`UTCCreated` AS `UTCCREATED1`,
`Extent5`.`UTCModified` AS `UTCMODIFIED1`,
`Extent5`.`ProductName`,
`Extent5`.`HardwareVer`,
`Extent5`.`IMEI`,
`Extent5`.`OS`,
`Extent5`.`Discriminator` AS `DISCRIMINATOR1`,
`Extent5`.`GatewayJobsLastRun_ID`
FROM
`GatewaySyncStatus` AS `Extent4`
INNER JOIN `Gateway` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`GatewayId`
WHERE
((`Extent5`.`Discriminator` = @gp5)
OR (`Extent5`.`Discriminator` = @gp6))
AND (`Join2`.`GatewayId` = `Extent5`.`ID`)) AS `Filter2`
WHERE
(`Filter2`.`Discriminator` = @gp7)
OR (`Filter2`.`Discriminator` = @gp8)) AS `GATEWAYJOBSLASTRUN_ID1`
FROM
(SELECT
`Extent1`.`ID`,
`Extent1`.`UserId`,
`Extent1`.`AccessLevel`,
`Extent1`.`GatewayId`,
`Extent1`.`UTCCreated`,
`Extent1`.`UTCModified`,
`Extent2`.`ID` AS `ID1`,
`Extent2`.`SerialNumber`,
`Extent2`.`UTCCreated` AS `UTCCreated1`,
`Extent2`.`UTCModified` AS `UTCModified1`,
`Extent2`.`ProductName`,
`Extent2`.`HardwareVer`,
`Extent2`.`IMEI`,
`Extent2`.`OS`,
`Extent2`.`Discriminator`,
`Extent3`.`ID` AS `ID2`,
`Extent3`.`GatewayJobsLastRun_ID`
FROM
`PermissionGateway` AS `Extent1`
LEFT OUTER JOIN `Gateway` AS `Extent2` ON ((`Extent2`.`Discriminator` = @gp1)
OR (`Extent2`.`Discriminator` = @gp2))
AND (`Extent1`.`GatewayId` = `Extent2`.`ID`)
LEFT OUTER JOIN `Gateway` AS `Extent3` ON ((`Extent3`.`Discriminator` = @gp3)
OR (`Extent3`.`Discriminator` = @gp4))
AND (`Extent1`.`GatewayId` = `Extent3`.`ID`)
WHERE
`Extent1`.`UserId` = @p__linq__0) AS `Project1`) AS `Apply1`) UNION ALL (SELECT
2 AS `C1`,
`Project3`.`ID2` AS `ID`,
`Project3`.`ID4` AS `ID1`,
`Project3`.`ID2`,
`Project3`.`ID2` AS `ID3`,
`Project3`.`UserId`,
`Project3`.`AccessLevel`,
`Project3`.`GatewayId1` AS `GatewayId`,
`Project3`.`UTCCreated1` AS `UTCCreated`,
`Project3`.`UTCModified1` AS `UTCModified`,
`Project3`.`ID2` AS `ID4`,
`Project3`.`Discriminator1` AS `Discriminator`,
`Project3`.`ID3` AS `ID5`,
`Project3`.`SerialNumber`,
`Project3`.`UTCCreated2` AS `UTCCreated1`,
`Project3`.`UTCModified2` AS `UTCModified1`,
`Project3`.`ProductName`,
`Project3`.`HardwareVer`,
`Project3`.`IMEI`,
`Project3`.`OS`,
`Project3`.`GatewayJobsLastRun_ID`,
NULL AS `C2`,
NULL AS `C3`,
NULL AS `C4`,
NULL AS `C5`,
NULL AS `C6`,
NULL AS `C7`,
NULL AS `C8`,
NULL AS `C9`,
NULL AS `C10`,
`Project3`.`ID` AS `ID6`,
`Project3`.`GatewayId` AS `GatewayId1`,
`Project3`.`Longitude`,
`Project3`.`Latitude`,
`Project3`.`Accuracy`,
`Project3`.`UTCCreated` AS `UTCCreated2`,
`Project3`.`UTCModified` AS `UTCModified2`
FROM
(SELECT
`Join6`.`ID`,
`Join6`.`GatewayId`,
`Join6`.`Longitude`,
`Join6`.`Latitude`,
`Join6`.`Accuracy`,
`Join6`.`UTCCreated`,
`Join6`.`UTCModified`,
`Join6`.`ID1`,
`Join6`.`Discriminator`,
`Extent6`.`ID` AS `ID2`,
`Extent6`.`UserId`,
`Extent6`.`AccessLevel`,
`Extent6`.`GatewayId` AS `GatewayId1`,
`Extent6`.`UTCCreated` AS `UTCCreated1`,
`Extent6`.`UTCModified` AS `UTCModified1`,
`Extent7`.`ID` AS `ID3`,
`Extent7`.`SerialNumber`,
`Extent7`.`UTCCreated` AS `UTCCreated2`,
`Extent7`.`UTCModified` AS `UTCModified2`,
`Extent7`.`ProductName`,
`Extent7`.`HardwareVer`,
`Extent7`.`IMEI`,
`Extent7`.`OS`,
`Extent7`.`Discriminator` AS `Discriminator1`,
`Extent8`.`ID` AS `ID4`,
`Extent8`.`GatewayJobsLastRun_ID`
FROM
`PermissionGateway` AS `Extent6`
LEFT OUTER JOIN `Gateway` AS `Extent7` ON ((`Extent7`.`Discriminator` = @gp9)
OR (`Extent7`.`Discriminator` = @gp10))
AND (`Extent6`.`GatewayId` = `Extent7`.`ID`)
LEFT OUTER JOIN `Gateway` AS `Extent8` ON ((`Extent8`.`Discriminator` = @gp11)
OR (`Extent8`.`Discriminator` = @gp12))
AND (`Extent6`.`GatewayId` = `Extent8`.`ID`)
INNER JOIN (SELECT
`Extent9`.`ID`,
`Extent9`.`GatewayId`,
`Extent9`.`Longitude`,
`Extent9`.`Latitude`,
`Extent9`.`Accuracy`,
`Extent9`.`UTCCreated`,
`Extent9`.`UTCModified`,
`Extent10`.`ID` AS `ID1`,
`Extent10`.`SerialNumber`,
`Extent10`.`UTCCreated` AS `UTCCREATED1`,
`Extent10`.`UTCModified` AS `UTCMODIFIED1`,
`Extent10`.`ProductName`,
`Extent10`.`HardwareVer`,
`Extent10`.`IMEI`,
`Extent10`.`OS`,
`Extent10`.`Discriminator`,
`Extent10`.`GatewayJobsLastRun_ID`
FROM
`GatewayLocation` AS `Extent9`
INNER JOIN `Gateway` AS `Extent10` ON `Extent10`.`ID` = `Extent9`.`GatewayId`) AS `Join6` ON ((`Join6`.`Discriminator` = @gp13)
OR (`Join6`.`Discriminator` = @gp14))
AND (`Extent6`.`GatewayId` = `Join6`.`ID1`)
WHERE
`Extent6`.`UserId` = @p__linq__0) AS `Project3`)) AS `UnionAll1`
ORDER BY `UnionAll1`.`ID` ASC , `UnionAll1`.`ID1` ASC , `UnionAll1`.`ID5` ASC , `UnionAll1`.`C1` ASC

Options: ReplyQuote


Subject
Written By
Posted
Extremely long SQL statement for fairly simple query
November 26, 2014 09:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.