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

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
Post Reply
linusconcepcion
Posts: 4
Joined: Thu 27 Dec 2018 04:17

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

Post by linusconcepcion » Fri 15 Nov 2019 16:22

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="test@email.com", 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
Site Admin
Posts: 8920
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Sat 16 Nov 2019 18:03

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="test@email.com", 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
Posts: 4
Joined: Thu 27 Dec 2018 04:17

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

Post by linusconcepcion » Mon 18 Nov 2019 13:30

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
Site Admin
Posts: 8920
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Fri 22 Nov 2019 15:23

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.

Post Reply