Blazor Helpers App Members: 2. Nuances with adding a Members Table


Some issues that arose when adding a Members table to an existing Blazor app when the data is provided as a CSV file.

This post has been significantly rewritten due to CSV code changes in teh app.

Nuances

The first issue was handling values that contain a comma. As is typical, these values are surrounded by double quotes. In this case this is used with Member_Type property. A member can be one or more of:

  • Athlete
  • Official
  • Coach

REGEX was the obvious methodology to implement this functionality, but I have a smoke and mirrors aversion to REGEX.

The first version of the solution used an excplicit method to handle this based upon finding one or none (assumption no more than one) substrings in each member’s CSV line delimeted by double quotes and replace any commas therein with a special string. Then when assigning this string value to the Member_Type property restore the comma in place of the special string. This worked OK but the coding needed to be tight and wasn’t scalabale.

A search for solutions indicated there was a Visaul Basic solution using Microsoft.VisualBasic.FileIO.TextFieldParser on StackOverflow: First answer. This requires the Microsoft.VisualBasic reference to be added to the solution. Whilst do-able, I was adverse to using this solution for esoteric reasons.

So the final solution ended up using REGEX in a suck-it-and-see-manner: Having found some suggested REGEX query strings to try on StackOverflow, one was found that worked OK: StackOverflow: 4th answer

  public const string CSV_REGEX = ",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))";

  System.Text.RegularExpressions.Regex CSVParser = new Regex(CSV_REGEX);
  values = CSVParser.Split(Lines[1]);

The only outcome issue was it left the double quotes on each end of the string. These are though easily removed with some C# string manipulations.

Another issue arose with respect to the MemberType field. It is written as a CSV list but we do require this to be parsed into a List of MemberTypes as a property of the class. Whilst this was initially implemented, EF-SQL ignores fields that are a list. Whilst there are alternative solutions to this, this is one way … **.

Update ** There is a better way using a many-to-many relationship:

“Now in Entity Framework Core 5.0, it will have full support for many-to-many relations without explicitly mapping the join table.” Reference
See to solution for this app in a later post: “5. Many to Many Entity Relationships”

The original solution here is:

  • Create a table of Groups with the MemberTypes as well as other custom groups such as TheCommittee with following properties:
    • Id
    • GroupName
    • IsCustom
  • For each member create a list of the Ids of the groups to which they belong
  • Serialise this list using NewtonSoft and save the text as a textual property.
  • When required, there is read-only property of the Member class that
    • Returns the list of Groups by first deserializing the text
    • Looks up Groups using the Ids from the DbContext.Groups entities.
  • It was found it was better to have an class method that explicitly did the serialisation for adding groups to the member rather than a writeable property that implicitly did the serialisation.
  public void AddGroupsFrom_Member_Type_Csv(List<MemberGroup> Groups, 
                              bool IsAddMemeberNotUpdateMember = true)
  {
    List<int> groups = new List<int>();

    string groupsCsv = this.Member_Type;
    if (!string.IsNullOrEmpty(groupsCsv))
    {
      string[] groupsArray = groupsCsv.Split(',',StringSplitOptions.RemoveEmptyEntries);
      foreach (var groupStr in groupsArray)
      {
        string groupStrTrimmed = groupStr.Trim();
        if (!string.IsNullOrEmpty(groupStrTrimmed))
        {
          int? groupId = (from g in Groups where g.GroupName.ToLower() 
            == groupStrTrimmed.ToLower() select g.Id).FirstOrDefault();
          if (groupId != null)
          {
            int id = (int)groupId;
            // Next should always be true but just checking in case.
            // Don't want same group more than once in list.
            if (!groups.Contains(id))
              groups.Add(id);
          }
        }
      }
    }
    this.MemberOfGroupsStr = JsonConvert.SerializeObject(groups);
  }

The code for generating the MemberofGroupsStr property from the Member_Type property in the Member class

There are some other generated group memberships in this method (not shown) such as Age Groups. Also, if this is called as part of an update then rather than creating a new list at the start, the original list is generated from the existing MemberofGroupsStr value with non-custom group Ids in the list are removed. This preserves previous assigned custom group memberships for the member.

Being only a serialized list of integers, the MemberOfGroupsStr property is easily edited in its raw form in situ.
The raw content is only a comma separated list of group Ids enclosed in square braces:
Edit Member MemberOfGroupsStr

This image is from the UI-dialog for directly editing a member’s properties. In this example, the Member is in the groups:

  • 2: All
  • 3: Athlete
  • 4: Coach
  • 5: 40+
  • 8: Committee

All except the last are auto-generated. Athlete and Coach come from the Member_Type property. Everyone is in the All group. 40+ is determined from the member’s DOB. The Committee is manually added.

This all worked fine locally using the same Azure SQL database as the published app, but when the app was published to Azure errors started to occur with some imports/updates with Data of Birth. It was realised the problem was one of System.Globalization.Cultureinfo being different on the development machine being different to the context that the app runs on Azure when DOB strings are parsed. The CSV DOBs are DD-MM-YYY. This was resolved by the app having a CultureInfo setting; in this case “en-AU” specifying this in the parsing.

  string  value ... //Read as DOB property as a string from the csv values
  DateTime dvalue = DateTime.Now;
  // https://docs.microsoft.com/en-us/dotnet/api/system.datetime.tryparse?view=net-5.0
  System.Globalization.CultureInfo cultureinfo = 
      new System.Globalization.CultureInfo(Settings.Locale);
  System.Globalization.DateTimeStyles styles = System.Globalization.DateTimeStyles.None;
  if (DateTime.TryParse(value, cultureinfo, styles, out dvalue))
  {
      isdatetime = true;
  }

  // Settings.Locale is "en-AU"

It is a requirement that when sent, members that already exist are not recreated but are updated. That way the database can be continuously updated from year to year without rebuilding. Members are identified based upon:

  • Last Name
  • First Name
  • DOB

When parsing a member’s line of values, once these three properties have been determined, a query is made to see if the member exists in the DbContext.Members. If it does then the AddNewMember server-side method calls out to the UpdateMember method and returns the result from there.

The parsing does not require all of the properties to be sent. The only requirements are:

  • The Header field names match the corresponding values in terms for the positions of commas in each.
  • The two name properties as well as the DOB must be provided
 TopicSubtopic
   
 This Category Links 
Category:Blazor Index:Blazor
  Next: > Blazor Helpers App members
<  Prev:   Blazor Helpers App Members