How to implement multiple joins with LINQ?

  • Thread starter Thread starter Siegfried Heintze
  • Start date Start date
S

Siegfried Heintze

The following code compiles and runs but does not produce the correct
output. I get the same product names listed over and over again.

I want to create customer objects that have a list of orders that in turn,
have a list of order-details. I'm using the famous Northwind Sample Database
on SQL Express. I am indeed getting the desired data structures with the
wrong contents.

I did some google searching for examples but could not find an example of a
double join that exploits this cool feature of grouping.

I'm trying to extrapolate from
http://msdn.microsoft.com/en-us/library/bb397905(v=VS.100).aspx.

Thanks,
Siegfried

class LINQNorthwindDemoMain

{

static System.IO.TextWriter outp = System.Console.Out;

static System.IO.TextReader inp = System.Console.In;

static void Main(string[] args)

{

LINQNorthwindDemoDataContext dc = new LINQNorthwindDemoDataContext();

var customers = from c in dc.Customers

join order in dc.Orders on c.CustomerID equals order.CustomerID into
myOrders

from subOrder in myOrders

join detail in dc.Order_Details on subOrder.OrderID equals detail.OrderID
into myDetails

select new

{

CompanyName = c.CompanyName,

orders = from o in myOrders

select new { details = from d in myDetails

select new { name=d.Product.ProductName, q=d.Quantity } }

};

foreach (var c in customers)

{

outp.Write("co={0} ", c.CompanyName);

int ii = 0;

foreach (var order in c.orders)

{

outp.Write("{0}", (ii++ == 0 ? "[" : ", "));

int jj=0;

foreach(var detail in order.details){

outp.Write("{0}{1}/{2}",(jj++==0?"[":", "), detail.name, detail.q);

}

outp.Write("]");

}

outp.Write("]");

outp.WriteLine();

}

}

}
 
Siegfried said:
The following code compiles and runs but does not produce the correct
output. I get the same product names listed over and over again.

I want to create customer objects that have a list of orders that in turn,
have a list of order-details. I'm using the famous Northwind Sample Database
on SQL Express. I am indeed getting the desired data structures with the
wrong contents.
Click to expand...

Here are lots of examples, which happened to be in VB.NET so find the C#
equivalent.

I have never seen multiple 'select new(s)' that's a good one. Why not? :)

http://msdn.microsoft.com/en-us/vbasic/bb688085.aspx
 
In my example, what value does myDetails have? I believe myDetails must be
repopulated for each order.

Hmmmm.... So how do I capture the current value for myDetails before LINQ
clobbers it with the next order object?

Well I abanond using myDetails because I don't know how to get it. So I'm
closer but I still don't have it right! Now the problem is that I'm getting
the same Customer object for each order object. Not what I want!

I hope this is possible. I think it is strange that I cannot find an example
of a nested one-to-many grouping that is nested more than two deep.
Microsoft gives a single one-to-many nesting example. Could this mean LINQ
won't do grouping (as they call it) beyond two levels of one-to-many?

Hmmmm....

Thanks,
Siegfried
 
So as I said, I'm trying to follow the example at
http://msdn.microsoft.com/en-us/library/bb397905(v=VS.100).aspx with pets
and persons but use the Northwind database. I decided to retreat and try a
simpler example. This does not work either! Why not? Why does it print the
same customerID over and over again?

Thanks,
Siegfried

foreach(var co in from c in dc.Customers

join order in dc.Orders on c.CustomerID equals order.CustomerID into
myOrders

orderby c.CustomerID

from subOrder in myOrders

select new

{

Name = c.CompanyName,

Contact = c.ContactName,

custID = c.CustomerID,

orders = from o in myOrders orderby o.OrderID select new { id = o.OrderID }

}){

outp.Write("{0}: <", co.custID);

co.orders.ForEach(o => outp.Write("{0} ", o.id));

outp.WriteLine(">");

}
 
Siegfried said:
So as I said, I'm trying to follow the example at
http://msdn.microsoft.com/en-us/library/bb397905(v=VS.100).aspx with pets
and persons but use the Northwind database. I decided to retreat and try a
simpler example. This does not work either! Why not? Why does it print the
same customerID over and over again?

Thanks,
Siegfried

foreach(var co in from c in dc.Customers

join order in dc.Orders on c.CustomerID equals order.CustomerID into
myOrders

orderby c.CustomerID

from subOrder in myOrders

select new

{

Name = c.CompanyName,

Contact = c.ContactName,

custID = c.CustomerID,

orders = from o in myOrders orderby o.OrderID select new { id = o.OrderID }

}){

outp.Write("{0}: <", co.custID);

co.orders.ForEach(o => outp.Write("{0} ", o.id));

outp.WriteLine(">");

}
Click to expand...

public class TheOrder
{
private _orderIDs = new List<int>();

public string Name {get; set;}
public string Contact {get; set;}
public int CustID {get; set;}
public List<_orderID> OrderIDs {get; set;}

}

from subOrder in myOrders

select new TheOrder
{

Name = c.CompanyName,

Contact = c.ContactName,

custID = c.CustomerID,

OrderIDs = (from o in myOrders orderby o.OrderID select
o.OrderID).ToList();
}


foreach (var id in TheOrder.OrderIDs)
{
do something with id;
}


Hey don't hold me to it, but it's something like the above.
 
For C#, the samples are there: http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx
The following code compiles and runs but does not produce the correct
output. I get the same product names listed over and over again.

I want to create customer objects that have a list of orders that in turn,
have a list of order-details. I am using the famous Northwind Sample Database
on SQL Express. I am indeed getting the desired data structures with the
wrong contents.

I did some google searching for examples but could not find an example of a
double join that exploits this cool feature of grouping.

I am trying to extrapolate from
http://msdn.microsoft.com/en-us/library/bb397905(v=VS.100).aspx.

Thanks,
Siegfried

class LINQNorthwindDemoMain

{

static System.IO.TextWriter outp = System.Console.Out;

static System.IO.TextReader inp = System.Console.In;

static void Main(string[] args)

{

LINQNorthwindDemoDataContext dc = new LINQNorthwindDemoDataContext();

var customers = from c in dc.Customers

join order in dc.Orders on c.CustomerID equals order.CustomerID into
myOrders

from subOrder in myOrders

join detail in dc.Order_Details on subOrder.OrderID equals detail.OrderID
into myDetails

select new

{

CompanyName = c.CompanyName,

orders = from o in myOrders

select new { details = from d in myDetails

select new { name=d.Product.ProductName, q=d.Quantity } }

};

foreach (var c in customers)

{

outp.Write("co={0} ", c.CompanyName);

int ii = 0;

foreach (var order in c.orders)

{

outp.Write("{0}", (ii++ == 0 ? "[" : ", "));

int jj=0;

foreach(var detail in order.details){

outp.Write("{0}{1}/{2}",(jj++==0?"[":", "), detail.name, detail.q);

}

outp.Write("]");

}

outp.Write("]");

outp.WriteLine();

}

}

}
Here are lots of examples, which happened to be in VB.NET so find the C#
equivalent.

I have never seen multiple 'select new(s)' that is a good one. Why not? :)

http://msdn.microsoft.com/en-us/vbasic/bb688085.aspx
Click to expand...
Click to expand...
 
Back
Top