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 >