How can I migrate a temp table using LINQ to SQL?

I have two tables: contacts and contact_temps. The contact_temps table mirrors the contacts table. What I'm trying to do is simply pull records from the temp table and insert them into contacts. Afterwards I will remove those records from the contact_temps table.

The code below only migrates one record and doesn't delete anything from the temp table. How can I fix my issue? Thanks.

            // migrate temp profile(s)...
            var tempProfilesToMigrate = from ct in db.contact_temp开发者_JAVA百科s
                                         where ct.SessionKey == contact.Profile.SessionId
                                         select new contact();



            db.contacts.InsertAllOnSubmit(tempProfilesToMigrate);
            db.SubmitChanges();

            //...clear temp table records
            var tempProfilesToDelete = from ct in db.contact_temps
                                        where ct.SessionKey == contact.Profile.SessionId
                                        select ct;

            db.contact_temps.DeleteAllOnSubmit(tempProfilesToDelete);
            db.SubmitChanges();

---

I wonder if your "Insert All on Submit" is causing the entities to become associated with db.contacts. Try this.

// migrate temp profile(s)...
var tempProfiles = from ct in db.contact_temps
                             where ct.SessionKey == contact.Profile.SessionId
                             select ct;

foreach (var c in tempProfiles)
{
    Contact newC = new Contact();
    newC.Name = c.Name;
    // copy other values

    db.contacts.InsertOnSubmit(newC);
}

// WAIT! do it at once in a single TX => avoid db.SubmitChanges() here.

 db.contact_temps.DeleteAllOnSubmit(tempProfiles);

 // Both sets of changes in one Tx.
 db.SubmitChanges();

You can also write a stored-proc and import it into the db context and just call it.

---

var result = db.ExecuteCommand("insert into contacts select * from contacts_temp where SessionKey={0}",contact.Profile.SessionId);

Of course, that's just off the top of my head, but you get the idea. Even better would be to put the migration and deletion into a stored procedure. The method you're using will round trip all the contact_temp records twice (once back and forth for the insert, once for the delete).

P.S. google "code first stored procedures" for a way to call stored procs using EF 4.1