LINQ to Entities does not recognize the method 'System.String get_Item(Int32)' method

Question

I am trying to assign a value to a list of classes, but it doesn't seem to like "outside" values in my select statement.

Code in C# .dll file (compiles with no errors in Visual Studio):

    Dictionary<int, string> My_dict1 = new Dictionary<int, string>();
    My_dict1.Add(0, "Welcome");

    var inv = traces.Select(x => new TraceLabelData
    {
        Name = My_dict1[0]
    }).ToList();

Error when referenced and run in LINQPAD:

NotSupportedException LINQ to Entities does not recognize the method 'System.String get_Item(Int32)' method, and this method cannot be translated into a store expression.

If I replace My_dict[0] with a plain string it works, and if I replace it with variable that's a plain string it works. It doesn't work with the dictionary, and it doesn't work with function that returns a string.

Answer 1

Linq-to-Entities can only use Linq operations compatible with your back-end database server. Obviously your My_dict1 object doesn't exist in the remote-server (i.e. you can't use it from raw SQL). Instead, pull your objects into memory using AsEnumerable(), ToList(), or ToListAsync() before getting your in-memory dictionary involved:

    Dictionary<int, string> dict = new Dictionary<int, string>();
    dict.Add( 0, "Welcome" );

    traces
        .AsEnumerable()
        .Select( t => new TraceLabelData() { Name = dict[0] } )
        .ToList();