How can I prevent a SQL incorrect syntax error when mapping a one-to-many relationship in Fluent NHibernate when using a custom collection class?

I am trying to map a one-to-many relationship in Fluent NHibernate where the collection property is an custom collection class. I am using a protected InnerList property on the collection class to expose the collection but am getting a SQL error when I try and save an item.

The Paragraph class has an Elements property of type ElementList. The mapping for this is:

public ParagraphMap()
{
    this.Id(x => x.Id);
    Component(
        x => x.Elements,
        m => m.HasMany<Element>(Reveal.Member<ElementList>("InnerList")).AsList().KeyColumn("ParagraphId").CollectionType<Element>());
}

The InnerList property on the ElementList class is:

protected List<Element> InnerList
{
    get { return this.list; }
    set { this.list = value; }
}

I have settled on using the AsList extension for the mapping and the List data type for the InnerList property because of much trial and error getting invalid cast errors when using any other data types.

When creating my repository I get the error, "System.Data.SqlClient.SqlException: Incorrect syntax near 'Index'. ".

The relevant part of the generated SQL is:

create table [Element] (
    Id INT IDENTITY NOT NULL,
   ParagraphId INT null,
   Index INT null,
   primary key (Id)
)

What is wrong with my mapping?

UPDATE: I have now reverted to using an array instead of a generic list for the property with the following mapping in my ParagraphMap:

Component(
    x => x.Elements,
    m => m.HasMany<Element>(Reveal.Member<ElementList>("InnerList")).AsArray(x => x.Id).KeyColumn("ParagraphId").Cascade.All());

I no longer get the SQL error on creating the database but still cannot add data. I get a null reference error on trying to add Paragraphs:

var paragraph1 = new Paragraph(new element[] { new Element(), new Element() });
var paragraph2 = new Paragraph(new element[] { new Element(), new Element() });

using (var unitOfWork = this.repository.Begin())
{
    try
    {
        this.repository.Add(paragraph1);
        this.repository.Add(paragraph2);
    }
    catch (Exception)
    {
        unitOfWork.Rollback();
        throw;
    }

    unitOfWork.Commit();
}

I assume there is still something wrong with the mapping but don't know what.

UPDATE: This is the stack trace for the null reference I get when using Element[] as my type on the InnerList property:

Execute
System.NullReferenceException: Object reference not set to an instance of an object.
   at NHibernate.Engine.Collections.ProcessReachableCollection(IPersistentCollection collection, CollectionType type, Object entity, ISessionImplementor session) in d:\CSharp\NH\nhibernate\src\NHibernate\Engine\Collections.cs:line 104
   at NHibernate.Event.Default.FlushVisitor.ProcessCollection(Object collection, CollectionType type) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\FlushVisitor.cs:line 40
   at NHibernate.Event.Default.AbstractVisitor.ProcessValue(Object value, IType type) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\AbstractVisitor.cs:line 51
   at NHibernate.Event.Default.AbstractVisitor.ProcessValue(Int32 i, Object[] values, IType[] types) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\AbstractVisitor.cs:line 37
   at NHibernate.Event.Default.AbstractVisitor.ProcessValues(Object[] values, IType[] types) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\AbstractVisitor.cs:line 32
   at NHibernate.Event.Default.AbstractVisitor.ProcessComponent(Object component, IAbstractComponentType componentType) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\AbstractVisitor.cs:line 77
   at NHibernate.Event.Default.AbstractVisitor.ProcessValue(Object value, IType type) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\AbstractVisitor.cs:line 59
   at NHibernate.Event.Default.AbstractVisitor.ProcessValue(Int32 i, Object[] values, IType[] types) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\AbstractVisitor.cs:line 37
   at NHibernate.Event.Default.AbstractVisitor.ProcessEntityPropertyValues(Object[] values, IType[] types) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\AbstractVisitor.cs:line 120
   at NHibernate.Event.Default.DefaultFlushEntityEventListener.OnFlushEntity(FlushEntityEvent event) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\DefaultFlushEntityEventListener.cs:line 58
   at NHibernate.Event.Default.AbstractFlushingEventListener.FlushEntities(FlushEvent event) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Defau开发者_如何学Golt\AbstractFlushingEventListener.cs:line 161
   at NHibernate.Event.Default.AbstractFlushingEventListener.FlushEverythingToExecutions(FlushEvent event) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\AbstractFlushingEventListener.cs:line 60
   at NHibernate.Event.Default.DefaultAutoFlushEventListener.OnAutoFlush(AutoFlushEvent event) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\DefaultAutoFlushEventListener.cs:line 30
   at NHibernate.Impl.SessionImpl.AutoFlushIfRequired(ISet`1 querySpaces) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1154
   at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 646
   at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 634
   at NHibernate.Impl.ExpressionQueryImpl.List() in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\ExpressionQueryImpl.cs:line 63
   at NHibernate.Linq.NhQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) in d:\CSharp\NH\nhibernate\src\NHibernate\Linq\NhQueryProvider.cs:line 78
   at NHibernate.Linq.NhQueryProvider.Execute(Expression expression) in d:\CSharp\NH\nhibernate\src\NHibernate\Linq\NhQueryProvider.cs:line 27
   at NHibernate.Linq.NhQueryProvider.Execute[TResult](Expression expression) in d:\CSharp\NH\nhibernate\src\NHibernate\Linq\NhQueryProvider.cs:line 101
   at Remotion.Data.Linq.QueryableBase`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Tests.WhenWorkingWithNHibernateRepository.CanGetAllParagraphs() in C:\test\Infrastructure\Infrastructure.Tests\Repository\WhenWorkingWithNHibernateRepository.cs:line 659

---

You have a column that is called Index, while Index is a reserved word (keyword) in SQL, so you'll have to make sure that NHibernate escapes that column.

When you're defining your mappings in classic hbm.xml files, you can define that NHibernate should escape that column by mapping it using backticks, like this:

<property name="myProperty" column="`Index`" />

So, in Fluent NHibernate, in the mapping where you map your class to the Element table, try defining the mapping like this:

Map (x => x.MyProperty).Column ("`Index`");

(Note that backticks are not equal to quotes!)

By doing this, NHibernate will generate an SQL statement where the Index column is escaped; like this, for example:

SELECT Id, ParagraphId, [Index] FROM [Element]

---

This is because you are using .AsList(),

try using .AsBag() instead.

---

Use the following configuration entry

<property name="hbm2ddl.keywords">auto-quote</property>

Explanation: NHibernate will quote all table/column names that match reserved SQL words, declared in the dialect in use. This is especially true when you handle .AsList() mapping because it adds an Index column you have no control over (so you can't rename).

---

In c# class Element try rename c# integer property Index to ElementIndex to see if there is a naming conflict.