Visual studio C# Linq bulk insert/update

Question

Hi, I have recently developed a C# application using Linq. I am getting from an external database a list of profiles I need to process, some are new and some are already in the database, and need to be updated. What I do today is go over the profile list and check each profile if such exists I update otherwise I insert - this solution is working fine.

I am sure there is a way to use bulk insert/update something like UPDATE ON DUPLICATE, this way I can save time since the files I get are huge and bulk insert/update is known to have better performance. I would like to avoid the iteration I am now using.

insertall doesn't work for already stored rows, I need the combination of both update and insert

Here is my code, Your help is highly appreciated.

foreach (Profile tmpProfile in profiles)
            {
                try
                {                      
                    var matchedProfile = (from c in db.ProfileEntities
                                          where c.ProfileId == tmpProfile.Id
                                          select c).SingleOrDefault();

                    if (matchedProfile == null)
                    {
                        //Insert
                        db.ProfileEntities.InsertOnSubmit(EntityMapper.ToEntity(tmpProfile));

                    }
                    else
                    {
                        //Update
                        EntityMapper.ToEntity(ref matchedProfile, tmpProfile);                           

                    }                                               
                }
                catch (System.Data.SqlServerCe.SqlCeException sqlExec)
                {                       

                }
                catch (Exception e)
                {

                }
            }

            db.SubmitChanges();

Answer 1

One possible optimisation would be to create a list of all the items that you have from the external application, and then read all items from the database that match at once, instead of doing multiple round trips.

You can then update all of those, insert all of the ones that are left and call SubmitChanges at the end - you will then have 2 round trips to the database instead of one per profile retreived externally.

I don't know of any bulk update or insert features in Linq to SQL