none
No supported translation to SQL (again) RRS feed

  • Question

  • Hello!

    I have a static class with two methods:

            public static string ToNumberString(this double value)
            {
                return value.ToString("N");
            }

            public static string ToMoneyString(this double value, string currencySymbol)
            {
                return string.Format("{0:N} {1}", value, currencySymbol);
            }

     


    In my page I have this Linq to Sql query:

            var q = from x in db.MyTable
                    select new
                    {
                        x.MyID,
                        a = x.MyDouble.ToNumberString(),
                        b = x.MyDouble.ToMoneyString("$")
                    };

     

    Problem:
    Ocurrs an exception:
    Method 'System.String ToNumberString(Double)' has no supported translation to SQL.

    So, the question is:
    Why the exception ocurrs only when call to ToNumberString() is present? Because if I remove the line "a = x.MyDouble.ToNumberString()" from the linq query, no excepcetion ocurrs...
    Any one can explain this? Please. Thanks.

    Thursday, September 10, 2009 10:27 PM

Answers

  • LINQ to SQL can't translate your methods into the equivalent versions on the server because they don't exist on the server. In this case, you could force the projection client side by changing it to this:

            var q = from x in db.MyTable.AsEnumerable()
                    select new
                    {
                        x.MyID,
                        a = x.MyDouble.ToNumberString(),
                        b = x.MyDouble.ToMoneyString("$")
                    };

    Alternatively, you could create server side scalar functions and use them in your query rather than the client side methods.

    Jim Wooley
    www.ThinqLinq.com
    http://www.LinqInAction.net - "LINQ In Action", The book is now available. Don't wait for the movie
    • Marked as answer by Hugo13246578 Monday, September 14, 2009 8:31 AM
    Sunday, September 13, 2009 11:43 PM
    Moderator

All replies

  • LINQ to SQL can't translate your methods into the equivalent versions on the server because they don't exist on the server. In this case, you could force the projection client side by changing it to this:

            var q = from x in db.MyTable.AsEnumerable()
                    select new
                    {
                        x.MyID,
                        a = x.MyDouble.ToNumberString(),
                        b = x.MyDouble.ToMoneyString("$")
                    };

    Alternatively, you could create server side scalar functions and use them in your query rather than the client side methods.

    Jim Wooley
    www.ThinqLinq.com
    http://www.LinqInAction.net - "LINQ In Action", The book is now available. Don't wait for the movie
    • Marked as answer by Hugo13246578 Monday, September 14, 2009 8:31 AM
    Sunday, September 13, 2009 11:43 PM
    Moderator
  • Hmm, it's still annoying it does it though for the top-level projection given that we could theoretically execute these functions client-side as we materialize.

    [)amien
    Monday, September 14, 2009 1:32 AM
    Moderator
  • You may want to check and make sure that .Net 3.5 SP1 is applied. If I recall, prior to SP1, any use of client side code would cause this issue. With SP1, client side code in the final projection was done on the client side (but required hydrating the entire object and then projecting into the resulting anonymous type). Using this kind of client side code anywhere other than the final projection (ex. in the Where predicate) will still cause the same error you are experiencing.

    Jim Wooley
    www.ThinqLinq.com
    http://www.LinqInAction.net - "LINQ In Action", The book is now available. Don't wait for the movie
    Tuesday, September 15, 2009 2:42 AM
    Moderator