Hello,

I can't seem to find an answer to this very common database schema senario:

Lets say I have the following 3 tables:

Customer CustomerOrder Order

the CustomerOrder table is an intermediate table used to join Customers to Orders (only for argument's sake please) and only holds the following columns

CustomerID OrderID

CustomerID is the primary key for Customer table, OrderID is the primary key for Order table

How in the heck do I then retrieve an Order collection that exists for a certain customer?

some example code would be appreciated.

Thanks, Dustin

Hi Dustin,

Here are some ways: http://llblgen.com/TinyForum/Messages.aspx?ThreadID=11839&StartAtMessage=0&#65792

In your case I would recommend to use M:N prefetchPaths. Here is an example (assuming Adpater scenario) using Product --(1:n)--> OrderDetail --(m:1)--> Order. So there's a M:N relation between Product and Order:

// setup my fetch
ProductEntity product = new ProductEntity(1);
PrefetchPath2 path = new PrefetchPath2((int)EntityType.ProductEntity);
path.Add(ProductEntity.PrefetchPathOrdersCollectionViaOrderDetails));

// fetch the stuff
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntity(product, path);
}

// example of accessing the related m:n collection
Console.WriteLine(product.OrdersCollectionViaOrderDetails.Count);

Or you can fetch the collection using a filter based on the corresponding product:

// the corresponding product. Could be in-memory or fetched
ProductEntity product = new ProductEntity(1);

// setup my fetch
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(OrderEntity.Relations.OrderDetailEntityUsingOrderId);
filter.PredicateExpression.Add(OrderDetailFields.ProductId == product.ProductId);

// fetch the stuff
EntityCollection<OrderEntity> orders = new EntityCollection<OrderEntity>();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(orders, filter);
}

// example of accessing the fetched relation
Console.WriteLine(orders.Count);

Is very similar with SelfServicing.

You also could fetch the m:n relation directly from a fetched entity. (read this on the m:n relation title for more info).

Please come back if you need further help