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

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 MemberType property. A member can be one or more of:

  • Athlete
  • Official
  • Coach

Whilst a Regex query could be used to not split commas within double quoted values I find that Regex is a bit of smoke and mirrors. My solution was to replace all double quotes with #COMMA#, do the CSV comma split then replace any #COMMA# in values with a comma when writing values to the database. These Csv values are handled bin the next section.


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, probably better **.


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.”
Ref See to solution for this app in a later post: “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 and 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;
  }

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