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

Mike Mattys mmattys at rochester.rr.com
Fri Jul 17 10:53:57 CDT 2009


Entity Framework

Extraneous Froth :)

-
Michael R Mattys
MapPoint and Database Dev
www.mattysconsulting.com
-
----- Original Message ----- 
From: "Mike Mattys" <mmattys at rochester.rr.com>
To: "Discussion concerning Visual Basic and related programming issues." 
<dba-vb at databaseadvisors.com>
Sent: Friday, July 17, 2009 10:13 AM
Subject: Re: [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
>>
>
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 




More information about the dba-VB mailing list