Why does LINQ-to-SQL sometimes allow me to project using a function, but sometimes it does not?

Question

This is really puzzling me. I know LINQ-to-SQL handles selects by processing the expression tree and attempting to translate stuff via the generated query, which is why some function translations don't work property (string.IsNullOrWhitespace is a regular annoyance).

I hit a situation in my code where LINQ-to-SQL was able to call my helper function via a Select projection ... sometimes. In fact, it works when the method has one name, but doesn't work with it has another name. I think this is best illustrated by the following program (this is about as simple as I could make it):

// #define BREAK_THE_CODE

using System;
using Sandbox.Data;
using System.Collections.Generic;
using System.Linq;

namespace Sandbox.Console
{

    class Program
    {
        static void Main(string[] args)
        {
            using (var dataContext = new SandboxDataContext())
            {
                List<MyValue> myValueList;

                try
                {
                    myValueList = dataContext.Numbers.Select(x => new MyValue
                    {
#if BREAK_THE_CODE
                        Type = ToValueType(x.Value),
#else
                        Type = DoIt(x.Value),
#endif
                    }).ToList();
                }
                catch (NotSupportedException)
                {
                    System.Console.WriteLine("Not supported, oh noes!");
                    System.Console.ReadKey();
                    return;
                }

                System.Console.WriteLine(myValueList.Count);
                System.Console.ReadKey();
            }
        }

#if BREAK_THE_CODE
        public static MyValueType ToValueType(int value)
#else
        public static MyValueType DoIt(int value)
#endif
        {
            return MyValueType.One;
        }

        public sealed class MyValue
        {
            public MyValueType Type { get; set; }
        }

        public enum MyValueType
        {
            One,
            Two,
            Unknown,
        }
    }
}

The backing database just has a single table named [Number] with a single column [Value] INT NOT NULL.

As written, the program works for me, but uncommenting the #define at the top will switch the name of the method call and then the program will throw a NotSupportedException when executing ToList().

I've tried several different method names to try to determine a pattern, but have not been able to. It looks like if the method is named anything that starts with To it will throw the NotSupportedException, but seems to work with any other name. This is still odd.

Can someone explain what is happening?

Here's another sample without the #define toggle, it just does both methods back to back and I'm still seeing the same issue. Specifically, DoIt works, but ToValueType does not.

using System;
using Sandbox.Data;
using System.Linq;

namespace Sandbox.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var dataContext = new SandboxDataContext())
            {
                try
                {
                    var myValueList = dataContext.Numbers.Select(x => new MyValue
                    {
                        Type = DoIt(x.Value),
                    }).ToList();

                    System.Console.WriteLine("DoIt Succeeded, found {0} results", myValueList.Count);
                }
                catch (NotSupportedException)
                {
                    System.Console.WriteLine("DoIt Failed, oh noes!");
                }

                try
                {
                    var myValueList = dataContext.Numbers.Select(x => new MyValue
                    {
                        Type = ToValueType(x.Value),
                    }).ToList();

                    System.Console.WriteLine("ToValueType Succeeded, found {0} results", myValueList.Count);
                }
                catch (NotSupportedException)
                {
                    System.Console.WriteLine("ToValueType Failed, oh noes!");
                }

                System.Console.ReadKey();
            }
        }

        public static MyValueType DoIt(int value)
        {
            return MyValueType.SpecialType;
        }

        public static MyValueType ToValueType(int value)
        {
            return MyValueType.SpecialType;
        }

        public sealed class MyValue
        {
            public MyValueType Type { get; set; }
        }

        public enum MyValueType
        {
            SpecialType,
        }
    }
}

Answer 1

This is a L2S bug. This is expected to work.

In the decompiled source code I find:

    private static MethodSupport GetDecimalMethodSupport(SqlMethodCall mc)
    {
        if (mc.Method.IsStatic)
        {
            if (mc.Arguments.Count == 2)
            {
                string str;
                if (((str = mc.Method.Name) != null) && ((((str == "Multiply") || (str == "Divide")) || ((str == "Subtract") || (str == "Add"))) || ((str == "Remainder") || (str == "Round"))))
                {
                    return MethodSupport.Method;
                }
            }
            else if (mc.Arguments.Count == 1)
            {
                string str2;
                if (((str2 = mc.Method.Name) != null) && (((str2 == "Negate") || (str2 == "Floor")) || ((str2 == "Truncate") || (str2 == "Round"))))
                {
                    return MethodSupport.Method;
                }
                if (mc.Method.Name.StartsWith("To", StringComparison.Ordinal))
                {
                    return MethodSupport.Method;
                }
            }
        }
        return MethodSupport.None;
    }

Searching for "To":

if (mc.Method.Name.StartsWith("To", StringComparison.Ordinal))

Hm... There is one other place. Maybe L2S thinks your value types are decimals. Try adding a second argument to break the mc.Arguments.Count == 1 condition.

Anyway, this is a bug. There is no deeper reason behind this. Normally, L2S can execute functions in the final Select just fine. This is an awesome feature that EF is still lacking.

Lay this to rest and migrate to EF asap. L2S is abandoned.