A simple PIVOT in c# built on the ExpandoObject

In many applications you have to make the decision whether to add numerous analysis fields onto an entity (effectively limiting the number and type available) or use a couple of extra tables, one for the analysis field details and one for the contents of these fields.

For the Nominal Accounts in ossAccounts I have chosen the latter which gives anyone taking on the system far more flexibility but with a little more complexity (mostly for me since they will have a simplified API to work with in the end).

This is fine for general processing but there are times when you wish to display all of the additional categories as though they are part of the NominalAccount, for example a list of accounts.  After all this is how the user envisages the data.

In the past I have made extensive use of Visual Foxpro and could create pivots reasonable easily.  However it appears that doing so in c# is a completely different kettle of fish even if the team behind VFP created linq.  There appears to be very few solutions and they tend to be based on the properties of DataTables not the IEnumerables that I am using. Casting backwards and forwards to DataTables is do-able but not great.

My solution is a collection of ExpandoObjects that have had all the relevant properties added.  It isn't going to win any efficiency awards but for relatively small datasets is ok.

Cut down versions of the Entity classes...

public class NominalAccount : BaseEntity
{
    [Key]
    public int NomincalAccountID { get; set; }

    [...]
}

public class NominalCategory : BaseEntity
{
    [Key]
    public int NominalCategoryID { get; set; }

    [Required]
    [StringLength(30)]
    public string Description { get; set; }

    [...]
}

public class NominalCodeCategoryValue : BaseEntity
{
    [Key]
    public int NominalCodeCategoryValueID { get; set; }

    [Required]
    [StringLength(30)]
    public string CategoryValue { get; set; }

    [...]
}

   
internal Dictionary CreateExtendedNominalAccountObjects(List nominalCategories, List nominalAccounts, List nominalCategoryValues)
{
   // Create object based on Categories
   // index will be NominalAccountID
   var extendedNominalAccounts = new Dictionary();

   dynamic nominalObject = new ExpandoObject();

   // Add all the categories to the ExpandoObject
   foreach (var nominalCategory in nominalCategories)
   {
       ((ExpandoObject)nominalObject).AddProperty(nominalCategory.Description, "");
   }

   // Create dataset for nominals
   foreach (var nominalAccount in nominalAccounts)
   {
      var neo = ExpandoObjectExtensions.CloneExpando(nominalObject);

      ((ExpandoObject)neo).AddProperties(nominalAccount);

      extendedNominalAccounts.Add(nominalAccount.NomincalAccountID, neo);
   }

   // Loop through the Nominal Category details and update the appropriate nominal ExpandoObject

   foreach (var ncv in nominalCategoryValues)
   {
      var nomObj = extendedNominalAccounts[ncv.NominalAccountID];

      var categoryDesc = (from nc in nominalCategories
                    where nc.NominalCategoryID == ncv.NominalCategoryID
                    select new { nc.Description }).Single();

      nomObj.UpdateProperty(categoryDesc.Description, ncv.CategoryValue);

      extendedNominalAccounts[ncv.NominalAccountID] = nomObj;
    }

     return extendedNominalAccounts;
 }

The AddProperties, UpdateProperties extensions...

   public static class ExpandoObjectExtensions
    {
        public static void AddProperty(this ExpandoObject expando, string propertyName, object propertyValue)
        {
            // ExpandoObject supports IDictionary so we can extend it like this
            var expandoDict = expando as IDictionary;
            if (expandoDict.ContainsKey(propertyName))
                expandoDict[propertyName] = propertyValue;
            else
                expandoDict.Add(propertyName, propertyValue);
        }

        public static void UpdateProperty(this ExpandoObject expando, string propertyName, object propertyValue)
        {
            // ExpandoObject supports IDictionary so we can extend it like this
            var expandoDict = expando as IDictionary;
            if (expandoDict.ContainsKey(propertyName))
                expandoDict[propertyName] = propertyValue;
            else
                throw new SettingsPropertyNotFoundException("Property "+propertyName.Trim()+" missing.");
        }

        public static void AddProperties(this ExpandoObject expando, T obj)
        {
            var props = obj.GetType().GetProperties();

            foreach (var propertyInfo in props)
            {
                expando.AddProperty(propertyInfo.Name,propertyInfo.GetValue(obj));
            }
        }

        /// 

        ///  Creates a clone of an ExpandoObject
        ///  https://stackoverflow.com/q/22826610/82536
        /// 
        /// The object to clone
        /// A clone of the given ExpandoObject.
        public static ExpandoObject CloneExpando(this ExpandoObject expando)
        {
            var newExpando = new ExpandoObject();

            foreach (var kvp in (IDictionary)expando)
            {
                ((IDictionary)newExpando).Add(kvp);
            }

            return newExpando;
        }
    }

Fully tailorable form text (wpf)

How do you get fully tailorable text on a window using wpf (c#,xaml)?

Painfully!

There are functions built into the system by MS that are designed to deal with localisation problems but this is at the dll level.  What I need for ossAccounts is a simple method for the vertical market developer (or systems admin) to be able to edit a table and change any field, either to allow additional languages or just tailoring the solution.  For example some accounting systems use 'Nominal Ledger', others 'General Ledger' etc.

It is particularly problematic when working with datagrids since the headers can not be simply bound.

The following is using a modified version of the GenericCRUDControl by Abdulrahman Emad and  Muhammad Magdi (found here ) but should transfer to other grids easy enough.  While there may be other approaches (if someone knows a better one, please tell!) it does work.

<CustomDataGridColumn BindingExpression="Name" Width="Auto">
       <CustomDataGridColumn.Header>                
                <TextBlock Text="{Binding DataContext.F[Name], 
                                                     RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type UserControl}},
                                                     Mode=OneWay}"/>                
        </CustomDataGridColumn.Header>
</CustomDataGridColumn>

Where F[Name] is :-

public Dictionary F { get; set; } = new LocalisationDictionary();

on the DataContext class (in the above on the CRUDWorkspaceViewModel). Note that the way I am handling multiple windows means that the ancestor I need for the DataContext is a UserControl, yours may be different.

LocalisationDictionary then returns the appropriate text based on the string it receives, the users language etc.

public class LocalisationDictionary : Dictionary

{

     public new string this[string key] => "Foo_"+key; // Some method here.

}