[dba-VB] LINQ for EF Example (Part III)

Thanks, Shamil - These look great!
I'll have to try it out.

As soon as I look up EF?

-
Michael R Mattys
MapPoint and Database Dev
www.mattysconsulting.com
-

----- Original Message ----- 
From: "Shamil Salakhetdinov" <shamil at smsconsulting.spb.ru>
To: "'Discussion concerning Visual Basic and related programming issues.'" 
<dba-vb at databaseadvisors.com>
Sent: Friday, July 17, 2009 3:22 AM
Subject: [dba-VB] LINQ for EF Example (Part III)


> (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
>
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
>