Search This Blog

Wednesday, August 6, 2014

Forcing Boolean Property Editor to work for a string DB column

The world is not perfect and sometimes we have to deal with legacy data, which is not well organized. For instance, data which is binary by nature can be stored using predefined strings ("T"/"F" or "Y"/"N"):

Correcting data is not often possible, because this data can already be used by other information systems.
As you know, XAF automatically generates editors for data fields in the UI based on the field type in the ORM data model (learn more...), so in this particular case an inappropriate editor (text box) will be used if we leave the default mapping to a string column "as is" - a text box instead of a check box or drop down box with the Yes/No values. In this blog post I will show you several methods on how to work around this situation and have the correct editor in the UI while keeping the underlying data table schema and data unchanged.

Update mapped string property via a calculated boolean one

The first solution I want to highlight is again coming from the fact that XAF automatically provides editors depending on the field type in the ORM data model.  So, if we want to have a BooleanPropertyEditor in the UI, we define a System.Boolean property in our data model. Since we do not want a boolean value to be actually stored, we make this property non-persistent/not mapped. It is also obvious that the value of this calculated property must depend on the stored string value, for which a string persistent property can be declared in our data model.

Here is some example code for what I have just described above using XPO for data access:
    public class Answer: XPLiteObject {
        public Answer(Session session) : base(session) { }
        public override void AfterConstruction() {
            AnswerStored = "F";
        [Persistent("Answer"), Size(1)]
        protected string AnswerStored {
            get { return GetPropertyValue<string>("AnswerStored"); }
            set { SetPropertyValue<string>("AnswerStored", value); }
        [PersistentAlias("iif(AnswerStored=='T', true, false)"), XafDisplayName("Answer")]
        [CaptionsForBoolValues("Yes", "No")]
        public bool AnswerForUI {
            get { return AnswerStored == "T"; }
            set { AnswerStored = value ? "T" : "F"; }

If you use Entity Framework, then the code will be very similar, e.g., check this example from StackOverFlow.

Some comments on the code above:
- I declared the AnswerStored property as "protected" to automatically hide it from the business model and consequently the UI. Alternatively, I could decorate it with the Browsable(false) attribute.
- The Persistent and Size attributes are used for the AnswerStored property to specify its persistence settings such as mapped column name and size.
- PersistentAliasAttribute is used for the AnswerForUI property to make it usable for server-side filtering: the alias expression depends on the AnswerStored column, which is physically present in the database and thus can be used in SQL queries.
- CaptuibsForBoolValuesAttribute is used for the AnswerForUI property to instruct the BooleanPropertyEditor to use a drop down box with the Yes/No items (they, of course, can be localized) instead of the default check box.
- XafDisplayNameAttribute is used to provide a different name for the property in the UI.
- A persistent property is updated every time our calculated AnswerForUI property is changed.

If you run the application, you will see the following UI, which fully meets our needs:

While this solution can be used for both supported ORMs (XPO and EF), a pair of such properties needs to be implemented for each case where you have to deal with such legacy data. This does not look bad after all, because such cases are exceptions rather than rules, which will force you to eventually come up with a better database design.
This approach is also interesting because using it, you can use standard XAF editors for viewing or editing of the properties of custom and complex types for which there is no built-in PropertyEditor by default. Check out these examples (one, two) for more inspiration.

Value conversion at the ORM level (XPO only)

Specially for more convenience and to avoid creating a pair or persistent/calculated properties for each such case, XPO provides a Value Converters mechanism that instructs the ORM to smoothly convert a property of one type to a different storage type. Using this mechanism our previous code can be shortened as follows:

        [Size(1), ValueConverter(typeof(BooleanToStringValueConverter))]

        [CaptionsForBoolValues("Yes", "No")]
        public bool Answer {
            get { return GetPropertyValue<bool>("Answer"); }
            set { SetPropertyValue<bool>("Answer", value); }
   public class BooleanToStringValueConverter : ValueConverter {
        public override object ConvertFromStorageType(object value) {
            return Convert.ToString(value) == "T";
        public override object ConvertToStorageType(object value) {
            return Convert.ToBoolean(value) ? "T" : "F";
        public override System.Type StorageType {
            get { return typeof(string); }

Value converters can also be globally registered for a certain type via the XPDictionary.RegisterValueConverter method not to decorate each property with the ValueConverter attribute.

Entity Framework does not yet provides a similar mechanism, but there is a corresponding feature request on

Other solutions

I cannot help mentioning a solution, which essentially performs the same value conversion at the UI level. That is implementing a custom XAF PropertyEditor for a string property based on a check box or drop down box controls. Take special note that while it is possible, it is not that universal and platform-agnostic, because you will have to create a custom editor for Windows and the Web, which doubles your work in a general case.

No comments:

Post a Comment