Error with Nhibernate: System.Data.SqlClient.SqlException: Incorrect syntax near 'Index'

Question

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 = '[email protected]', @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();
    }
}

Can you post the mapping and class for Tracks_Credits? Also the code that you used before calling the save(). — Kevin Crowell
– Kevin Crowell, Commented Jul 22, 2010 at 0:35
Add the Track and the Credit map. there is no Tracks_Credits map because it's just a HasManyToMany — bryan costanich
– bryan costanich, Commented Jul 22, 2010 at 0:43

Jamie Ide · Accepted Answer · 2010-07-22 01:18:10Z

14

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

answered Jul 22, 2010 at 1:18

Jamie Ide

49.4k18 gold badges85 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bryan costanich Over a year ago

If i do AsBag, can i still use IList on the entity?

DanP Over a year ago

@bryan: yes you can...a set is the collection to which IList is not applicable.

majimenezp · Accepted Answer · 2011-08-30 17:27:16Z

0

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

answered Aug 30, 2011 at 17:27

majimenezp

3122 silver badges3 bronze badges

Collectives™ on Stack Overflow

Error with Nhibernate: System.Data.SqlClient.SqlException: Incorrect syntax near 'Index'

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related