Linq to SQL and SQL Server Compact Error: "There was an error parsing the query."

Question

I created a SQL server compact database (MyDatabase.sdf), and populated it with some data. I then ran SQLMetal.exe and generated a linq to sql class (MyDatabase.mdf)

Now I'm trying to select all records from a table with a relatively straightforward select, and I get the error:

"There was an error parsing the query. [ Token line number = 3,Token line offset = 67,Token in error = MAX]"

Here is my select code:

public IEnumerable<Item> ListItems()
{
    MyDatabase db_m = new MyDatabase("c:\mydatabase.sdf");
    return this.db_m.TestTable.Select(test => new Item()
        {
            ID = test.ID,
            Name = test.Name,
            RequestData = test.RequestData != null ? test.RequestData.ToString() : null,   
            Url = new System.Uri(test.Uri)
        }.AsEnumerable();
}

I've read that Linq to SQL works with Sql Compact, is there some other configuration I need to do?

Answer 1

Could it be an errant NVARCHAR(MAX)? I think I've seen an error like this before with sql compact edition, and as I recall it had to do with the fact that sql compact edition doesn't support the NVARCHAR(MAX) datatype. This is also maybe why you see the "token in error = MAX" message in the exception?

Answer 2

Why do you need to do the conversion on RequestData? What does your class look like? Can you just set it like this?

    RequestData = test.RequestData