title: Error with Nhibernate: System.Data.SqlClient.SqlException: Incorrect syntax near 'Index'
body: <p>i have an nhibernate solution and i'm trying to do a save, but i'm getting this error:</p>
  
  <pre><code>Test method HelloMusic.Core.Test.CrudTests.TestTrackAdd threw exception: 
  NHibernate.Exceptions.GenericADOException: could not insert collection: [HelloMusic.BLL.Track.Credits#20][SQL: INSERT INTO Tracks_Credits (TrackID, Index, CreditID) VALUES (@p0, @p1, @p2)] ---&gt; System.Data.SqlClient.SqlException: Incorrect syntax near 'Index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
  </code></pre>
  
  <p>The SQL is as follows:</p>
  
  <pre><code>NHibernate: SELECT this_.GenreID as GenreID9_0_, this_.GenreName as GenreName9_0_ FROM Genres this_
  NHibernate: INSERT INTO Tracks (ContainsSamples, Description, HasExplicitLyrics, IsCover, Lyrics, Name, OrderIndex, GenreID) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7); select SCOPE_IDENTITY();@p0 = False, @p1 = 'Teh awesome18133437', @p2 = True, @p3 = False, @p4 = 'b'z in yer mouth18141375', @p5 = 'beez in yer mouth18141375', @p6 = 1, @p7 = 1
  NHibernate: INSERT INTO Credits (Email, Location, Name, Role) VALUES (@p0, @p1, @p2, @p3); select SCOPE_IDENTITY();@p0 = 'foo@foo.com', @p1 = NULL, @p2 = 'Some Dood', @p3 = 'teh Awesums'
  NHibernate: INSERT INTO Images (ForeignEntityID, ForeignEntityType, CreatedDate, Extension, FileName, Height, IsOriginal, LastModifiedDate, LocationPath, MetaData, SizeInKiloBytes, Type, Width) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12); select SCOPE_IDENTITY();@p0 = 0, @p1 = 'Track', @p2 = 7/21/2010 5:20:59 PM, @p3 = 'jpregg', @p4 = 'tex0r', @p5 = 0, @p6 = True, @p7 = 7/21/2010 5:21:00 PM, @p8 = '//server/yourmom', @p9 = NULL, @p10 = 5898, @p11 = NULL, @p12 = 0
  NHibernate: INSERT INTO Languages (Name) VALUES (@p0); select SCOPE_IDENTITY();@p0 = 'Spanglish'
  NHibernate: INSERT INTO Tracks_Credits (TrackID, Index, CreditID) VALUES (@p0, @p1, @p2);@p0 = 20, @p1 = 0, @p2 = 19
  </code></pre>
  
  <p>I suspect the problem line is this (i've run it in SQL and get the same error):</p>
  
  <pre><code>NHibernate: INSERT INTO Tracks_Credits (TrackID, Index, CreditID) VALUES (@p0, @p1, @p2);@p0 = 20, @p1 = 0, @p2 = 19
  </code></pre>
  
  <p>but what's throwing me here is that table doesn't have an "Index" column</p>
  
  <p>there is no "Index" anywhere in my maps either. has anyone seen this?</p>
  
  <p>why is it throwing the 'index' in there?</p>
  
  <p>here is the credit map:</p>
  
  <pre><code>public class CreditMap: ClassMap&lt;Credit&gt;
  {
      public CreditMap()
      {
          Table("Credits");
          Id(x =&gt; x.ID, "CreditId");
          Map(x =&gt; x.Email, "Email")
              .Length(1000);
          Map(x =&gt; x.Location, "Location")
              .Length(1000);
          Map(x =&gt; x.Name, "Name")
              .Length(1000);
          Map(x =&gt; x.Role, "Role")
              .Length(1000);
      }
  }
  </code></pre>
  
  <p>and here is the track map:</p>
  
  <pre><code>public class ForeignEntityTypeFilter : FilterDefinition
  {
      public ForeignEntityTypeFilter()
      {
          WithName("ForeignEntity")
              .AddParameter("IsType", NHibernate.NHibernateUtil.String);
      }
  }
  
  public class TrackMap: ClassMap&lt;Track&gt;
  {
      public TrackMap()
      {
          Table("Tracks");
          Id(x =&gt; x.ID, "TrackId");
          Map(x =&gt; x.ContainsSamples);
          Map(x =&gt; x.Description);
          Map(x =&gt; x.HasExplicitLyrics);
          Map(x =&gt; x.IsCover);
          Map(x =&gt; x.Lyrics);
          Map(x =&gt; x.Name)
              .Length(1000);
          Map(x =&gt; x.OrderIndex);
          References&lt;Genre&gt;(x =&gt; x.Genre, "GenreID");
          HasManyToMany&lt;Credit&gt;(x =&gt; x.Credits)
              .ChildKeyColumn("CreditID")
              .AsList()
              .ParentKeyColumn("TrackID")
              .Table("Tracks_Credits")
              .Not.Inverse()
              .Cascade.SaveUpdate();
          HasMany&lt;TrackImage&gt;(x =&gt; x.Images)
              .Table("Images")
              .KeyColumn("ForeignEntityID")
              .ApplyFilter&lt;ForeignEntityTypeFilter&gt;("'Track' == ForeignEntityType")
              .Not.Inverse()
              .Cascade.SaveUpdate();
          HasManyToMany&lt;Language&gt;(x =&gt; x.Languages)
              .ChildKeyColumn("LanguageID")
              .AsList()
              .ParentKeyColumn("TrackID")
              .Table("Tracks_Languages")
              .Not.Inverse()
              .Cascade.SaveUpdate();
          HasManyToMany&lt;MediaFile&gt;(x =&gt; x.MediaFiles)
              .ChildKeyColumn("MediaFileID")
              .AsList()
              .ParentKeyColumn("TrackID")
              .Table("Tracks_MediaFiles")
              .Not.Inverse()
              .Cascade.SaveUpdate();
      }
  }
  </code></pre>