Entity Framework: Duplicate Record Linq Queries


Some ancillary Linq queries to get duplicate record claims (same claim entered more than once) and to identify records where one is an update to another.

As previous: The app is a Blazor Service app using Entity Framework for access to the Azure Sql data. The Code-First approach is used where entities are defined as classes and added to the database context. The code behind is then updated using the add=migration command and then update-database command to update the database table/s. The app can handle club athletic records putting the onus onto athletes and their coaches to claim records when performed in state, national and international accredited events.

A claim is submitted by an athlete (or their coach) and a claim life cycle is:

  • Pending <– Has been submitted
  • Verified <– The date and performance details are correct
  • Rejected <– Not a better performance or details not verifiable
  • (Soft) Deleted
  • Recommended <– Verified and recommended to the committee
  • Current <– The current club record
  • Historical <– A previous club record

A claim can also be hard deleted. Where a claim that has been identified that is an exact duplicate of another (except for its Id) it needs to be soft deleted. This needs to be identified when in the pending state or later. At a much later stage, soft deleted claims are permanently (hard) deleted. A spurious claim once identified as such, will be hard deleted. When a recommended claim is elevated to current there is a need to identify the matching (except for the performance) current record that needs to made historical. So the target of the queries presented here are:

  • Duplicate Claims
    • Everything the same except the Id and perhaps, the submitter.
    • Duplicates to be soft deleted.
  • Clashes
    • Records for the same event, gender and age group that are in the recommended or current state. = Old records need to bumped in favour of the new record.
    • Note that this will also identify records that are different date and time but the same performance. This is where the same time, height or length was achieved for the same event, gender and age group, by different athletes on different dates.

Processing

One approach would be to identify duplicates at initial submission by the athlete (or coach) but the approach adopted here is to allow duplicates, which do give an email conformation to the athlete, and to remove them during the validation phase. Similarly current records could automatically be “bumped” to historical when a new record claim is accepted. Again the approach used here is to do this manually.

When viewing record claims in admin mode, there is an option to just view duplicates or to just view clashes. That way in Duplicates mode, the duplicates can be selected and their state changed to soft deleted. Similarly in Clashes mode, the “old” record can be “bumped? in favour of the new record.

The Code

Duplicates

        public async Task<List<RecordResult>> GetRecordDuplicates()
        {
            List<RecordResult> results = new List<RecordResult>();
            var allList = await _context.RecordResults.Include(record => record.ClubMember).Include(record => record.Event).ToListAsync();

            foreach (var RecordResult in allList)
            {
                foreach (var RecordResult2 in allList.Where(RecordResult2 => RecordResult2.Id != RecordResult.Id))
                {
                    if (
                        (RecordResult2.Event.Id == RecordResult.Event.Id) &&
                        (RecordResult2.Gender == RecordResult.Gender) &&
                        (RecordResult2.AgeGroup == RecordResult.AgeGroup) &&
                        (RecordResult2.Date == RecordResult.Date) &&
                        (RecordResult2.ResultView == RecordResult.ResultView) &&
                        (RecordResult2.First_Name == RecordResult.First_Name) &&
                        (RecordResult2.Last_Name == RecordResult.Last_Name) 
                    )
                    if (!results.Contains(RecordResult2))
                        results.Add(RecordResult2);
                }
            }

            return results;
        }

This performs a double iteration (inner and outer loop) over all records comparing them (except when the same record) and adds them if not already added. (This could be speed up by doing a triangular looping, where the inner loop start from after the current outer loop entity). The comparison is made on all properties that are germane to claim equality. Note that the claim state is not pivotal here. A pending and a recommended claim both with the same comparable properties would be identified.

Clashes

        public async Task<List<RecordResult>> GetRecordClashes()
        {
            List<RecordResult> results = new List<RecordResult>();
            var allList = await _context.RecordResults.Include(record => record.ClubMember).Include(record => record.Event).ToListAsync();
            var listOfRecommended = (from m in allList where m.Status == recordStatus.recommended select m).ToList();
            foreach (var RecordResult in listOfRecommended)
            {
            
                var ev = RecordResult.Event;
                var gender = RecordResult.Gender;
                var age = RecordResult.AgeGroup;
                var alist3 = (from m in allList where (m.AgeGroup == age) && (m.Gender == gender) && (m.Event.Id==ev.Id )&& (m.Status== recordStatus.current) select m).ToList();
                if (alist3.Count() != 0)
                {
                    results.Add(RecordResult);
                    results.AddRange(alist3);
                }
            }
            var listOfCurrent = (from m in allList where m.Status == recordStatus.current select m).ToList();
            foreach (var RecordResult in listOfCurrent)
            {
                var ev = RecordResult.Event;
                var gender = RecordResult.Gender;
                var age = RecordResult.AgeGroup;
                var alist3 = (from m in allList where (m.AgeGroup == age) && (m.Gender == gender) && (m.Event.Id == ev.Id) && (m.Status == recordStatus.current) select m).ToList();
                foreach (var RecordResult2 in alist3.Where (RecordResult2 => RecordResult2.Id != RecordResult.Id))
                {
                    if(!results.Contains(RecordResult2))
                        results.Add(RecordResult2);
                }
            }
            return results;
        }

This performs an iteration over all recommended claims and does a Linq query to find the same record (using comparable properties) amongst current records, collecting the result as a range. (There can be more than one). It then iterates over current records comparing to current. It then separately adds each result of that query to the collection if not already in the collection.

 TopicSubtopic
   
 This Category Links 
Category:Web Sites Index:Web Sites
  Next: > Club Record Certificate
<  Prev:   Club Record Certificate