Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Fri Jul 17 02:22:16 CDT 2009
(continued)
Request:
========
Get all pairs of part numbers such that some supplier supplies both of
the indicated parts.
Generated SQL expression (obtained via SQL profiler):
=====================================================
SELECT
[Project2].[C1] AS [C1],
[Project2].[SN] AS [SN],
[Project2].[PN] AS [PN],
[Project2].[PN1] AS [PN1]
FROM ( SELECT
[Distinct1].[PN] AS [PN],
[Distinct1].[SN] AS [SN],
[Distinct1].[PN1] AS [PN1],
1 AS [C1]
FROM (SELECT DISTINCT
[Extent1].[PN] AS [PN],
[Extent1].[SN] AS [SN],
[Extent2].[PN] AS [PN1],
1 AS [C1]
FROM [dbo].[SPJ] AS [Extent1]
INNER JOIN [dbo].[SPJ] AS [Extent2] ON [Extent1].[SN] =
[Extent2].[SN] ) AS [Distinct1]
INNER JOIN (SELECT [Extent3].[CITY] AS [CITY1], [Extent3].[COLOR] AS
[COLOR1], [Extent3].[PN] AS [PN1], [Extent3].[PNAME] AS [PNAME1],
[Extent3].[WEIGHT] AS [WEIGHT1], [Extent4].[CITY] AS [CITY2],
[Extent4].[COLOR] AS [COLOR2], [Extent4].[PN] AS [PN2], [Extent4].[PNAME] AS
[PNAME2], [Extent4].[WEIGHT] AS [WEIGHT2]
FROM [dbo].[P] AS [Extent3]
CROSS JOIN [dbo].[P] AS [Extent4] ) AS [Join2] ON
([Distinct1].[PN] = [Join2].[PN1]) AND ([Distinct1].[PN1] = [Join2].[PN2])
WHERE ([Join2].[PN1] <> [Join2].[PN2]) AND ((SUBSTRING([Join2].[PN1],
1 + 1, 1)) < (SUBSTRING([Join2].[PN2], 1 + 1, 1)))
) AS [Project2]
ORDER BY [Project2].[SN] ASC, [Project2].[PN] ASC, [Project2].[PN1] ASC
Result (LINQ for EF - C#):
==========================
SN PN1 PN2
----------
S2 P3 P5
S3 P3 P4
S5 P1 P2
S5 P1 P3
S5 P1 P4
S5 P1 P5
S5 P1 P6
S5 P2 P3
S5 P2 P4
S5 P2 P5
S5 P2 P6
S5 P3 P4
S5 P3 P5
S5 P3 P6
S5 P4 P5
S5 P4 P6
S5 P5 P6
----------
(17 rows)
Result (Manual SQL):
====================
SN PN1 PN2
S2 P3 P5
S3 P3 P4
S5 P1 P2
S5 P1 P3
S5 P1 P4
S5 P1 P5
S5 P1 P6
S5 P2 P3
S5 P2 P4
S5 P2 P5
S5 P2 P6
S5 P3 P4
S5 P3 P5
S5 P3 P6
S5 P4 P5
S5 P4 P6
S5 P5 P6
(17 rows)
---
LINQ for EF looks "smart" isn't it?
Thank you.
--
Shamil