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

Hi All,

 

Have a look what LINQ for EF can do - I mainly mean that it can combine C#
subqueries into one SQL expression executed on server side. I personally was
unaware of this feature. Yes, I have read about LINQ's "lazy execution mode"
but I haven't seen such advanced examples. Of course I could have missed
them. Anyway I wanted to post about this LINQ feature here - just for the
records J This posting is split into three parts to get through dba-VB
posting size limitations. Example uses well known C.J. Date sample database
from his "An Introduction to Database Systems" book
(http://www.amazon.com/Introduction-Database-Systems-C-Date/dp/0201385902): 

 

Request:

========

   Get all pairs of part numbers such that some supplier supplies both of
the indicated parts.

 

Manually written SQL:

=================

SELECT DISTINCT

 spj1.SN as SN,

 spj1.PN AS PN1,

 spj2.PN AS PN2

FROM   

 SPJ AS spj1, 

 SPJ AS spj2,

(

select 

  p1.PN as PN1, p2.PN as PN2

  from P as p1, P as p2

where

  p1.PN <> p2.PN and

  CAST(SUBSTRING(p1.PN,2,1) as int) <

  CAST(SUBSTRING(p2.PN,2,1) as int)

) Q

WHERE  

   spj1.SN = spj2.SN and 

   spj1.PN = Q.PN1 and

   spj2.PN = Q.PN2

order by SN, PN1, PN2

 

--

Shamil