Reserved words

I have a database table in SQL Server 2005 called User. I get this error when I get an entity from the database:

 System.Data.SqlClient.SqlException : Incorrect syntax near the keyword 'User'.

This is the SQL generated:

exec sp_executesql N'SELECT
  User.UserId,
  User.DisplayName,
  User.SystemName
FROM
  User
WHERE
  User.UserId = @p0',N'@p0 uniqueidentifier',@p0='BD736773-D8F4-4FD8-A13B-F94D02605831'

Thanks James, the ability to quote identifiers has always been there. Please enable it on your LightSpeedContext by setting the QuoteIdentifiers to true. 

I hope this helps,

John-Daniel Trask

It would be nice if this was enabled by default rather than having to turn it on by hand. That would have saved me 1/2 hour of gawping at at "Incorrect syntax near the keyword 'Key'" error.

Is there any downside to generating safer SQL by default?

There are a couple of potential downsides:

1. It makes the SQL more cluttered and hard to read.  On the other hand, by default, you're not reading the SQL, so it's reasonable to say that the solution to this is to quote identifiers by default and let people turn it off if it annoys them.

2. On some databases, quoting seems to have a dual role: it escapes reserved words, but it also turns on case sensitivity.  This could result in confusing behaviour on these databases and would be a breaking change for users of those databases.

We could make the default behaviour depend on the database provider (e.g. default to quoting for SQL Server, but to not quoting for Oracle or PostgreSQL), but that makes LightSpeed's behaviour harder for users to understand and predict.  I feel your pain though because I've been bitten by the same issue!  I'll raise this with the Mindscape brains trust and see if we can come up with anything.