>>Hi everybody,
>>
>>I have Invoices table and Transact table that has an invoice_no column (it is currently not marked as FK to invoices, I sent email asking why).
>>
>>So, as a result the Invoices class doesn't have navigation to transact.
>>
>>I have the following query:
>>
>>

>>select i.invoice_no, i.descrip1, i.date_time,
>>   (select sum(isnull(t.extension,0.00)) from dbo.transact t where t.invoice_no=i.invoice_no) as extension,
>>   i.finalized from dbo.invoices i

>>
>>and this is what I have in C# right now:
>>
>>

>> var invoicesList = _dbSet.Where(x => x.Account == accountName && x.Hidden == false).
>>                Select(x => new InvoicesList { InvoiceNo = x.InvoiceNo, Account = x.Account, Hidden = x.Hidden,
>>                Descrip = x.Descrip1, Finalized = x.Finalized, Created = x.DateTime});
>>
>>            if (showFinalized)
>>            {
>>                invoicesList = invoicesList.Where(x => x.Finalized == true);
>>            }

>>
>>this code is in the Invoices repository class. I'm wondering how can I add the Invoice Balance column into this query.
>>
>>Once again, right now the transact is not defined as navigation property in the invoices class.
>>
>>Thanks in advance.
>
>Off the top of my head, if I understood your problem correctly. It would be something like this:
>

>var list = (from  d in _dbSet.Where(x => x.Account == accountName && x.Hidden == false)
>               from t in transact .Sum(s=>s.extension).Where(w=>w.extension != null && w.invoiceNo == d.InvoiceNo).
>              select(x => new InvoicesList { InvoiceNo = x.InvoiceNo, Account = x.Account, Hidden = x.Hidden, 
>                                                          Descrip = x.Descrip1, Finalized = x.Finalized, Created = .DateTime}, InvoiceBalance= t)).ToList();
>

I am trying to use this syntax, but getting errors and can not figure this out. Here is my attempt, but I don't see where to put Sum here:

 var invoicesList =  (from  d in _dbSet.Where(x => x.Account == accountName && x.Hidden == false)
                 from i in _siriusContext.Transact.Where(w=>w.Extension != null && w.InvoiceNo == d.InvoiceNo)
                select(x => new InvoicesList { InvoiceNo = x.InvoiceNo, Account = x.Account, Hidden = x.Hidden, Descrip = x.Descrip1, Finalized = x.Finalized, Created = x.DateTime, Balance= i.Extension}))