Quoting SQLite column name that is keyword

[DavidF]

In LinqConnect for Metro, using the SQLite provider, is it possible to specify that column names that are also keywords are quoted in the generated SQL?

Example:

I have an entity mapped as follows:

<Table Name='Indicator' Member='Indicators'>
<Type Name='Indicator'>
<Column Member='Id' IsPrimaryKey = 'true' />
<Column Member='Name' />
<Column Member='Description' />
<Column Member='Group' />
<Column Member='IsKey' />
<Column Member='DisplayOrder' />
</Type>
</Table>

Note that 'Group' is a SQLite keyword.

The following Linq query:

from i in _session.Indicators where i.Group == groupName orderby i.DisplayOrder select i

generates this SQL:

SELECT t1.Id AS Id, t1.Name AS Name, t1.Description AS Description, t1.Group AS `Group`, t1.IsKey AS IsKey, t1.DisplayOrder AS DisplayOrder
FROM Indicator t1
WHERE t1.Group = :p0
ORDER BY t1.DisplayOrder

Which gives the following error:

Devart.Data.SQLite.SQLiteException
HResult=-2146233088
Message=SQLite error
near "Group": syntax error
Source=Devart.Data.SQLite
ErrorCode=0
StackTrace:
at . (String , UInt32 , String& )
at . ()
at . ()
at Devart.Data.SQLite.SQLiteDataReader. ()
at Devart.Data.SQLite.SQLiteCommand.InternalExecute(CommandBehavior behavior, IDisposable statement, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.m8ely6dkey8cq72cwv8bdd53g5x84avl ​ ()
at Devart.Data.Linq.DataProvider. (ICompiledQuery , Object[] )

[MariiaI]

Please perform the following steps:
- open your model;
- double-click the "Group" property of the necessary entity class - the Property Editor will open;
- put the value of the Source field in double quotes, e.g. "Group";
- save the changes and try your code.

After these steps your mapping should be like this:

Code: Select all

<Table Name='Indicator' Member='Indicators'>
<Type Name='Indicator'>
<Column Member='Id' IsPrimaryKey = 'true' />
...
<Column Name=""Group"" Member="Group" />
...
</Type>
</Table>

Please tell us if this helps.