LINQ To SQL exception: Local sequence cannot be used in LINQ to SQL implementation

Question

Hello, everybody. I know, that this topic has been discussed yet. But, unfortunately, I didn't find any solution in existing answers.So, I have the next code:

public List<List<string>> DataTableParser(IQueryable<T> queriable)
    {
        //I missed the unnecessary code

        return queriable.Select(SelectProperties).ToList();

        //I missed the unnecessary code        
    }

    private Expression<Func<T, List<string>>> SelectProperties
    {
        get
        {
            var properties = typeof(T).GetProperties();
            // 
            return value => properties.Select
                                        (
                // empty string is the default property value
            prop => (prop.GetValue(value, null) ?? string.Empty).ToString()
                                        )
                                       .ToList();
        }
    }

So, in the method DataTableParser I have the exception with the next message:
"Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator". I don't use in my query "where" part. So I can't imagine how to use "Contains" operator. And I can't understand the reason of the exception. Does anyone have any ideas? I will appreciate any help. Thanks.

Answer 1

try using

return queriable.AsEnumerable().Select(SelectProperties).ToList();

this evaluates the sql of the queriable first and creates in memory objects that will then be processable by reflection

linq to sql only knows how to translate an expression into sql. there is a limited number of expressions that are translatable to sql. the properties that represent your columns are translatable to sql.

queriable.Select(x=>x.MyColumn);
//is translatable to sql when there is a column that is named MyColumn in your table

queriable.Where(x=>x.MyColumn.Contains("X"))
//is translatable to sql as "...where MyColumn like '%X%' ..."

queriable.Select(x=> new { x.MyColumn, x.AnotherColumn})
//is translatable to sql for selecting multiple columns

queriable.Select(SelectProperties)
//is not translatable to sql because it does not return an expression that selects a single value, and its not an expression that returns a new object.

How do you intend to use this method?