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.
Comments
Anonymous
July 27, 2008
PingBack from http://blog.a-foton.ru/2008/07/relational-object-xml-object-with-linq-to-sql-linq-to-xml/Anonymous
July 28, 2008
Interesting solution. Might I suggest giving explanations of the abbreviations used, for people new to n-tier development? <a href="http://blog.linqexchange.com">LINQ Exchange - Learn LINQ and Lambda Expressions</a>Anonymous
July 29, 2008
Sure thing LINQ Master! Just fixed the abbreviations. Thanks for the suggestion. BTW, I deliberately cut out some explanatory text. I would really like people to copy-paste (and change) the code and not spend too much time reading the non-code text. No amount of explanation can equal the understanding gained from working code adapter to your scenario.Anonymous
July 30, 2008
Awesome. I was just trying right something like this the other day for building a navigation bar from a cached xml object!Anonymous
August 04, 2008
Why not just simply use XML serializer? It's more faster and the code becomes cleaner. Successfully done XML <-> Object <-> DB roundtrip. The only problem with LINQ is to get a KeyedCollection from ITable and EntitySet<T> keyed by the primary key (assuming only one primary key per table/view exists). Unfortunatelly, if XML contains updates of data, you need to have both DB data and your objects: table.Attach(newRows[primaryKey], oldRow); db.SubmitChanges();Anonymous
August 06, 2008
Any reason why you did not use the DataContractSerializer? You could do the same within 4 lines of code ...Anonymous
August 08, 2008
The comments about using one of the serializers (DC or XML) is valid for the specific code fragments posted above. The serializers are great if you want to move all public or DataMember annotated members. If you want more control (e.g. encrypt a member) or want to check things declaratively, then the approach above provides more options. But the main idea is to show you that LINQ to XML democratizes serializers. Writing one is a lot easier now than before. Same applies to O->O mapping. You could have done that before too but now it is more declarative and compact.