help creating a customer list for a linq query

[Rich P]

I am trying to develop a customer list to run the following linq query
-- Linq4() -- which uses an outer foreach loop to get customer
information and an inner foreach loop to get orders information. In
VS2008, there is a linq sample -- from the samples that install with
VS2008 - which reads a customer.xml file directly and creates a
hierarchichal list from that. I loaded the cusomter.xml file into a
dataset and compared that against a basic sql statement that I ran
against Northwind for the exact same resultset. My question is how I
could create a customer list (hierarchichal if required) from the
dataset which contains the data directly from Northwind? In my effort I
am trying to employ 2 classes - Customer and Orders - to create my
customer list to run the Linq4() sample. I can do this if I use just
one class - but I don't see how this extra work would be beneficial over
just using a straight forward dataset.

//--this is the sample query I am trying to use
public void Linq4() {

//--how do I create this list?
List customers = GetCustomerList(dataset1);

var waCustomers = from c in customers where c.Region == "WA"
select c;
Console.WriteLine("Customers from Washington and their orders:");
foreach (var customer in waCustomers) {
Console.WriteLine("Customer {0}: {1}", customer.CustomerID,
customer.CompanyName);
foreach (var order in customer.Orders) {
Console.WriteLine(" Order {0}: {1}", order.OrderID,
order.OrderDate);
}
}
}

//--this is my customerlist effort - what do I need to do?
public static List<Customer> GetCustomerList(DataSet ds1)
{
var CustomerList = new List<Customer>();
int i = 0;
foreach (DataRow dr in ds1.Tables[0].Rows)
{
CustomerList.Add(new Customer() { CustomerID =
dr["CustomerID"].ToString(), CompanyName = dr["CompanyName"].ToString()}
);
//--how to add the Orders information?
}
return CustomerList;
}

public class Customer
{
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public Order[] Orders;
}

public class Orders
{
public int OrderID { get; set; }
public DateTime OrderDate { get; set; }
}

//--this list is from the VS2008 linq sample
customerList = (
from e in XDocument.Load(customerListPath).
Root.Elements("customer")
select new Customer {
CustomerID = (string)e.Element("id"),
CompanyName = (string)e.Element("name"),
Address = (string)e.Element("address"),
City = (string)e.Element("city"),
Region = (string)e.Element("region"),
PostalCode = (string)e.Element("postalcode"),
Country = (string)e.Element("country"),
Phone = (string)e.Element("phone"),
Fax = (string)e.Element("fax"),
Orders = (
from o in e.Elements("orders").Elements("order")
select new Order {
OrderID = (int)o.Element("id"),
OrderDate = (DateTime)o.Element("orderdate"),
Total = (decimal)o.Element("total") } )
.ToArray() } )
.ToList();
}


Rich

 

[miher]

I am trying to develop a customer list to run the following linq query
-- Linq4() -- which uses an outer foreach loop to get customer
information and an inner foreach loop to get orders information. In
VS2008, there is a linq sample -- from the samples that install with
VS2008 - which reads a customer.xml file directly and creates a
hierarchichal list from that. I loaded the cusomter.xml file into a
dataset and compared that against a basic sql statement that I ran
against Northwind for the exact same resultset. My question is how I
could create a customer list (hierarchichal if required) from the
dataset which contains the data directly from Northwind? In my effort I
am trying to employ 2 classes - Customer and Orders - to create my
customer list to run the Linq4() sample. I can do this if I use just
one class - but I don't see how this extra work would be beneficial over
just using a straight forward dataset.

//--this is the sample query I am trying to use
public void Linq4() {

//--how do I create this list?
List customers = GetCustomerList(dataset1);

var waCustomers = from c in customers where c.Region == "WA"
select c;
Console.WriteLine("Customers from Washington and their orders:");
foreach (var customer in waCustomers) {
Console.WriteLine("Customer {0}: {1}", customer.CustomerID,
customer.CompanyName);
foreach (var order in customer.Orders) {
Console.WriteLine(" Order {0}: {1}", order.OrderID,
order.OrderDate);
}
}
}

//--this is my customerlist effort - what do I need to do?
public static List<Customer> GetCustomerList(DataSet ds1)
{
var CustomerList = new List<Customer>();
int i = 0;
foreach (DataRow dr in ds1.Tables[0].Rows)
{
CustomerList.Add(new Customer() { CustomerID =
dr["CustomerID"].ToString(), CompanyName = dr["CompanyName"].ToString()}
);
//--how to add the Orders information?
}
return CustomerList;
}

public class Customer
{
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public Order[] Orders;
}

public class Orders
{
public int OrderID { get; set; }
public DateTime OrderDate { get; set; }
}

//--this list is from the VS2008 linq sample
customerList = (
from e in XDocument.Load(customerListPath).
Root.Elements("customer")
select new Customer {
CustomerID = (string)e.Element("id"),
CompanyName = (string)e.Element("name"),
Address = (string)e.Element("address"),
City = (string)e.Element("city"),
Region = (string)e.Element("region"),
PostalCode = (string)e.Element("postalcode"),
Country = (string)e.Element("country"),
Phone = (string)e.Element("phone"),
Fax = (string)e.Element("fax"),
Orders = (
from o in e.Elements("orders").Elements("order")
select new Order {
OrderID = (int)o.Element("id"),
OrderDate = (DateTime)o.Element("orderdate"),
Total = (decimal)o.Element("total") } )
.ToArray() } )
.ToList();
}


Rich

Hi,

To query with Linq vs datasets use Linq to DataSet. (
http://msdn.microsoft.com/en-us/library/bb386977.aspx ).

(In my opinion Your solution is not the most appropriate, since as it seems
to me it loads all data into a dataset and queries from that in memory. A
better solution will be to fetch only the required records from that
database, not all, with for example LinqtoSql. Of course using a dataset
like You did also an option if You trying to achieve some kind of caching.)

Hope You find this useful.
-Zsolt

 

[Rich P]

Thanks for the reply. I didn't think this was the most efficient use of
Linq. It is mainly for exercise to understand Linq and C# programming.
I have a ways to go before I sort it all out. I vaguely understand some
of the capabilities of linq - let alone the syntax, objects, ... Then,
making efficient use will be the next level for me.

Rich