LINQ-to-SQL eagerly loads the entire graphical object
I need to load an entire LINQ-to-SQL object graph from a specific point down, loading all child collections and objects within them, etc. This will be used to dump the object structure and data into XML.
Is there a way to do this without creating a large hard-coded set of DataLoadOptions to "format" my data?
a source to share
Yes, you can do it using aka projection select
. Choosing LINQ to SQL allows you to optimize your query and retrieve only what you need. There are two main scenarios. One of them moves up the relational tree, from many to one, and the other moves from one to many. Here's a many-to-one example:
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's 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 subquery, LINQ to SQL will solve this for you. I've used anonymous types in my examples, but you can use plain old named types as well. I think you can even mix your LINQ to SQL code with 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);
a source to share
If you don't want to manually support these DataLoadOptions, you can use the T4 Toolbox to create your L2S classes and configure the DataContext generator to create a DataLoadOptions property that you can assign to the DataContext's LoadOptions property when you need it. This is what I did, and now that I want to XML Serialize an object and all of its descendants, I can.
I added this code to 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
a source to share