LINQ-to-SQL eagerly load entire object graph

Question

I have a need to load an entire LINQ-to-SQL object graph from a certain point downwards, loading all child collections and the objects within them etc. This is going to be used to dump out the object structure and data to XML.

Is there a way to do this without generating a large hard coded set of DataLoadOptions to 'shape' my data?

Answer 1

No, I don't believe there is.

Answer 2

Yes, you can do this by using a projection a.k.a. select. LINQ to SQL select will enable to optimize the query and retrieves only what is needed. There are two basic scenarios. One traveling up the relational tree, from many to one, and the other travelling down, from one to many. Here is an example of many to one:

var unshippedOrders =
    from order in db.Orders
    where order.ShipDate == null
    select
    {
        OrderId = order.Id,
        CustomerId = order.Customer.Id,
        CustomerName = order.Customer.Name
    };

And here is an example from one to many:

var unshippedOrdersPerCustomer =
    from customer in db.Customers
    select
    {
        CustomerId = customer.Id,
        CustomerName = customer.Name
        UnshippedOrders =
            from order in customer.Orders
            where order.ShipDate == null
            select
            {
                OrderId = order.Id,
                OrderPrice = order.Price
            }
    };

As you can see, in the second query I have another sub query, LINQ to SQL will resolve this for you. In my examples I used anonymous types, but you can also use plain old named types. I think you can even mix your LINQ to SQL code with your LINQ to XML by creating XElement nodes right in your LINQ to SQL query :-). The sky is the limit.

---

What the heck, let me give an example if LINQ to SQL+XML.

XElement xml = new XElement("customers", 
    from customer in db.Customers
    select new XElement("customer",
        from order in customer.Orders
        where order.ShipDate == null
        select new XElement("order",
            new XAttribute("id", order.Id),
            new XAttribute("price", order.Price)
        )
    ));

Console.WriteLine(xml);

Answer 3

If you don't want to manually maintain those DataLoadOptions, you could use the T4 Toolbox to generate your L2S classes and customize the DataContext generator to build a DataLoadOptions property that you can assign to the DataContext LoadOptions property when you need it. That's what I did, and now, when I want to XML Serialize an object and all of it's descendants, I can.

I added this code to the LinqToSqlDataContextTemplate.tt

    /// <summary>
    /// Sets up a property that will allow loading of all child properties.
    /// This is done to make serializing and entire object graph easier.
    /// </summary>
    private void SetupChildLoading() {

#>
        private DataLoadOptions loadAllChildrenOptions;

        public DataLoadOptions LoadAllChildrenOptions
        {
            get
            {
                if (loadAllChildrenOptions == null) {
                    loadAllChildrenOptions = new DataLoadOptions();
<#+
    this.PushIndent("                    ");
    foreach (Table t in this.Database.Table) {
        for (int i = 0; i < t.Type.Items.Length; i++)
        {
            Association association = t.Type.Items[i] as Association;
            if (association != null)
            {
                if (association.AccessModifier == GeneratedTextTransformation.AccessModifier.Public && !association.IsForeignKey) {
                    this.WriteLine("loadAllChildrenOptions.LoadWith<{0}>(Q => Q.{1});",t.Type.Name.ToString(),association.Member);
                }
            }
        }
    }
    this.PopIndent();
#>
                }
                return loadAllChildrenOptions;
            }
        }
<#+
    }

And in the TransformText method:

        #region LoadOptions
<#+ SetupChildLoading(); #>
        #endregion