Jekyll: Blog Site Updates - Grouping Query


Linq and SQL Queries can generate quite complex data queries. But you have to dot the Is and cross the Ts before they will work. When is it conceptually simpler (and quicker) to just do a manual search with a couple of for loops?

This site creation is documented in this Category page. There have been another update as documented here.

Situation

The Blazor app permits athletes to submit a claim by themselves for a club record in an event. A coach can also submit one on behalf of an athlete. A claim goes through various states leading up to the “current” state which means it is the current club record. When a claim is bumped up to this state there is the issue of making the previous current record “history”. A query was required to get all claim records for the same event-gender-age group where there is more than one, so that the earlier one can be made history.

Claim States

  • Pending, just been submitted by athlete/coach
  • Verified, the meta data is correct (but no comparison to current)
  • Recommended. to the committee as it is better than the current
  • Current, is the current record (committee approved)
  • History, was a previous club record
  • Rejected, didn’t pass the verified step, (may need some edits)
  • Deleted, was soft deleted. Note that a spurious or duplicate claim can be “hard” deleted.

Note that the initial claim is a multi-step process:

  • Athlete (and their gender, age group), the event and event age group selected.
    • If athlete entry, athlete info gleaned from login
    • Athlete’s age group must be less than or equal to event age group
      • Overage athletes can also claim open records
    • Performance entered
    • If better performance:
  • Meta information entered
    • Date of event
    • Meet type
    • Location
    • Other info
    • Link to results ( is auto-entered for state comps)
    • If required info entered:
  • Invited to submit
    • Entry becomes Pending

The Grouping Query

Need to search All records for where there is more than one record with the same property values for certain fields. Could do a SQL or Linq query looking for partial matches on field properties. Most examples seem to focus upon matches where all properties match. Note one property that won’t match in this case is the Id, of course. Whilst I guess there are some esoteric solutions to this in Linq or SQL, I experimented with those with no success. I found it quick and dirty to just code it in C# with an inner and outer loop both looping through all records and making a comparison between the inner and out loop record based upon the specific fields.

Whilst foreach looping was initially envisaged, numerical for loops were used (records accessed using indexes) so that there was some relative order with the outer loop record with respect to the inner loop record. The dual looping is triangular in that the inner loop is from the next record after the outer loop record. (The reason for the indexed looping) When there is a match, both records are tagged and future loops ignore the records so tagged. They are also added to the list of records to be displayed.

The Comparison Fields

  • EventDescriptionView: Whilst there are a number of fields for each event (In/Out of Stadium, Track/Field etc) there is a generated (readonly) field that integrates these into a string which is unique for each event. This is used is used in various tables and documentation generated by the app (eg emails).
  • Gender
  • Age Group

The Query

    async Task ShowMulti()
    {
        // Get all records from the database
        var records1 = await service.GetRecordResults();

        // Get current records
        IEnumerable<RecordResult> records2;
        records2 = from r in records1
                   where
         (r.Status == recordStatus.current) 
                   select r;

        // Get them sorted, overall by Id
        records = records2
        .OrderBy(s => s.Status)
        .ThenBy(s => s.Event.InStadia)
        .ThenBy(s => s.Event.EventType)
        .ThenBy(s => s.Event.TrackType)
        .ThenBy(s => s.Event.EventDistance)
        .ThenBy(s => s.Event.EventDescriptionView)
        .ThenBy(s => s.Gender)
        .ThenBy(s => s.AgeGroupView)
        .ThenBy(s => s.EventDate)
        .ThenBy(s => s.Id)
        .ToList();

        // A list of all records/clasims for which there is more than one record
        List<RecordResult> Multis = new List<RecordResult>();

        // A flag for each record claim that if true means it has been selected for the list
        // Once selected for the list it is ignored.
        bool[] recFlag = Enumerable.Repeat(false, records.Count()).ToArray<bool>();

        // A "triangular" double for loop through records against records for comparison
        for (int i=0; i< records.Count();i++)
        {
            if (recFlag[i])
                continue;
            for(int j = i+1; j < records.Count(); j++)
            {
                if (recFlag[j])
                    continue;

                // Attempt to match certain properties in both claims
                if (records[i].EventDescriptionView==records[j].EventDescriptionView)
                {
                    if (records[i].Gender == records[j].Gender)
                    {
                        if (records[i].AgeGroup == records[j].AgeGroup)
                        {
                            // Matched
                            if (!recFlag[i])
                            {
                                // Only add the outer loop claim once
                                recFlag[i] = true;
                                Multis.Add(records[i]);
                            }
                            // Add the inner loop claim
                            recFlag[j] = true;
                            Multis.Add(records[j]);

                        }
                    }
                }
            }
        }
        records = new List<RecordResult>(Multis);
    }

The Query

Outcome

This is “quick-and-dirty” in that it was ultimately simple to code and runs quite quickly … fraction of a second over 1000 records. It does deliver the required matching records so the previous current record is easily identified and can be bumped to the historic state. One complication to resolve is that there are some older records where there were already 2 because they have equal performances. There are also some paired records that are slightly different on performance because one is electronic timed and the other is hand-held. There is a formula in athletics for comparing these. Need to add some way of tagging these so that they can be de-identified in the query but resurrected when there is a new claim for a record in the corresponding event. (2Do).

Footnotes

Note that database is Azure SQL and the app interface is via Entity Framework. For most processing all records are loaded into memory; there is less than 1000.

The 2DO was implemented as follow:

  • A PlzIgnore boolean property was added with a default value of false;
  • The paired records have this property set to false.
  • The sort has a final PlzIgnore sort:
        .ThenBy(s => s.Id)
        .ThenBy(s => s.PlzIgnore)
        .ToList();
  • After a comparison match, a further check is added:
                if (records[i].EventDescriptionView==records[j].EventDescriptionView)
                {
                    if (records[i].Gender == records[j].Gender)
                    {
                        if (records[i].AgeGroup == records[j].AgeGroup)
                        {
                            if (!(records[i].PlzIgnore && records[j].PlzIgnore)) // <- Added>
                            {                                                    // <- Added>
                               // Matched
                                if (!recFlag[i])
                                {
                                    // Only add the outer loop claim once
                                    recFlag[i] = true;

                                    ...

That way a new record claim will be compared to both ignored existing records before the existing ones are compared to each other. And so, for a new record where there is an existing ignored set of “ignored” records for the same event-gender-age group, it will show these ignored records in the generated list (and only then).

Further

The algorithm can be further improved. The outer loop will iterate through all of the records that have PlzIgnore false, then those with it true. Given the triangular nature of the inner outer and inner loops, as the inner loop is from the next record after the inner loop index, when the outer loop gets to PlzIgnore = true records, the comparisons can stop as the comparisons will be between records that both have PzIgnore true. Any records that match on those three properties (as listed above) will be groups of records for the same event-gender-age group and therefore the same (or similar) performance and so can be ignore (not added to the list).

        for (int i=0; i< records.Count();i++)
        {
            if (records[i].PlzIgnore) // <- Added 
                break;

The modified outer loop

 TopicSubtopic
  Next: > Aged Care Independent Living
<  Prev:   RPI-Pico-Arduino-AzSDK
   
 This Category Links 
Category:Web Sites Index:Web Sites
  Next: > Entity Framework Group Summing
<  Prev:   Jekyll