Wednesday, December 5, 2007

Active Record and Many to Many

I tried to get the following to work:

Distributor[] all = Distributor.FindAll();
foreach(Distributor distributor in all) {
Console.WriteLine(distributor.Name);
foreach(DeliveryMethod deliveryMethod in distributor.DeliveryMethods)
Console.WriteLine("--" + deliveryMethod.Description);
}

The result was multiple queries to the db and multiples of Distributor objects.
It had to be changed to this to actually get what a non-AR user would expect:

Distributor[] all = Distributor.FindAll(DetachedCriteria.For(typeof(Distributor)).SetResultTransformer(CriteriaUtil.DistinctRootEntity).SetFetchMode("DeliveryMethods", FetchMode.Eager));
foreach(Distributor distributor in all) {
Console.WriteLine(distributor.Name);
foreach(DeliveryMethod deliveryMethod in distributor.DeliveryMethods)
Console.WriteLine("--" + deliveryMethod.Description);
}



The attribute on the Distributor class' DeliveryMethods property is as follows:

[HasAndBelongsToMany(typeof(DeliveryMethod), Table="distributorDeliveryMethod", ColumnKey="distributorId", ColumnRef = "deliveryMethodId")]
public ICollection DeliveryMethods { get { return distributorDeliveryMethods; } set { distributorDeliveryMethods = value; } }


While no collection of Distributors exists on the DeliveryMethod class.

Is there an easier way to do the above find? What happens if you want 3 or more joins chained?

No comments: