Share via

Relational-Object-XML-Object with LINQ to SQL & LINQ to XML

  • Article

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 smile_sad ). 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 smile_wink

 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.

  1. 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.
  2. 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 smile_regular
  3. 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.