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

(continued)

 

Request:

========

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

 

LINQ for EF (C#):

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

SPJEntities suppliersAndParts = new SPJEntities();

var queryResult1 =

    (

    from spj1 in suppliersAndParts.SPJ

    from spj2 in suppliersAndParts.SPJ

    where (spj1.SN == spj2.SN)

    select new { SN = spj1.SN, PN1 = spj1.PN, PN2 = spj2.PN }

    ).Distinct() ;

var queryResult2 =

    (

    from p1 in suppliersAndParts.P

    from p2 in suppliersAndParts.P

    let p1Num = p1.PN.Substring(1, 1)

    let p2Num = p2.PN.Substring(1, 1)

    where (p1.PN != p2.PN

           && string.Compare(p1Num,p2Num) < 0

           )

    select new { PN1 = p1.PN, PN2 = p2.PN }

    ).Distinct() ;

        

var queryResult =

    from q1 in queryResult1 

    from q2 in queryResult2

    where (q1.PN1 == q2.PN1 &&

           q1.PN2 == q2.PN2)

    orderby q1.SN, q1.PN1, q1.PN2 

    select new { SN = q1.SN, PN1 = q1.PN1, PN2 = q1.PN2 };

 

Console.WriteLine("SN PN1 PN2");

Console.WriteLine("----------");

 

foreach (var row in queryResult)

    Console.WriteLine("{0}  {1}  {2}", row.SN, row.PN1, row.PN2);

Console.WriteLine("----------");

 

--

Shamil