Who Wants to Tackle Huge SQL to EF?

Question

Would anyone like to help me convert this to EF?

SELECT DISTINCT Item.ITEMID AS StockItem, ISNULL(DsoItems.ITEMID, N'') AS DsoItemId, DsoInventItem.ITEMNAME, ISNULL
((SELECT SUM(PHYSICALINVENT) AS Expr1
FROM DP_AX_PROD.dbo.INVENTSUM
WHERE (ITEMID = DsoItems.ITEMID) AND (DATAAREAID = 'PROD') AND (PHYSICALINVENT > 0)), 0) AS OnHandQty, ISNULL
((SELECT TOP (1) PRODID
FROM DP_AX_PROD.dbo.PRODTABLE
WHERE (ITEMID = DsoItems.ITEMID) AND (SCHEDSTART >= GETDATE())
ORDER BY SCHEDDATE), N'') AS ProdId, ISNULL
((SELECT TOP (1) SCHEDDATE
FROM DP_AX_PROD.dbo.PRODTABLE AS PRODTABLE_2
WHERE (ITEMID = DsoItems.ITEMID) AND (SCHEDSTART >= GETDATE())
ORDER BY SCHEDDATE), N'1900-01-01') AS ProdSchedDate, ISNULL
((SELECT TOP (1) QTYSCHED
FROM DP_AX_PROD.dbo.PRODTABLE AS PRODTABLE_1
WHERE (ITEMID = DsoItems.ITEMID) AND (SCHEDSTART >= GETDATE())
ORDER BY SCHEDDATE), 0) AS ProdQty
FROM DP_AX_PROD.dbo.INVENTTABLE AS Item LEFT OUTER JOIN
(SELECT ITEMID, DATAAREAID, DSO_PREP AS DsoId
FROM DP_AX_PROD.dbo.INVENTTABLE
WHERE (DATAAREAID = 'PROD') AND (STOCKITEM = 1)
UNION ALL
SELECT ItemId, 'PROD' AS DataAreaId, CONVERT(VARCHAR(5), DsoId) AS DsoId
FROM dbo.DsoItem) AS DsoItems ON Item.DSO_PREP = DsoItems.DsoId AND Item.DATAAREAID = DsoItems.DATAAREAID LEFT OUTER JOIN
DP_AX_PROD.dbo.INVENTTABLE AS DsoInventItem ON DsoInventItem.ITEMID = DsoItems.ITEMID AND DsoInventItem.DATAAREAID = DsoItems.DATAAREAID
WHERE (Item.STOCKITEM = 1) AND (Item.DATAAREAID = 'PROD')

Any help would be greatly appreciated! :D

Answer 1

I would strongly recommend against such a rewrite. Then again, I'm a database guy, and I would never recommend EF for anything. :-)

But unless this is a baby system with only some tens of megabytes of data, you are likely to sooner or later to get performance problems, and tuning queries written in EF adds extra complication. Not the least if the database guy who is going to do the tuning work does not know EF.

(They say that ORMs remove the object/relational impedance mismatch. It does not, it just moves the mismatch to be between developers and DBAs where no one understand what the other group is doing.)