Relational-Object-XML-Object with LINQ to SQL & LINQ to XML
This is old story and I have used parts of it over the last three years to show how LINQ lets you navigate from relational (R) to object (O) or object to XML (X) etc. But for a forthcoming demo, I had to write some quick code today and it looked like it might be worth sharing. So here it is ...
A little background first: I need to show a 3-tier app where the mid-tier can use a variety of Data Acccess Layers or DALs (not just LINQ to SQL ). A good way to show that is how you can use POCO classes with some extra info. An easy way to populate it is from an XML file. But I would like to continue the demo and show a real DAL talking to a real DB and it would be good to have consistent storyboard and data. So I decided to extract a small subset of AdventureWorks data - Employee-PurchaseOrderHeader-PurchaseOrderDetail into an XML file and then use that file for the first part. So what I have is a nice trip across the three domains: R-> O -> X (file) -> O.
Here is the R-O-X part. The object model is crafted up in the LINQ to SQL designer with a bunch of unrelated foreign keys (FK) eliminated. I won't repeat it since you can easily create it with drag-drop-member delete gestures in the designer.
Caution - this is all just a quick and dirty shell to get going. It is not robust with respect to missing files, changed data, test code etc. That is left to you as an exercise
AWExtractDataContext db = new AWExtractDataContext(@"C:\Northwind\AdventureWorks_Data.mdf");
db.Log = Console.Out;
// Preselected employees based on browsing of data
var EmpList = (from e in db.Employees
where ((e.EmployeeID > 240) && (e.PurchaseOrderHeaders.Any()))
select e).ToList();
Console.WriteLine("\nNumber of employees: {0}\n",EmpList.Count);
foreach (var e in EmpList)
{
// Select only five POs per employee
e.POList = new List<PurchaseOrderHeader>(e.PurchaseOrderHeaders.Take(5));
e.PurchaseOrderHeaders = null;
}
// Write out a single XML hierarchy with some additional denormalized data
var EmpXML = new XElement("Employees",
from e in EmpList
select new XElement("Employee",
new XAttribute("EmployeeID", e.EmployeeID),
new XAttribute("LoginID", e.LoginID),
new XAttribute("ManagerID", e.ManagerID),
from po in e.POList
select new XElement(("PurchaseOrder"),
new XAttribute("PurchaseOrderID", po.PurchaseOrderID),
new XAttribute("Status", po.Status),
new XAttribute("EmployeeID", po.EmployeeID),
new XAttribute("OrderDate", po.OrderDate),
new XAttribute("ShipDate", po.ShipDate),
new XAttribute("SubTotal", po.SubTotal),
new XAttribute("TaxAmt", po.TaxAmt),
new XAttribute("Freight", po.Freight),
new XAttribute("TotalDue", po.TotalDue),
from det in po.PurchaseOrderDetails
select new XElement(("PurchaseOrderDetail"),
new XAttribute("PurchaseOrderID", po.PurchaseOrderID),
new XAttribute("PurchaseOrderDetailID", det.PurchaseOrderDetailID),
new XAttribute("ProductID", det.ProductID),
new XAttribute("ProductName", det.ProductName),
new XAttribute("OrderQty", det.OrderQty),
new XAttribute("UnitPrice", det.UnitPrice),
new XAttribute("LineTotal", det.LineTotal)
)
)
)
);
EmpXML.Save(@"C:\temp\AWEmpPOs.xml");
Here is a small portion of the XML from the file.
<Employees>
<Employee EmployeeID="241" LoginID="adventure-works\eric2" ManagerID="274">
<PurchaseOrder PurchaseOrderID="3" Status="4" EmployeeID="0" OrderDate="2001-05-17T00:00:00" ShipDate="2001-05-26T00:00:00" SubTotal="8847.3000" TaxAmt="707.7840" Freight="221.1825" TotalDue="9776.2665">
<PurchaseOrderDetail PurchaseOrderID="3" PurchaseOrderDetailID="4" ProductID="530" ProductName="Seat Post" OrderQty="550" UnitPrice="16.0860" LineTotal="8847.3000" />
</PurchaseOrder>
...
Now in another project, I just took the XML file and created the object hierarchy. This time, I did NOT use any LINQ to SQL or any other DAL-generated classes. I won't bother showing all the POCO classes since the initializer shows you the members and types anyway. But the classes are hand-coded with the auto-implemented properties feature as follows:
public class Employee
{
public int EmployeeID { get; set; }
public string LoginID { get; set; }
public int? ManagerID { get; set; }
public List<PurchaseOrder> POs { get; set; }
}
The code to populate the object graph is as follows (hierarchy first and backpatching later)
XElement Emps = XElement.Load(@"C:\temp\AWEmpPOs.xml");
var EmpFromXML =
from e in Emps.Elements()
select new Employee
{
EmployeeID = (int) e.Attribute("EmployeeID"),
LoginID = (string) e.Attribute("LoginID"),
ManagerID = (int) e.Attribute("ManagerID"),
POs = new List<PurchaseOrder>(
from po in e.Elements()
select new PurchaseOrder {
PurchaseOrderID = (int) po.Attribute("PurchaseOrderID"),
Status = (byte) (int) po.Attribute("Status"),
EmployeeID = (int) po.Attribute("EmployeeID"),
OrderDate = (DateTime) po.Attribute("OrderDate"),
ShipDate = (DateTime?) po.Attribute("ShipDate"),
SubTotal = (decimal) po.Attribute("SubTotal"),
TaxAmt = (decimal)po.Attribute("TaxAmt"),
Freight = (decimal)po.Attribute("Freight"),
TotalDue = (decimal)po.Attribute("TotalDue"),
Details = new List<PurchaseOrderDetail> (
from det in po.Elements()
select new PurchaseOrderDetail {
PurchaseOrderID = (int)det.Attribute("PurchaseOrderID"),
PurchaseOrderDetailID = (int)det.Attribute("PurchaseOrderDetailID"),
ProductID = (int)det.Attribute("ProductID"),
ProductName = (string)det.Attribute("ProductName"),
OrderQty = (short)det.Attribute("OrderQty"),
UnitPrice = (decimal)det.Attribute("UnitPrice"),
LineTotal = (decimal)det.Attribute("LineTotal")
}
)
}
)
};
List<Employee> EmpList = EmpFromXML.ToList();
// Backpatch the references based on collection membership
foreach(Employee e in EmpList)
foreach (PurchaseOrder po in e.POs)
{
po.Employee = e;
foreach (PurchaseOrderDetail det in po.Details)
det.PurchaseOrder = po;
}
// Show some of the constructed graph
ObjectDumper.Write(EmpList.First(), 1);
I found it quite easy and quick to roll this up. You might find it useful where you need to combine data from the three domains and go back and forth.
Dinesh
P.S.
- In case you haven't used it before, ObjectDumper is the new incarnation of Console.WriteLine(). It shipped as a sample with Visual Studio 2008 in source form so you will need to build it into a DLL and add a reference to your project. It is well worth it.
- Before you say it ... When you have a hammer, everything looks like a nail. But LINQ is an awesome hammer - it often works better than screwdrivers and spanners
- I could have used a smart collection to backpatch the reference to the parent object but I just got lazy and wrote some imperative code that traverses the hierarchy again.