CODEWITHSUNDEEP
c#ormlite-servicestack
coffekid
coffekid

Reputation: 605

Best approach to insert on many to many tables using Identity columns on ServiceStack ORMLite

Yesterday I found this great ORM and would like to perform some testings on more complex stuff than the samples provided on github.

This is the model I'm using

class BaseClass
{
    [AutoIncrement]
    public int Id { get; set; }

    [StringLength(200)]
    public string Name { get; set; }
}

[Alias("artist")]
class Artist : BaseClass
{

}

[Alias("genre")]
class Genre : BaseClass
{

}

[Alias("artistgenre")]
class ArtistGenre
{
    [PrimaryKey]
    public int Id { get; set; }

    [Alias("idgenre")]
    [References(typeof(Genre))]
    public int IdGenre { get; set; }

    [Alias("idartist")]
    [References(typeof(Artist))]
    public int IdArtist { get; set; }
}

And this is what I'm trying to achieve (although it doesn't work since the Identity values are unknown when inserting into artistgenre table).

                if (dbConn.TableExists("artistgenre"))
                    dbConn.DropTable<ArtistGenre>();

                if (dbConn.TableExists("artist"))
                    dbConn.DropTable<Artist>();

                if (dbConn.TableExists("genre"))
                    dbConn.DropTable<Genre>();

                dbConn.CreateTables(false, typeof(Artist), typeof(Genre), typeof(ArtistGenre));

                var genres = new List<Genre>();
                genres.Add(new Genre() { Name = "Rock" });
                genres.Add(new Genre() { Name = "Pop" });
                genres.Add(new Genre() { Name = "Jazz" });
                genres.Add(new Genre() { Name = "Classic" });
                genres.Add(new Genre() { Name = "Bossanova" });

                var artists = new List<Artist>();
                artists.Add(new Artist() { Name = "Rush" });
                artists.Add(new Artist() { Name = "Queen" });
                artists.Add(new Artist() { Name = "Pat Metheny" });

                var ag = new List<ArtistGenre>();
                var a = artists.FirstOrDefault(c => c.Name == "Rush");
                var g = genres.FirstOrDefault(c => c.Name == "Rock");
                ag.Add(new ArtistGenre() { IdArtist = a.Id, IdGenre = g.Id });

                dbConn.SaveAll<Artist>(artists);
                dbConn.SaveAll<Genre>(genres);
                dbConn.SaveAll<ArtistGenre>(ag);

Is there a simple solution other than adding each row and obtaining its Identity values?

Thanks.

Upvotes: 1

Views: 643

Answers (1)

mythz
mythz

Reputation: 143319

OrmLite doesn't currently populate the model with the auto-incrementing id atm. The way you obtain the autoincrement Id is to use db.GetLastInsertId() after each insert, e.g:

artists.ForEach(artist => {
  db.Insert(artist);
  artist.Id = db.GetLastInsertId();
});

I recommend wrapping this in an extension method to make this nicer to work with.

Upvotes: 2

Related Questions