The column name is not valid. [Node name (if any) = t0, column name = version]

Question

I am encountering a problem while trying to query the SQLCE database in my Mango app for Windows Phone.

I get an exception when I execute

foreach (var item in myDataContext.MyTable.Select(item => item))

The column name is not valid. [ Node name (if any) = t0,Column name = version ]

Strange when I execute a query based on any single column, it works fine

foreach (var item in myDataContext.MyTable.Select(item => item.SomeColumn))

Any idea what might be wrong here?

Answer 1

I installed LINQ to SQL Debug Visualizer to find out exactly which query is generated behind the scenes, and that was

{SELECT [t0].[version], [t0].[ID], [t0].[Volume], ... similarly rest of the columns FROM [MyTable] AS [t0]

This was strange because I did not have a version column in my table (ever). I looked into my model and found this column

 [Column(IsVersion = true)] private Binary version; 

I deleted the column by commenting out these two lines and restarting the application. The newly created SQL did not have a version column, and my query worked fine.

I am using SQLCEMangoCodeGenerator to generate LINQ to SQL classes. I think the error in this tool, due to which it created an additional column, which I did not have in my table

Answer 2

I had the same issue with the SQLCEMangoCodeGenerator tool.

I started using the SQL Server Compact Toolbox tool and I had no problems: http://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1/

(Search for “NEW: DataContext” on the above website for a screenshot. Once you have installed the extension, click “SQL Server Compact Toolbox” in the “Tools” menu)

Answer 3

I solved this problem by deleting the entire database and re-creating it.

It's a shame this happens if you have an older version of the table in the database than you want to use, so this column does not exist in this table.

Answer 4

In SqlCE.sdf with running SQL Server Managment Studio:

DELETE FROM [User] WHERE [User].[UserName] = "zz"

There was a similar problem with an invalid column name, but changed the quotes to apostrophes :

DELETE FROM [User] WHERE [User].[UserName] = 'zz'

and it worked!