Optimise Linq-to-Sql mapping with one to many lookup

I'm having problems optimising data lookup with the following data structure:

Order
----- 
Id 
Customer 
Date 
... etc


OrderStatus
------
Id
OrderId
Date
UpdatedBy
StatusTypeId
...etc

This is causing me a headache on the Order List page, which basically shows a list of Orders. Each Order Summary in the list shows a bunch of fields from Order and the current OrderStatus, i.e. the OrderStatus with the latest Date which is linked to the Order.

Order List
-------------------------------------------------------
Order Id | Customer     | Order Date  | CurrentStatus |
-------------------------------------------------------
1        | Someone      |  1.10.2010  | Completed     |
------------------------------------开发者_JS百科-------------------
2        | Someone else | 12.10.2010  | In Progress   |
-------------------------------------------------------
3        | Whoever      | 17.10.2010  | On Hold       |
-------------------------------------------------------

Now, say I want to list all orders from this year. My Repository fetches the Order objects

var orders = _repository.GetAllOrdersSinceDate(dt);

and now I end up with something like

foreach (Order order in orders)
{
    OrderSummary summary = new OrderSummary();
    summary.Customer = order.Customer;
    summary.Date = order.Date;
    // ...etc

    // problem here!!
    summary.OrderStatus = order.OrderStatus
                .OrderByDescending(s => status.Date).First();
}

So what I end up with is a SELECT statement on Order and then a further SELECT statement on OrderStatus for each Order returned.

So to show the summary of all records for this year is requiring around 20,000 individual SQL queries and taking many minutes to load.

Is there any neat way to fix this problem?

I'm considering re-writing the database to hold the current OrderStatus in the Order table, so I end up with something like

Order
----- 
Id 
Customer 
Date 
CurrentStatusTypeId
CurrentStatusDate
CurrentStatusUpdatedBy
...etc


OrderStatusHistory
------
Id
OrderId
Date
UpdatedBy
StatusTypeId
...etc

which is the only way I can see to solve the problem but seems a pretty nasty solution.

Whats the best way forward here?

---

Please don't denormalize your database model to solve your problem. This will only make things worse. You can fix this by writing a service method that returns a list of data transfer objects (DTO) instead of the LINQ to SQL entities. For instance, the service method might look like this:

public OrderSummary[] GetOrderSummariesSinceDate(DateTime d)
{
    return (
        from order in this.context.Orders
        where order.Date >= d
        let lastStatus = (
            from status in order.OrderStatusses
            orderby status.Date descending
            select status).First()
        select new OrderSummary
        {
            OrderId = order.Id,
            CustomerName = order.Customer.Name,
            Date = order.Date,
            OrderStatus = lastStatus.StatusType.Name
        }).ToArray();    
}

Note the following:

• This code will execute as a single SQL query in the database.
• This method will return an object that contains just the data that the client needs, but nothing more. No Customer object, no OrderStatus object.
• By calling ToArray we ensure that the database is queried at this point and it is not deferred.

These three points ensure that the performance is maximized and allows the service layer to stay in control over what is executed to the database.

I hope this helps.

---

You can create a DataLoadOptions object as follows:

DataContext db = new DataContext
DataLoadOptions ds = new DataLoadOptions();
ds.LoadWith<OrderStatus>(c => c.Orders);
db.LoadOptions = ds;

Then when you run your query it should prefetch the OrderStatus table