Orchard CMS: Beware of SQL Keywords

Just a quick reminder, before you debug hours on end (like me).

Sometimes while developing a new Orchard module I get carried away and forget about other stuff. Reserved SQL keywords for example. Then we might end up with a class like this:

public class ShoppingCartEntryPart : ContentPart<ShoppingCartEntryRecord>
{
  public int Order
  {
    get { return this.Retrieve(x => x.Order); }
    set { this.Store(x => x.Order, value); }
  }

  public int Price
  {
    get { return this.Retrieve(x => x.Price); }
    set { this.Store(x => x.Price, value); }
  }
}

and a migration like this:

public int Create()
{
  SchemaBuilder.CreateTable(
    "ShoppingCartEntryRecord",
    table => table
      .ContentPartRecord()
      .Column<int>("Price")
      .Column<int>("Order"));

  ContentDefinitionManager.AlterPartDefinition(
    typeof(ShoppingCartEntryPart).Name,
    config => config.Attachable());

  return 1;
}

The module will of course compile, but as soon as we use the ContentManager to retrieve or store it, it will crash with an exception like this:

NHibernate.Exceptions.GenericADOException:
'could not insert: [ReservedKeywordsTest.Models.ShoppingCartEntryRecord#12][sql: insert into reservedkeywordstest_shoppingcartentryrecord (price, order, id) values (?, ?, ?)]'

InnerException
SqlCeException: There was an error parsing the query.
[ Token line number = 1, Token line offset = 66, Token in error = Order ]

That's because we're using SQL-Server or SQLCE where ORDER is a reserved keyword. Check this list on MSDN for all reserved keywords.

In the example above we'd just need to rename Order to something like SortOrder and we're good to go again.