I have code that checks to see if a record exists, if it does not it inserts it. The problem is it fails at times. It is pulling from a message queue. I have done everything I can think of but it is somehow creating the record on another 'thread' (I am assuming) sometime after it checks and before it creates the new one.
The error is in the try / catch loop -- this works but I want to know how to avoid it from happening in the first place. What am I doing wrong, or what could I do better?
The code starts off like this:
public class Functions
{
// This function will get triggered/executed when a new message is written
// on an Azure Queue called queue.
public static void ProcessQueueMessage([QueueTrigger("stops-to-import-to-mobile")] string message, TextWriter log)
{
rest of the code here...then the trouble starts
mobileEntities mdbPcs = new mobileEntities(); //get a new context for the below
//now go create all the pcs for this stop.
var stopPieces = (from sp in db.Stop_Items where sp.stop_detail_id == stop.Id select sp).ToArray();
//get the count of the current pcs for the enxt stop
int mobilePcCount = (from s in mdbPcs.mobile_Item_Detail where s.mobile_stops_id == mstopId select s.Id).Count();
if (mobilePcCount != stopPieces.Count()) //if the piece count is the same already then no need to go through the loop
{
foreach (var item in stopPieces)//step through the items one at a time
{
int seek = (from s in mdbPcs.mobile_Item_Detail
where s.mobile_stops_id == mstopId &&
s.unique_scan_code == item.item_detail.unique_scan_code
select s.Id).FirstOrDefault();
if (seek == 0) //if we do not already have the item create it
{
mobile_Item_Detail newItem = new mobile_Item_Detail();
newItem.item_description = item.item_detail.item_description;
newItem.LOB_item_detail_id = item.item_detail.Id;
newItem.mobile_stops_id = mstopId;
newItem.dt_seq_no = item.item_detail.dt_item_seq_no;
newItem.unique_scan_code = item.item_detail.unique_scan_code;
mdbPcs.mobile_Item_Detail.Add(newItem);
try
{
mdbPcs.SaveChanges();
}
catch (Exception ex)
{
if (ex.InnerException.InnerException.Message.Contains("UNIQUE KEY")) //WTH -- Why does this keep happening...how do I fix this??!
{
Console.WriteLine($"{DateTime.Now}Unique Contraint {message} {newItem.unique_scan_code} for stop {newItem.mobile_stops_id}");
//item was already created by another thread so continue the foreach loop (I guess?!)
continue;
}
throw;
}
}
}
}
I'm assuming mobile_Item_Detail has an Id property that represents the identity PK and mstopid is the FK relationship to the parent. In that case, I would make all the changes the entities collection and then call save changes outside the foreach. This way, the DbContext can handle all the Ids at once because its aware of all changes via ChangeTracker and the save can be executed within in a single db call. You could also wrap the operation within a transaction to easily rollback the changes should an error occur. I would also enclose the operation in a using statement to ensure the connection the data source closed (see here).
using (var mdbPcs = new mobileEntities ()) //get a new context for the below
{
//now go create all the pcs for this stop.
var stopPieces = (from sp in db.Stop_Items where sp.stop_detail_id == stop.Id select sp).ToArray ();
//get the count of the current pcs for the enxt stop
var mobilePcQuery = (from s in mdbPcs.mobile_Item_Detail where s.mobile_stops_id == mstopId select s.Id);
int mobilePcCount = mobilePcQuery.Count ();
if (mobilePcCount != stopPieces.Count ()) //if the piece count is the same already then no need to go through the loop
{
try
{
foreach (var item in stopPieces) //step through the items one at a time
{
int seek = mobilePcQuery.Where(s => s.unique_scan_code == item.item_detail.unique_scan_code select s.Id).FirstOrDefault ();
if (seek == 0) //if we do not already have the item create it
{
mobile_Item_Detail newItem = new mobile_Item_Detail ();
newItem.item_description = item.item_detail.item_description;
newItem.LOB_item_detail_id = item.item_detail.Id;
newItem.mobile_stops_id = mstopId;
newItem.dt_seq_no = item.item_detail.dt_item_seq_no;
newItem.unique_scan_code = item.item_detail.unique_scan_code;
mdbPcs.mobile_Item_Detail.Add (newItem);
}
}
mdbPcs.SaveChanges ();
} catch (Exception ex) {
if (ex.InnerException.InnerException.Message.Contains ("UNIQUE KEY")) //WTH -- Why does this keep happening...how do I fix this??!
{
Console.WriteLine ($"{DateTime.Now}Unique Contraint {message} {newItem.unique_scan_code} for stop {newItem.mobile_stops_id}");
//item was already created by another thread so continue the foreach loop (I guess?!)
continue;
}
throw;
}
}
}
Let me know if this helps.