LINQ to SQL throws SQL Exception using local collection

Question

Amounts = 
    Context.Ppm_TblCodeType
    .Where(bc => bc.OrganisationId == Contract.OrganisationId)
    .Select(bc => new MacroPaymentValue
    {
        Code = bc.CodeTypeCode,
        ShortName = bc.ShortDescription,
        ValueForPayment = visits
            .Where(v => v.Detail.CodeTypeCode == bc.CodeTypeCode)
            .Sum(v => v.Detail.Charge != null ? v.Detail.Charge.Value : 0)
     }).ToArray();

I am getting the exception: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

I am guessing it is something to do with the .Sum() call... How can I modify this to avoid the exception?

Answer 1

This is happening because you're trying to use visits which is a local sequence in your code as part of the query and LINQ to SQL can't convert this to SQL.

Looking at your code I think you'd be better off populating ValueForPayment once you have the query result in memory. Something like:

Amounts = 
    Context.Ppm_TblCodeType
    .Where(bc => bc.OrganisationId == Contract.OrganisationId)
    .Select(bc => new MacroPaymentValue
    {
        Code = bc.CodeTypeCode,
        ShortName = bc.ShortDescription,
        CodeTypeCode = bc.CodeTypeCode,
        ValueForPayment = 0
     }).ToArray();

foreach(var bc in Amounts) 
{
    bc.ValueForPayment = visits
            .Where(v => v.Detail.CodeTypeCode == bc.CodeTypeCode)
            .Sum(v => v.Detail.Charge != null ? v.Detail.Charge.Value : 0)
}