Bulk inserting and updating with Entity Framework (Probably a better alternative?)
- by Dave
I have a data set of devices, addresses, and companies that I need to import into our database, with the catch that our database may already include a specific device/address/company that is included in the new data set. If that is the case, I need to update that entry with the new information in the data set, excluding addresses. We check if an exact copy of that address exists, otherwise we make a new entry.
My issue is that it is very slow to attempt to grab a device/company in EF and if it exist updated it, otherwise insert it. To fix this I tried to get all the companies, devices, and addresses and insert them into respective hashmaps, and check if the identifier of the new data exists in the hashmap. This hasn't led to any performance increases. I've included my code below. Typically I would do a batch insert, I'm not sure what I would do for a batch update though. Can someone advise a different route?
var context = ObjectContextHelper.CurrentObjectContext;
var oldDevices = context.Devices;
var companies = context.Companies;
var addresses = context.Addresses;
Dictionary<string, Company> companyMap = new Dictionary<string, Company>(StringComparer.OrdinalIgnoreCase);
Dictionary<string, Device> deviceMap = new Dictionary<string, Device>(StringComparer.OrdinalIgnoreCase);
Dictionary<string, Address> addressMap = new Dictionary<string, Address>(StringComparer.OrdinalIgnoreCase);
foreach (Company c in companies)
{
if (c.CompanyAccountID != null && !companyMap.ContainsKey(c.CompanyAccountID))
companyMap.Add(c.CompanyAccountID, c);
}
foreach (Device d in oldDevices)
{
if (d.SerialNumber != null && !deviceMap.ContainsKey(d.SerialNumber))
deviceMap.Add(d.SerialNumber, d);
}
foreach (Address a in addresses)
{
string identifier = GetAddressIdentifier(a);
if (!addressMap.ContainsKey(identifier))
addressMap.Add(identifier, a);
}
foreach (DeviceData.TabsDevice device in devices)
{
// update a device
Company tempCompany;
Address tempAddress;
Device currentDevice;
if (deviceMap.ContainsKey(device.SerialNumber)) //update a device
deviceMap.TryGetValue(device.SerialNumber, out currentDevice);
else // insert a new device
currentDevice = new Device();
currentDevice.SerialNumber = device.SerialNumber;
currentDevice.SerialNumberTABS = device.SerialNumberTabs;
currentDevice.Model = device.Model;
if (device.CustomerAccountID != null && device.CustomerAccountID != "")
{
companyMap.TryGetValue(device.CustomerAccountID, out tempCompany);
currentDevice.CustomerID = tempCompany.CompanyID;
currentDevice.CustomerName = tempCompany.CompanyName;
}
if (companyMap.TryGetValue(device.ServicingDealerAccountID, out tempCompany))
currentDevice.CompanyID = tempCompany.CompanyID;
currentDevice.StatusID = 1;
currentDevice.Retries = 0;
currentDevice.ControllerFamilyID = 1;
if (currentDevice.EWBFrontPanelMsgOption == null) // set the Panel option to the default if it isn't set already
currentDevice.EWBFrontPanelMsgOption = context.EWBFrontPanelMsgOptions.Where( i => i.OptionDescription.Contains("default")).Single();
// link the device to the existing address as long as it is actually an address
if (addressMap.TryGetValue(GetAddressIdentifier(device.address), out tempAddress))
{
if (GetAddressIdentifier(device.address) != "")
currentDevice.Address = tempAddress;
else
currentDevice.Address = null;
}
else // insert a new Address and link the device to it (if not null)
{
if (GetAddressIdentifier(device.address) == "")
currentDevice.Address = null;
else
{
tempAddress = new Address();
tempAddress.Address1 = device.address.Address1;
tempAddress.Address2 = device.address.Address2;
tempAddress.Address3 = device.address.Address3;
tempAddress.Address4 = device.address.Address4;
tempAddress.City = device.address.City;
tempAddress.Country = device.address.Country;
tempAddress.PostalCode = device.address.PostalCode;
tempAddress.State = device.address.State;
addresses.AddObject(tempAddress);
addressMap.Add(GetAddressIdentifier(tempAddress), tempAddress);
currentDevice.Address = tempAddress;
}
}
if (!deviceMap.ContainsKey(device.SerialNumber)) // if inserting, add to context
{
oldDevices.AddObject(currentDevice);
deviceMap.Add(device.SerialNumber, currentDevice);
}
}
context.SaveChanges();
