What's the best way to copy and insert data from related tables into a new set of related tables with Linq

Question

I work on a web game , I use asp.net mvc, linqtosql.

I have 3 tables that store default game values: DefaultRegions, DefaultSubRegions and DefaultCountries. These 3 tables are related to each other thanks to primary / foreign keys.

I need to copy the values of these tables into 3 tables called Regions, SubRegions and Countries when someone creates a new Game. In these 3 tables the values for the ongoing games are saved (GameId is added to differentiate the games).

Now, I need to update the relationships, since the primary keys obviously change.

Anyone knows a way to achieve this in an efficient manner?

My idea would involve cumbersome foreach loops, maybe someone else has an idea?

edit looks like my initial idea is the best way to go, thanks

Answer 1

Try this, it should create and interrelate all the new auto generated ID's on submit.

var defaultRegions = from r in DefaultRegions
             select r;

IList<Region> rlist = new List<Region>();
foreach(var dr in defaultRegions) {
    var r = new Region {
        Name = dr.Name };

    foreach(DefaultSubRegion dsr in dr.DefaultSubRegions) {
        var sr = new SubRegion {
            Name = dsr.Name };

        foreach(DefaultCountries dc in dsf.DefaultCountries) {
            var c = new Country {
                Name = dc.Name };

            sr.Countries.Add(c);
        }

        r.SubRegions.Add(sr);
   }

   rlist.Add(r);
}

DataContext dc = new DataContext();
dc.Regions.InsertAllOnSubmit(rlist);
dc.Submit();

This should work for you, and it is the easiest way I have found to copy data.