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();
how to bulk insert/update using linq C#
If you want to use only LINQ to SQL, you should call the InsertAllOnSubmit method to attach the records to the context
and then the SubmitChanges to execute SQL commands.
Another option is to use the Loader component to insert the records to database and then run the
DataContext.Attach(entity, false) method to attach the inserted records to the context.
and then the SubmitChanges to execute SQL commands.
Another option is to use the Loader component to insert the records to database and then run the
DataContext.Attach(entity, false) method to attach the inserted records to the context.