Thanks for the reply to my comment.

I'd better start with a brief description of the sort of applications we write here. I'm working on an accounts package, where the unit of work is the client, ie a client of the accountant we sell our software to. Optimistic locking is out of the question here for usability reasons, so a client (and associated data) is only ever modified by one user at a time.

1) I didn't express myself very clearly with regards LINQ, as I was concerned about repeated queries to the database. Since only one user may alter a client at a time, once I've loaded in a certain bit of data, there's no point re-loading it every time it's used. Am I right in thinking that were I to use LINQ for this sort of application, I'd probably want to add my own caching layer above it?

2) You're absolutely right, we can't handle the situation where you only want some of the OrderDetails. However, I don't believe there can be an automated method of knowing that in advance. Our schema (written as little XML files) generates three things: database creation scripts, a thin data access layer (which presents the object/collection interface above, and talks to the data server layer below), plus a schema description which is used by (amongst other things) the query generator. We can mark-up the schema by defining points from which joined queries can be made. We choose these where the table represents a logical root of a block of data, and where the fan-out is relatively high (ie, where there will likely be a lot of iteration within).

In the following example, each of the marked SQL statements is issued only once, and is issued only as the data is needed. If I hadn't started to iterate the OrderItems, the system wouldn't have loaded them wouldn't have loaded them. (I've trimmed the SQL slightly for readability; in particular I've changed it from parameterized SQL).

Customer customer = CustomerCollection.FromClientCode( se, "STU01" );

--> SELECT [CustomerID], [ClientCode], etc FROM [Customer] WHERE [ClientCode] = 'STU01'

foreach( Order order in customer.OrderCollection )

--> SELECT [OrderID], [CustomerID], etc FROM [Order] WHERE [CustomerID] = 1234

{
foreach( OrderItem item in order.OrderItemCollection )

--> SELECT t1.[ItemID], t1.[OrderID], t1.[ProductCode], t1.[Quantity], t1.[UnitPrice]
--> FROM ( [Order] AS t0 INNER JOIN [OrderItem] AS t1 ON t0.[OrderID] = t1.[OrderID] )
--> WHERE t0.[CustomerID] = 1234

{
total += item.GetUnitPrice().Value;
}
}

Although it could be seen as a shortcoming that when we start to iterate the OrderItems, we get all the items for that customer, in practice the application would never want just some of them -- when one is loaded, the others will all be required soon. The "intelligence" comes from the developer who writes the schema -- and the advantage here is that if we get it wrong, we just tweak the schema, based on profiling our queries, and that produces a fix across the whole application.

I appreciate of course that LINQ has to be more of a general solution (although like eg DataSets it does seem to have more of a web-app bent than a desktop application file-open-file-save one), and that our system is tailored to one pattern of data-access.

I'm looking forward to LINQ but I don't think I'd want to see it even in my object/collection layer, rather I'd hide it in a lower-level layer. I think there are two main dangers otherwise; one is that queries would be so easy to write that a developer might forget the consequence of repeated data-loads; and secondly, the optimal select is now baked into the code (which is difficult to change), rather than being driven by the schema (which is easier to change).

(And of course it wouldn't give us all the other nice features we already have like long transactions and offline working).

Stu