This outputs Unable to cast object of type 'System.Linq.Expressions.MemberExpression' to type 'SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.SetExpression'

Any suggestions appreciated

public IEnumerable<UnFillFilledInvoices> GetUnFullFilledInvoicesCollection(Guid AccountId, bool useFullFilled) { IEnumerable<UnFillFilledInvoices> returnCol = null; this.LLBLGenPattern.PerformTryCatch( delegate(IDataAccessAdapter adapter) { var metaData = new LinqMetaData(adapter); var invoices = from invoice in metaData.Invoice where invoice.AccountId == AccountId

                                   join paymentInvoice in metaData.PaymentInvoice on invoice.InvoiceId equals
                                       paymentInvoice.InvoiceId
                                       into pi

                                   join invoiceLine in metaData.InvoiceLine on invoice.InvoiceId equals
                                       invoiceLine.InvoiceId
                                       into il

                                   join creditNote in metaData.CreditNote on invoice.InvoiceId equals
                                       creditNote.InvoiceId into cn
                                   from cnCheck in cn.DefaultIfEmpty()

                                   select new UnFillFilledInvoices
                                              {
                                                  AccountId = invoice.AccountId,
                                                  Amt = il.Sum(x => (x.Amount * x.Quantity) - ((x.Amount * x.Quantity) * (x.Discount.GetValueOrDefault() / 100)))),
                                                  CreditNoteId = cnCheck == null ? null : new Guid?(cnCheck.CreditNoteId),
                                                  DateDue = invoice.DateDue,
                                                  InvoiceId = invoice.InvoiceId,
                                                  IsCredited = cnCheck != null,
                                                  PaymentId = null,
                                                  Name = invoice.Reference,
                                                  PaidAmt = pi.Sum(x => x.Amount)

                                              };
                    returnCol = invoices;
                });
        return returnCol;
    }

please provide more info: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=12769

base {System.ApplicationException} = {"The source of the aggregate function 'Sum' is not convertable to a query. Likely caused by specifying an aggregate on an IEnumerable source which is only known at runtime."}

Could it be that i am not putting .ToArray At the ennd of my linq statement?

Surely sum is supported

Don't you need to "Group" in order to perform aggregation (Sum)?

Would you please post the SQL query you need to produce?

select i.AccountId, sum((il.Amount * il.Quantity) - ((il.Amount * il.Quantity) * (il.Discount / 100))) as Amount, cn.CreditNoteId, i.DateDue, i.InvoiceId, i.Reference, sum([pi].Amount) as Paid

from Invoice i left join InvoiceLine il on i.InvoiceId = il.InvoiceId left join PaymentInvoice [pi] on i.InvoiceId = [pi].InvoiceId left join CreditNote cn on i.InvoiceId = cn.InvoiceId

group by i.AccountId,cn.CreditNoteId,i.DateDue,i.InvoiceId,i.Reference

Did you try grouping with Linq?

var invoiceLines = from uuu in ( from uu in metaData.InvoiceLine select new { Invoice = uu.InvoiceId, Amount = (uu.Amount * uu.Quantity) - ((uu.Amount * uu.Quantity) * (uu.Discount.HasValue ? uu.Discount.Value : 0 / 100)) } ) group uuu by uuu.Invoice into ug select new { Invoice = ug.Key, Amount = ug.Sum ( x => x.Amount ) };

                    var paymentInvoices = from uuu in
                                              (
                                                  from uu in metaData.PaymentInvoice
                                                  select new
                                                  {
                                                      Invoice = uu.InvoiceId,
                                                      Amount = uu.Amount
                                                  }
                                              )
                                          group uuu by uuu.Invoice into ug
                                          select new
                                          {
                                              Invoice = ug.Key,
                                              Amount = ug.Sum
                                              (
                                                  x => x.Amount
                                              )
                                          };


                    var invoices = from invoice in metaData.Invoice
                                   where invoice.AccountId == AccountId



                                   from invoiceLine in invoiceLines.Where(x=>x.Invoice == invoice.InvoiceId)
                                   from paymentInvoice in paymentInvoices.Where(x => x.Invoice == invoice.InvoiceId)


                                   join creditNote in metaData.CreditNote on invoice.InvoiceId equals creditNote.InvoiceId into cn
                                   from cnCheck in cn


                                   select new UnFillFilledInvoices
                                   {
                                       AccountId = invoice.AccountId,
                                       Amt = invoiceLine != null ? invoiceLine.Amount : 0,
                                       CreditNoteId = cnCheck == null ? Guid.Empty : cnCheck.CreditNoteId,
                                       DateDue = invoice.DateDue,
                                       InvoiceId = invoice.InvoiceId,
                                       IsCredited = cnCheck != null,
                                       PaymentId = Guid.Empty,
                                       Name = invoice.Reference,
                                       PaidAmt = paymentInvoice != null ? paymentInvoice.Amount : 0
                                   };

                    returnCol = invoices.ToArray();
                });
        return returnCol;

I managed to get this to work to a point. This works for inner joins i am looking for left joins

Sadly I dont know how to do a link

from paymentInvoice in paymentInvoices.Where(x => x.Invoice == invoice.InvoiceId)

like

join creditNote in metaData.CreditNote on invoice.InvoiceId equals creditNote.InvoiceId into cn from cnCheck in cn

due to paymentInvoices being a iqueirable<T> This is a linq question i know. Sure i will find a way.

Thanks for your patience.

How does enforce a left join and not do a inner join. This code

var invoiceLines = from uuu in ( from uu in metaData.InvoiceLine select new { Invoice = uu.InvoiceId, Amount = (uu.Amount * uu.Quantity) - ((uu.Amount * uu.Quantity) * (uu.Discount.HasValue ? uu.Discount.Value : 0 / 100)) } ) group uuu by uuu.Invoice into ug select new { Invoice = ug.Key, Amount = ug.Sum ( x => x.Amount ) };

                    var paymentInvoices = from uuu in
                                              (
                                                  from uu in metaData.PaymentInvoice
                                                  select new
                                                  {
                                                      Invoice = uu.InvoiceId,
                                                      Amount = uu.Amount
                                                  }
                                              )
                                          group uuu by uuu.Invoice into ug
                                          select new
                                          {
                                              Invoice = ug.Key,
                                              Amount = ug.Sum
                                              (
                                                  x => x.Amount
                                              )
                                          };


                    var invoices = from invoice in metaData.Invoice
                                   where invoice.AccountId == AccountId



                                   join invoiceLine1 in invoiceLines on invoice.InvoiceId equals invoiceLine1.Invoice into il
                                   from invoiceLine in il

                                   join paymentInvoice1 in paymentInvoices on invoice.InvoiceId equals paymentInvoice1.Invoice into pi
                                   from paymentInvoice in pi



                                   join creditNote in metaData.CreditNote on invoice.InvoiceId equals creditNote.InvoiceId into cn
                                   from cnCheck in cn


                                   select new UnFillFilledInvoices
                                   {
                                       AccountId = invoice.AccountId,
                                       //Amt = invoiceLine != null ? invoiceLine.Amount : 0,
                                       //CreditNoteId = cnCheck == null ? Guid.Empty : cnCheck.CreditNoteId,
                                       //DateDue = invoice.DateDue,
                                       //InvoiceId = invoice.InvoiceId,
                                       //IsCredited = cnCheck != null,
                                       //PaymentId = Guid.Empty,
                                       //Name = invoice.Reference,
                                       //PaidAmt = paymentInvoice != null ? paymentInvoice.Amount : 0
                                   };

                    returnCol = invoices.ToArray();

Produces

SELECT [LPA_L2].[AccountId] FROM ((( (SELECT [LPA_L6].[Invoice], [LPA_L6].[LPAV] AS [Amount] FROM (SELECT [LPA_L7].[Invoice], SUM([LPA_L7].[Amount]) AS [LPAV] FROM (SELECT [LPLA_2].[InvoiceId] AS [Invoice], [LPLA_2].[Amount] * [LPLA_2].[Quantity] AS [Amount], ([LPLA_2].[Amount] * [LPLA_2].[Quantity]) * (CASE WHEN CASE WHEN ( [LPLA_2].[Discount] IS NOT NULL) THEN 1 ELSE 0 END=1 THEN [LPLA_2].[Discount] ELSE @LO01 END) AS [Amount0] FROM [AxxessRad].[dbo].[InvoiceLine] [LPLA_2] ) [LPA_L7] GROUP BY [LPA_L7].[Invoice]) [LPA_L6]) [LPA_L1] INNER JOIN [AxxessRad].[dbo].[Invoice] [LPA_L2] ON [LPA_L2].[InvoiceId] = [LPA_L1].[Invoice]) INNER JOIN (SELECT [LPA_L8].[Invoice], [LPA_L8].[LPAV] AS [Amount] FROM (SELECT [LPA_L9].[Invoice], SUM([LPA_L9].[Amount]) AS [LPAV] FROM (SELECT [LPLA_12].[InvoiceId] AS [Invoice], [LPLA_12].[Amount] FROM [AxxessRad].[dbo].[PaymentInvoice] [LPLA_12] ) [LPA_L9] GROUP BY [LPA_L9].[Invoice]) [LPA_L8]) [LPA_L3] ON ( ( ( [LPA_L2].[AccountId] = @AccountId2)) AND [LPA_L2].[InvoiceId] = [LPA_L3].[Invoice])) INNER JOIN [AxxessRad].[dbo].[CreditNote] [LPA_L4] ON [LPA_L2].[InvoiceId] = [LPA_L4].[InvoiceId])

Left joins are all i need now. Linq examples say all i need to do is the into keyword but then i require a DefaultIfEmpty() which isnt catered for.

This is all alright unless i try access any values from any of the other table eg invoiceline.amount