December 2007
Intermediate to advanced
896 pages
19h 57m
English
You want to be able to take a set of data from a database and represent it as XML.
Use LINQ to SQL and LINQ to XML to retrieve and transform the data all in one query. In this case, we will select the top five customers in the Northwind database whose contact is the owner and those owners who placed orders totaling more than $10,000, then create XML containing the company name, contact name, phone number, and total amount of the orders. Finally, the results are written out to the BigSpenders.xml file:
Northwind dataContext = new Northwind(Settings.Default.NorthwindConnectionString); // Log the generated SQL to the console dataContext.Log = Console.Out; var bigSpenders = new XElement("BigSpenders", from top5 in ( from customer in ( from c in dataContext.Customers // get the customers where the contact is the owner // and they placed orders where c.ContactTitle.Contains("Owner") && c.Orders.Count > 0 join orderData in ( from c in dataContext.Customers // get the customers where the contact is the owner // and they placed orders where c.ContactTitle.Contains("Owner") && c.Orders.Count > 0 from o in c.Orders // get the order details join od in dataContext.OrderDetails on o.OrderID equals od.OrderID select new { c.CompanyName, c.CustomerID, o.OrderID, // have to calc order value from orderdetails //(UnitPrice*Quantity as Total)- (Total*Discount) // as NetOrderTotal NetOrderTotal = ( (((double)od.UnitPrice) * od.Quantity) (((double)od.UnitPrice) ...Read now
Unlock full access