c# - LinqToSQL - ToList() appears to be very slow -

i'm newish linqtosql , project working on cannot changed else. translating old sql code linq. not being hot @ linq, used linqer translation me. query took 90 seconds run, thought must linqtosql. however, when copied query linqtosql produced , ran executequery on datacontext super quick expected. i've copied full queries, rather trying distil down, looks issue linqtosql doing behind scenes.

to summarise, if copy t-sql created linq , run

var results = db.executequery<invoicebalancecheckdto.invoicebalancecheck>(@"t-sql created linq - see below").tolist() 

it completes expected results in 0.5 seconds. runs same time directly in ssms. however, if use linqtosql code creates t-sql , tolist() takes ages. result 9 records, although without constraint check balance <> 0, there around 19,000 records. it's if it's getting 19,000 , checking <> 0 after it's got records. have changed linq project class used above, rather anonymous type, makes not difference

this original sql :

select invoicenum, max(accountcode), sum(amountinc) balance      (select invoicenum, accountcode, amountinc tourbookaccount accdetailtypee in(20,30) , invoicenum >= 1000      union      select invoicenum, '<no matching invoice>' accountcode, accountinvoicedetail.amountinc      accountinvoicedetail          inner join accountinvoice on accountinvoicedetail.invoiceid=accountinvoice.invoiceid      accdetailtypee in(20,30)      , invoicenum >= 1000      ) t group invoicenum  having (sum(t.amountinc)<>0)  order invoicenum 

and linq

var test =  (from t in                         (                             //this gets tourbookaccount totals                             tba in db.tourbookaccount                                                         detailtypes.contains(tba.accdetailtypee) &&                             tba.invoicenum >= dto.checkinvoicenumfrom                             select new                              {                                 invoicenum = tba.invoicenum,                                 accountcode = tba.accountcode,                                 balance = tba.amountinc                             }                         )                         .concat //note concat, since it's possible accountinvoice record not exist                         (                             //this gets invoice detail totals.                             aid in db.accountinvoicedetail                                                         detailtypes.contains(aid.accdetailtypee) &&                             aid.accountinvoice.invoicenum >= dto.checkinvoicenumfrom &&                             select new                              {                                 invoicenum = aid.accountinvoice.invoicenum,                                 accountcode = "<no account records>",                                 balance = aid.amountinc                             }                         )                  group t t.invoicenum g                 convert.todecimal(g.sum(p => p.balance)) != 0m                 select new                  {                     invoicenum = g.key,                     accountcode = g.max(p => p.accountcode),                     balance = g.sum(p => p.balance)                 }).tolist(); 

and t-sql linq produces

  select [t5].[invoicenum], [t5].[value2] [accountcode], [t5].[value3] [balance] (     select sum([t4].[amountinc]) [value], max([t4].[accountcode]) [value2], sum([t4].[amountinc]) [value3], [t4].[invoicenum]     (         select [t3].[invoicenum], [t3].[accountcode], [t3].[amountinc]         (             select [t0].[invoicenum], [t0].[accountcode], [t0].[amountinc]             [dbo].[tourbookaccount] [t0]             ([t0].[accdetailtypee] in (20, 30)) , ([t0].[invoicenum] >= 1000)             union             select [t2].[invoicenum],'<no account records>' [value], [t1].[amountinc]             [dbo].[accountinvoicedetail] [t1]             inner join [dbo].[accountinvoice] [t2] on [t2].[invoiceid] = [t1].[invoiceid]             ([t1].[accdetailtypee] in (20, 30)) , ([t2].[invoicenum] >= 1000)             ) [t3]         ) [t4]     group [t4].[invoicenum]     ) [t5] [t5].[value] <> 0 

i bet money, problem in line:

where convert.todecimal(g.sum(p => p.balance)) != 0m 

what happening, can't translate sql , silently tries rows db memory, , filtering on in memory objects (linq objects) maybe try change like:

where g.sum(p=>.balance!=0)