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?
LinqConnect does not put [ ] around field names that match SQL key words
-
- Posts: 0
- Joined: Thu 27 Dec 2018 04:17
Re: LinqConnect does not put [ ] around field names that match SQL key words
We cannot reproduce the issue using the following steps.
1. Create the table in SQL Server:
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:
4. Enable the dbMonitor tool. Run your code, it is executed successfully:
The generated SQL in dbMonitor is:
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
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();
}
}
}
Code: Select all
var email = new Email { To="[email protected]", Subject="test", Body="test" };
var context = CreateContext();
context.Emails.InsertOnSubmit(email);
context.SubmitChanges();
Code: Select all
INSERT INTO dbo.Emails (EmailID, [To], Subject, Body) VALUES (@p1, @p2, @p3, @p4)
-
- Posts: 0
- Joined: Thu 27 Dec 2018 04:17
Re: LinqConnect does not put [ ] around field names that match SQL key words
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.
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.
Re: LinqConnect does not put [ ] around field names that match SQL key words
We will investigate the question and notify you about the result.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
Re: LinqConnect does not put [ ] around field names that match SQL key words
The bug with quoting SQL Server reserved keywords used as identifiers in Code-First mapping is fixed: viewtopic.php?f=31&t=39714.