Unable to create schema with Castle ActiveRecord

Question

Hi all,

I'm trying to get started with Castle ActiveRecord (following the "Getting started with ActiveRecord" screencast).

I created two ActiveRecord classes, one of them named User. I added the relevant strings to the app.config file, and I initialize using:

var config = ActiveRecordSectionHandler.Instance;
ActiveRecordStarter.Initialize(typeof(User).Assembly, config);

ActiveRecordStarter.CreateSchema();

Unfortunately, when I run this, I get the following exception:

Unhandled Exception: Castle.ActiveRecord.Framework.ActiveRecordException: Could not create the schema ---> NHibernate.HibernateException: Incorrect syntax near the keyword 'User'. ---> System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'User'

I uploaded a small reproduction to http://bitbucket.org/hmemcpy/general, could someone please tell me what have I done wrong?

Thanks!

Answer 1

'User' is a reserved word in SQL server, the SQL command being executed is:

create table User (Id INT IDENTITY NOT NULL,
                   UserName NVARCHAR(255) null unique,
                   primary key (Id))

It's illegal to name a table User in SQL server, but it is legal to name it [User].

create table [User] (Id INT IDENTITY NOT NULL,
                   UserName NVARCHAR(255) null unique,
                   primary key (Id))

As a solution you can define different names for tables and columns named by reserved words:

[ActiveRecord("[User]")]
public class User : ActiveRecordLinqBase<User>
{
    [PrimaryKey]
    public int Id { get; set; }

    [Property(Unique = true)]
    public string UserName { get; set; }

    [HasMany]
    public IList<Activity> Activities { get; set; }
}

When using a [ ] around the table name, reserved names are allowed. The same is relevant for relation columns:

[ActiveRecord]
public class Activity : ActiveRecordLinqBase<Activity>
{
    [PrimaryKey]
    public int Id { get; set; }

    [BelongsTo("[User]")]
    public User User { get; set; }
}

Of course, any other name like [ActiveRecord("SomeUser")] will work.