Error with Nhibernate: System.Data.SqlClient.SqlEx

i have an nhibernate solution and i'm trying to do a save, but i'm getting this error:

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)] ---> 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.

The SQL is as follows:

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

I suspect the problem line is this (i've run it in SQL and get the same error):

NHibernate: INSERT INTO Tracks_Credits (TrackID, Index, CreditID) VALUES (@p0, @p1, @p2);@p0 = 20, @p1 = 0, @p2 = 19

but what's throwing me here is that table doesn't have an "Index" column

there is no "Index" anywhere in my maps either. has anyone seen this?

why is it throwing the 'index' in there?

here is the credit map:

public class CreditMap: ClassMap<Credit>
{
    public CreditMap()
    {
        Table("Credits");
        Id(x => x.ID, "CreditId");
        Map(x => x.Email, "Email")
            .Length(1000);
        Map(x => x.Location, "Location")
            .Length(1000);
        Map(x => x.Name, "Name")
            .Length(1000);
        Map(x => x.Role, "Role")
            .Length(1000);
    }
}

and here is the track map:

public class ForeignEntityTypeFilter : FilterDefinition
{
    public ForeignEntityTypeFilter()
    {
        WithName("ForeignEntity")
            .AddParameter("IsType", NHibernate.NHibernateUtil.String);
    }
}

public class TrackMap: ClassMap<Track>
{
    public TrackMap()
    {
        Table("Tracks");
        Id(x => x.ID, "TrackId");
        Map(x => x.ContainsSamples);
        Map(x => x.Description);
        Map(x => x.HasExplicitLyrics);
        Map(x => x.IsCover);
        Map(x => x.Lyrics);
        Map(x => x.Name)
            .Length(1000);
        Map(x => x.OrderIndex);
        References<Genre>(x => x.Genre, "GenreID");
        HasManyToMany<Credit>(x => x.Credits)
            .ChildKeyColumn("CreditID")
            .AsList()
            .ParentKeyColumn("TrackID")
            .Table("Tracks_Credits")
            .Not.Inverse()
            .Cascade.SaveUpdate();
        HasMany<TrackImage>(x => x.Images)
            .Table("Images")
            .KeyColumn("ForeignEntityID")
            .ApplyFilter<ForeignEntityTypeFilter>("'Track' == ForeignEntityType")
            .Not.Inverse()
            .Cascade.SaveUpdate();
        HasManyToMany<Language>(x => x.Languages)
            .ChildKeyColumn("LanguageID")
            .AsList()
            .ParentKeyColumn("TrackID")
            .Table("Tracks_Languages")
            .Not.Inverse()
            .Cascade.SaveUpdate();
        HasManyToMany<MediaFile>(x => x.MediaFiles)
            .ChildKeyColumn("MediaFileID")
            .AsList()
            .ParentKeyColumn("TrackID")
            .Table("Tracks_MediaFiles")
            .Not.Inverse()
            .Cascade.SaveUpdate();
    }
}

回答1:

The problem is the AsList() mappings for the collections. This maps the collection as an ordered list which requires an index column in the database to maintain the order. You probably want to map them using AsBag().

回答2:

If you are using automapping and already do an override to create a many-to-many relationship, you need to add to set the index name for the column, for example:

public class ManytoManyConvention: IHasManyToManyConvetion
{
   public void Apply(IManyToManyCollectionInstance instance)
   {
      instance.Index.Column("Index1");
   }
}

This create a table like this:

create table Table1ToTable2 (
       Table1_id INT not null,
       Table2_id INT not null,
       Index1 INT not null,
       primary key (Table1_id, Index1)
    )