How to solve issue "Could not translate expression ...into SQL and could not treat it as a local expression."

Question

I have code below:

void Main()
{
    var q = from a in Applicants
             where(a.Claims.Any())
             select a.Claims.Sum(c => c.TotalClaimAmount());


    q.Dump();
}

public static class MyExt
{
    public static decimal TotalClaimAmount(this Claim c)
    {
        var t = c.Accommodations.Sum(a => a.AmountClaimed) +
                c.MealAllowances.Sum(ma => ma.AmountClaimed) +
                c.Meals.Sum(m => m.AmountClaimed) +
                c.Mileages.Sum(mi => mi.AmountClaimed) +
                c.Others.Sum(o => o.AmountClaimed) +
                c.ParkingTransits.Sum(pt => pt.AmountClaimed) +
                c.Travels.Sum(tr => tr.AmountClaimed);

        return (decimal)t;
    }
}

when I run it in LinqPad get below issue:

InvalidOperationException: Could not translate expression 'a.Claims.Sum(c => c.TotalClaimAmount())' into SQL and could not treat it as a local expression.

Please help me out. Many thanks

Lukas Kabrt · Answer 1 · 2014-02-07T07:45:19.570

5

LINQ is unable to convert the TotalClaimAmount method call to a SQL expression.

What you can do, is to extract the expression from the TotalClaimAmount method and use it directly in the Sum method call.

public static class MyExt {
    public static Func<Claim, decimal> TotalClaimAmountFunc = c =>
        c.Accommodations.Sum(a => a.AmountClaimed) +
        c.MealAllowances.Sum(ma => ma.AmountClaimed) +
        c.Meals.Sum(m => m.AmountClaimed) +
        c.Mileages.Sum(mi => mi.AmountClaimed) +
        c.Others.Sum(o => o.AmountClaimed) +
        c.ParkingTransits.Sum(pt => pt.AmountClaimed) +
        c.Travels.Sum(tr => tr.AmountClaimed);

    public static decimal TotalClaimAmount(this Claim c) {
        return TotalClaimAmountFunc(c);
    }
}

void Main() {
    var q = from a in Applicants
         where(a.Claims.Any())
         select a.Claims.Sum(MyExt.TotalClaimAmountFunc);

    q.Dump();
}

edited Feb 07 '14 at 07:45

answered Feb 05 '14 at 23:14

Lukas Kabrt

5,441
4
43
58

Hi Lukas, your code is not working. It got compiling errors. "The name 'TotalClaimAmountFunc' does not exist in the current context" – Deep in Development Feb 06 '14 at 18:05
I just forget `MyExt`, please see edited answer. – Lukas Kabrt Feb 06 '14 at 18:37
I suspect you'll need to use Expression> instead of Func<> – Joe Albahari Feb 07 '14 at 01:02
Hi Lukas, it still can not pass compiling. The error shows "The name 'TotalClaimAmountExpression' does not exist in the current context" – Deep in Development Feb 07 '14 at 02:18
Hi Joe Albahari. I am your fans. I love Linqpad very much. I hope oracle Intelligent Prompt can like sql server Intelligent Prompt. That will be very nice. Thank you bring Linqpad to the world. – Deep in Development Feb 07 '14 at 02:54
Look like I should always try to compile the code ... fixed another typo. `TotalClaimAmount` uses `TotalClaimAmountFunc` not `TotalClaimAmountExpression`. – Lukas Kabrt Feb 07 '14 at 07:47
Hi Lukas, thanks a lot for your effort. The code pass compile now. It show s another error when run it : "NotSupportedException: Unsupported overload used for query operator 'Sum'." – Deep in Development Feb 07 '14 at 12:47

score 4 · Accepted Answer · answered Feb 05 '14 at 22:55

4

There is no corresponding command for your custom method in SQL. so compiler could not translate your expression. Instead first get your items,then select your results:

 var items = Applicants.Where(a => a.Claims.Any()).ToList();
 var results = items.Select(a => a.Claims.Sum(c => c.TotalClaimAmount());

answered Feb 05 '14 at 22:55

Selman Genç

100,147
13
119
184

2

Of course this will run everything on the client, causing the N+1 problem and horrible performance. – usr Feb 05 '14 at 22:56
probably.but I don't see another (better) way :) – Selman Genç Feb 05 '14 at 23:04

How to solve issue "Could not translate expression ...into SQL and could not treat it as a local expression."

2 Answers2

Linked