Duplicate record issue when using Entity Framework and concurrent connections

Question

var DoesNotExist = this.db.Regions.Where(!m.Reports.Any(r => r.ReportId == m.ReportId)).ToList();

foreach (var item in DoesNotExist)
                        {
                            Report rpt = new Report { ReportId = item.ReportId, ReportDate = endDate };
                            this.db.Reports.AddObject(rpt);

                            foreach (var Point in this.db.RegionalData.Where(s => s.ReportId == item.ReportId && s.ReportDate == lastendDate))
                            {
                                if (this.db.RegionalData.Where(d => d.ReportId == Point.ReportId).Count() == 0)
                                {
                                    StateData sd = new StateData
                                    {
                                        ReportDate = endDate,
                                        ReportId = Point.ReportId,
                                        Female = Point.Female,
                                        Male = Point.Male,
                                        Total = Point.Total,
                                       };
                                    this.db.RegionalData.AddObject(sd);
                                }
                            }
                        }

                        if (DoesNotExist.Count() != 0)
                        {
                            this.db.SaveChanges();
                        }

I have the above code that checks if reports for a Region do not exist, and if they do not exist i insert them.

The problem i'm having is that if two users trigger the execution of this code a few seconds apart, two reports for the same region are created which creates duplicates since a region can only have one report.

How best can i resolve this?

I tried calling putting if (missingReportData.Count() != 0) { this.db.SaveChanges(); }

immediately after this.db.Reports.AddObject(rpt); but still the issue persisted.

Answer 1

I would solve this by adding a unique index that prevents you from inserting two reports for the same region. Without seeing your model it's hard to say if it should be the primary key or not.

This way database server will throw an exception if there is already a report for that region. Then you can handle that exception by switching to an update or show an error to the user.

Answer 2

You can also use a transaction but you need to update EF4 to EF6.