Mapping Database Types to Domain Types

Many legacy databases have their own ways of storing data – some systematic conventions, some ad hoc.  Often, these storage formats aren’t the way you want to represent the data in the domain model.

For example, many databases without a Boolean type tend to store Boolean values using single‑character strings, “Y” for true and “N” for false.  You would not want to surface these columns as strings in the domain model, because that would be confusing to users of the domain model, and could lead to errors if users tried to enter arbitrary strings.  Rather, you would prefer to surface these columns as Boolean fields, and translate between the database storage format and the domain type within the entity.

There are two ways of doing this in LightSpeed: custom wrappers, and field converters.

Custom Wrappers

As previously noted, LightSpeed maps .NET fields to database columns, and ignores .NET properties.  This means you are free to define wrapper properties or methods with different types from the underlying field.  These wrappers can convert between the domain type, which they present in the public interface of the entity, and the database representation, which is encapsulated in the private fields.

To implement this, select the field you want to wrap and set its Generation option to FieldOnly.  Then open or create a partial class file for the entity, and implement the wrapper property or methods yourself.

partial class Member
{
  // _isModerator string field is in designer generated code
  public bool IsModerator
  {
    get { return _isModerator == "Y"; }
    set
    {
      string dbIsModerator = value ? "Y" : "N";
      Set(ref _isModerator, dbIsModerator, "IsModerator");
    }
  }
}

Remember to use the Entity.Set method to set the value of persistent fields.

A crucial pitfall of this approach is that the translation logic is not applied to queries.  Users must write queries in terms of the underlying database format, not the domain format.  This is confusing when using the query objects syntax, and is not possible at all in LINQ:

Entity.Attribute("IsModerator") == "Y" // works
Entity.Attribute("IsModerator") == true // fails
from m uow.Members where m.IsModerator == "Y" // compiler error, can’t compare bool and string
select m

Therefore, if you use a custom wrapper, and anticipate users wanting to query on the wrapped field, be sure to encapsulate all querying logic behind a repository API which can perform the necessary translations.

Field Converters

A field converter encapsulates the translation between database and domain as a reusable object.  This is useful for two reasons: first, it allows that translation to be applied to queries; and second, it allows the same translation to be applied to many different fields without needing to write a custom wrapper for each.  There are also specialised cases which are problematic for custom mapping, such as LightSpeed 3‑style data transfer objects, or TimeSpan fields where LightSpeed’s built-in handling may clash with a desired database-specific mapping.

To implement a field converter, implement the IFieldConverter interface, or derive from the FieldConverter<TDatabase, TModel> base class.

public class YesNoConverter : FieldConverter<string, bool>
{
  protected override bool ConvertFromDatabase(string databaseValue)
  {
    return "Y".Equals(databaseValue, StringComparison.OrdinalIgnoreCase);
  }
  protected override string ConvertToDatabase(bool value)
  {
    return value ? "Y" : "N";
  }
}

The LightSpeed designer represents columns requiring conversion using a user‑defined type.  For example, you might represent the Y/N Boolean idiom as a YesNo type.  This saves you applying the converter separately to each field that needs it – instead, the user‑defined type bundles up the domain type, the database type and the mapping between them.  Note that the user‑defined type will still surface in the domain model as (in this case) a Boolean.

To create a user‑defined type that represents a custom format, open the LightSpeed Model Explorer, right-click the root model node, choose Add New User Defined Type, and apply the following settings:

·         Name: The name to appear in the Data Type drop-down, e.g. YesNo.

·         CLR Type Name: How this type should be surfaced in the domain model, e.g. System.Boolean.  This should be a namespace-qualified CLR type name (see Enums and Other User‑Defined Types in the chapter Working with Models in the Visual Designer for more information).

·         Is Value Type: Set this to True or False depending on whether the CLR type is a struct or a class.

·         Data Type: How this type is represented in the database, e.g. String.

·         Converter Type: The name of the class which converts between the database representation and the CLR type, e.g. MyProject.YesNoConverter.

·         Is Standard Data Type: You can usually leave this as True.  However, if the storage format uses a database‑specific type such as the MySQL time type, set it to False, and enter the database type name in the Database Type Name box.

Once you have defined a user-defined type to represent a custom storage format, you can use it just like any other data type, by selecting it from the Data Type drop down:

LightSpeed will now generate a boolean property, but map it to a string column. Furthermore, now that the user-defined type has been set up, you can apply it to as many properties as you want, which makes it very convenient for mapping patterns that are used widely across a database.

Field Converters for Hand‑Coded Entities

You can also use field converters with hand‑coded entities.  This is done in a slightly different way from the designer.  Instead of creating a user‑defined type, you simply define each field as being of the desired domain type, then apply ColumnAttribute to the field, specifying the ConverterType property.

[Column(ConverterType = typeof(YesNoConverter))]
private bool _isFullOfGreeks; // domain type
 
public bool IsFullOfGreeks
{
  get { return _isFullOfGreeks; }
  set { Set(ref _isFullOfGreeks, value, "IsFullOfGreeks"); }
}

Field Converters and Querying

When you use a field that has a field converter in a query, the field converter is used to translate the comparand to its database format.  For example, consider the following query:

var woes = from h in uow.Horses
           where h.IsFullOfGreeks == true
           select h;

The YesNoConverter is applied to the comparand true, resulting in SQL such as:

SELECT ...
FROM Horse
WHERE IsFullOfGreeks = 'Y' // parameter substituted for clarity

This enables users of the entity to write queries in domain terms, and to use LINQ without suffering compiler type errors.

Querying Considerations for Field Converter Design

Some care is required if the conversion is not one-to-one.  For example, consider a database convention which represents Booleans as integers, with 0 representing false and any non-zero value representing true.  When converting Booleans back to integers, the converter has to pick a value to represent true, say 1.

public class IntBoolConverter : FieldConverter<int, bool>
{
  protected override bool ConvertFromDatabase(int databaseValue)
  {
    return databaseValue != 0;
  }
  protected override string ConvertToDatabase(bool value)
  {
    return value ? 1 : 0; // danger!
  }
}

Suppose now you wrote the LINQ query where o.SomeIntBoolField == true.  The converter translates the comparand true to the database value 1.  So LightSpeed would emit the SQL clause WHERE SomeIntBoolField == 1.  This would not match values such as 2 or -1, even though these are meant to be considered true.  You would therefore need to be careful to write the query where o.SomeIntBoolField != false, as this would translate to WHERE SomeIntBoolField <> 0.  This is only an issue if your mapping is not one-to-one.