Fluent NHibernate bulk data insert

Question

I need to transfer some data to other db, but when I start this mssql server freeze. ds2 row count about 250,000. What am I doing wrong?

every time I try same funcion first looks working and after give error on random row. maybe 5, 95 or 280. until error records looks valid.

        string inlineSql = "Select * from album";
        DataSet ds2 = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, inlineSql);

        for (int i = 0; i < ds2.Tables[0].Rows.Count; i++)
        {
            Entity.Album.Album album = new Entity.Album.Album();
            album.AlbumName = ds2.Tables[0].Rows[i]["AlbumName"].ToString();
            album.WebSite = (Entity.Artist.WebSite)Convert.ToInt16(ds2.Tables[0].Rows[i]["WebSite"]);
            album.Year = ds2.Tables[0].Rows[i]["Year"].ToString();

            Entity.Artist.Artist artist = Entity.Artist.Artist.READ.ById(Convert.ToInt32(ds2.Tables[0].Rows[i]["ArtistId"]));
            if (artist != null)
            {
                artist.AddAlbum(album);
                album.Save();
            }
        }

AddAlbum Method

    public virtual void AddAlbum(Album.Album album)
    {
        album.Artist = this;
        AlbumList.Add(album);
    }

Save Method

    using (var session = NHibernateHelper.OpenSession())
    {
        using (var transaction = session.BeginTransaction())
        {
            session.SaveOrUpdate(x);

            transaction.Commit();
        }
    }

Error on session.SaveOrUpdate(x);

Error :

An exception of type 'NHibernate.Exceptions.GenericADOException' occurred in NHibernate.dll but was not handled in user code

Additional information: could not insert: [Entity.Album.Album][SQL: INSERT INTO [Album]

(AlbumName, WebSite, Year, ArtistId) VALUES (?, ?, ?, ?); select SCOPE_IDENTITY()]

AlbumMap

public class AlbumMap : ClassMap<Album>
{
    public AlbumMap()
    {
        Id(x => x.AlbumId);
        Map(x => x.AlbumName);
        Map(x => x.WebSite).CustomType<short>();
        Map(x => x.Year);
        HasMany(x => x.LyricList).Table("Lyric").KeyColumn("AlbumId").Cascade.All().Not.LazyLoad();
        References(x => x.Artist).Column("ArtistId").ForeignKey("ArtistId").Fetch.Join();
    }

}

ArtistMap

public class ArtistMap : ClassMap<Artist>
{
    public ArtistMap()
    {
        Id(x => x.ArtistId);
        Map(x => x.ArtistName);
        Map(x => x.ArtistURL);
        Map(x => x.ImgName);
        Map(x => x.ImgURL);
        Map(x => x.Alfabet);
        Map(x => x.SeoLink);
        Map(x => x.WebSite).CustomType<short>();
        HasMany(x => x.AlbumList).Table("Album").KeyColumn("ArtistId").Cascade.All().Not.LazyLoad();
    }

}

More Exception

The INSERT statement conflicted with the FOREIGN KEY constraint "FKA0CE20AA48AC4CAD". The conflict occurred in database "LYRICSWEB", table "dbo.Artist", column 'ArtistId'. The statement has been terminated.

Answer 1

There are some indications, which should lead to the answer. We can see that the error is:

Additional information: could not insert: [Entity.Album.Album][SQL: INSERT INTO [Album]
(AlbumName, WebSite, Year, ArtistId) VALUES (?, ?, ?, ?); select SCOPE_IDENTITY()]

while we are loading artist correct way...

Entity.Artist.Artist artist = Entity.Artist.Artist.READ
  .ById(Convert.ToInt32(ds2.Tables[0].Rows[i]["ArtistId"]));

The issue would be in incorrect collection mapping. The reference from Albumt to Artist seem to be correct:

public AlbumMap()
{
    ..
    References(x => x.Artist)
       .Column("ArtistId")
       .ForeignKey("ArtistId")
       .Fetch.Join();
}

But the other end of that relation is mapped like this:

// wrong
public ArtistMap()
{
    ..
    HasMany(x => x.AlbumList)
       .Table("Album")
       .KeyColumn("AlbumId")         // wrong
       .Cascade.All().Not.LazyLoad();
}

Both above relations are the same! They are only mapped from different ends (from collection item perspective and from collection owner perespective).

That means that the mapping column must be the same, i.e. not "AlbumId" but "ArtistId"

// correct
public ArtistMap()
{
    ..
    HasMany(x => x.AlbumList)
       .Table("Album")
       .KeyColumn("ArtistId")         // this is the relation
       .Cascade.All().Not.LazyLoad();
}

Because NHibernate loads some artist, and its collection of AlubmList.. it could lead to some unexpected insert statements... fix of that mapping should help...

Second thing to mention: Inverse()

We are working with Album instances... and we are assigning their relation to collection holder - Artist. This is good and suggested way how to do that. Good.

But we can profit from NHibernate feature called Inverse() which will reduce few SQL statements... and make all the insert more simple... so extend your Artist mapping like this:

// correct
public ArtistMap()
{
    ..
    HasMany(x => x.AlbumList)
       .Table("Album")
       .KeyColumn("ArtistId")  // this is the relation
       .Cascade.All()
       .Not.LazyLoad()
       .Inverse();             // ESSENTIAL improvement of generated SQL
}

Check this for more details