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     |
    -------------------------------------------------------
    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?



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     |
    -------------------------------------------------------
    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?


