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

i'm having problems optimising data lookup following data structure:

order -----  id  customer  date  ... etc   orderstatus ------ id orderid date updatedby statustypeid ...etc 

this causing me headache on order list page, shows list of orders. each order summary in list shows bunch of fields order , current orderstatus, i.e. orderstatus latest date linked order.

order list ------------------------------------------------------- order id | customer     | order date  | currentstatus | ------------------------------------------------------- 1        |      |  1.10.2010  | completed     | ------------------------------------------------------- 2        | else | 12.10.2010  | in progress   | ------------------------------------------------------- 3        | whoever      | 17.10.2010  | on hold       | ------------------------------------------------------- 

now, want list orders year. repository fetches order objects

var orders = _repository.getallorderssincedate(dt);

and end 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 end select statement on order , further select statement on orderstatus each order returned.

so show summary of records year requiring around 20,000 individual sql queries , taking many minutes load.

is there neat way fix problem?

i'm considering re-writing database hold current orderstatus in order table, end like

order -----  id  customer  date  currentstatustypeid currentstatusdate currentstatusupdatedby ...etc   orderstatushistory ------ id orderid date updatedby statustypeid ...etc 

which way can see solve problem seems pretty nasty solution.

whats best way forward here?

please don't denormalize database model solve problem. make things worse. can fix writing service method returns list of data transfer objects (dto) instead of linq sql entities. instance, service method might this:

public ordersummary[] getordersummariessincedate(datetime d) {     return (         order in this.context.orders         order.date >= d         let laststatus = (             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 following:

• this code execute single sql query in database.
• this method return object contains data client needs, nothing more. no customer object, no orderstatus object.
• by calling toarray ensure database queried @ point , not deferred.

these 3 points ensure performance maximized , allows service layer stay in control on executed database.

i hope helps.