LinqConnect does not put [ ] around field names that match SQL key words

[linusconcepcion]

I'm using SQL Server, and I have a class called Email. It looks something like this:

[Table(Name="Emails")
public class Email
{
public int EmailID {get;set;}
public string To {get; set;}
public string Subject {get; set;}
public string Body {get; set;}
}

Assume the fields are properly mapped to fields with the same names in the Emails table.

When I do an insert using this code:

var email = new Email { To="[email protected]", Subject="test", Body="test" };

var context = CreateContext();
context.Emails.InsertOnSubmit(email);
context.SubmitChanges();

I get an exception from SQL Server:

Devart.Data.Linq.LinqCommandExecutionException: Error on executing DbCommand. ---> System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'To'.

The issue is that LinqConnect is executing an INSERT statement, but failing to put [ ] around the field names to guard against field names that are potentially keywords for SQL Server.

This is something that works fine in Linq to SQL.

Is there a way around this issue other than renaming the fields?

[Shalex]

We cannot reproduce the issue using the following steps.

1. Create the table in SQL Server:

Code: Select all

CREATE TABLE [dbo].[Emails](
	[EmailID] [int] NOT NULL PRIMARY KEY,
	[To] [nvarchar](50) NULL,
	[Subject] [nvarchar](50) NULL,
	[Body] [nvarchar](50) NULL
)
GO

2. Right-click your project in Solution Explorer > Add > New Item > Data > Devart LinqConnect Model (*.lqml). Select the Emails table in Create Model Wizard. Save the model. As a result, the following mapping is generated:

Code: Select all

       [Column(Name = @"[To]", Storage = "_To", DbType = "NVARCHAR(50)", UpdateCheck = UpdateCheck.Never)]
        public string To
        {
            get
            {
                return this._To;
            }
            set
            {
                if (this._To != value)
                {
                    this.OnToChanging(value);
                    this.SendPropertyChanging("To");
                    this._To = value;
                    this.SendPropertyChanged("To");
                    this.OnToChanged();
                }
            }
        }

4. Enable the dbMonitor tool. Run your code, it is executed successfully:

Code: Select all

var email = new Email { To="[email protected]", Subject="test", Body="test" };

var context = CreateContext();
context.Emails.InsertOnSubmit(email);
context.SubmitChanges();

The generated SQL in dbMonitor is:

Code: Select all

INSERT INTO dbo.Emails (EmailID, [To], Subject, Body) VALUES (@p1, @p2, @p3, @p4)

[linusconcepcion]

I see. So the fix is to add the brackets to the mapping:

Column(Name="[To]")

I suppose we can do that, however, IMO, it would be better if the system just put brackets around all field and table names automatically when generating SQL, so we don't have to worry about keywords.

Thanks.

[Shalex]

IMO, it would be better if the system just put brackets around all field and table names automatically when generating SQL, so we don't have to worry about keywords

We will investigate the question and notify you about the result.

[Shalex]

The bug with quoting SQL Server reserved keywords used as identifiers in Code-First mapping is fixed: viewtopic.php?f=31&t=39714.