Failed to create schema using Castle ActiveRecord

Question

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

I created two ActiveRecord classes, one of which is called User. I added the appropriate lines to the file app.config, and I initialize with:

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: Failed to create schema ---> NHibernate.HibernateException: Invalid syntax next to the User keyword. ---> System.Data.SqlClient.SqlException: Invalid syntax next to the 'User' keyword

I uploaded a small reproduction of http://bitbucket.org/hmemcpy/general , can someone please tell me what I did wrong?

Thank!

Answer 1

"User" is a reserved word on the SQL server, executed by the SQL command:

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

It is incorrect to name the User table on the SQL server, but it is the right to call 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 with 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 [] around the table name, reserved names are valid. The same applies to relationship 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, for example [ActiveRecord("SomeUser")], will work.