score:5

Accepted answer

execute query first without aggregation:

var test = 
(from record in ent.records join ship in ent.ship on record.shipid equals ship.id                       

orderby ship.station
where ship.date > model.startview && ship.date < model.endview && ship.station == model.station && record.flightnumber == model.flightnumber

select new {ship.station, record.code, record.flightnumber, ship.date, record.amounttype1};

then calculate sum

var result = 
    from row in test
    select new {row.station, row.code, row.flightnumber, row.date, row.amounttype1, 
    amounttype1sum = test.where(r => r.date == row.date).sum(r => r.amounttype1) };

this should produce the same effect as database query. code above may contain errors, because i wrote it only here.

score:1

i've answered a similar thread on: linq to sql and a running total on ordered results

on that thread it was like this:

var withruningtotals = from i in itemlist    
                   select i.date, i.amount,    
                          runningtotal = itemlist.where( x=> x.date == i.date).
                                                  groupby(x=> x.date).
                                                  select(dategroup=> dategroup.sum(x=> x.amount)).single();

in you situation, you might have to join the two tables together first while grouping, then run the same concept above on the joined table result.


Related Query

More Query from same tag