dotConnect for DB2 Documentation
In This Topic
    Using CHAR(1) mapped to System.Boolean in Entity Framework
    In This Topic

    DB2 does not provide a dedicated type for storing boolean values in tables. There are different workarounds for this problem. The most common workarounds are using SMALLINT or CHAR(1) columns for storing boolean values and setting them to 0 or 1 for SMALLINT fields or to 'Y' or 'N' for CHAR(1) fields.

    dotConnect for DB2 Entity Framework provider supports both mapping SMALLINT and CHAR(1) to System.Boolean. To support mapping System.Boolean to CHAR(1), a new data type "yes no char as boolean" is introduced. This type does not have a direct counterpart in DB2. It can be used in SSDL model part for columns of storage entities and as a column data types for Code First mapping. By default, mapping SMALLINT to System.Boolean is used. You can read about mapping CHAR(1) to System.Boolean for different development approaches below.

    Database First

    EDM Wizard / EDM Designer

    After creating an Entity Framework model, you need to edit it in the following way:

    Entity Developer

    If you need to map all the CHAR(1) columns to System.Boolean, add the new type mapping rule (on the Server Options -> DB2 page of Entity Developer Options dialog box) and then create the model. See the Creating and Editing Type Mapping Rules topic in the Entity Developer documentation.

    If you need only some of the CHAR(1) columns to be mapped to System.Boolean, you need to manually edit the necessary properties and change their type from String to Boolean. You also need to change the type of the corresponding storage model columns from "CHAR" to "yes no char as boolean". Entity Developer allows doing it quite easily because it supports full-featured visual editing of both conceptual and storage parts of the model and quick navigation between conceptual model entity property and storage model entity column in two ways:

    Code First

    Defining Type Explicitly

    The following example demonstrates mapping System.Boolean to both SMALLINT (the NumberBasedBoolean property) and CHAR(1) (the CharBasedBoolean property).

    We will use the Column data annotation attribute to specify the type "yes no char as boolean":

      public class BoolTable {
     
        public int Id { get; set; }
     
        public bool NumberBasedBoolean { get; set; }
     
        [Column(TypeName = "yes no char as boolean")]
        public bool CharBasedBoolean { get; set; }
     
      }
     
      public class MyContext: DbContext {
     
        public DbSet<BoolTable> BoolTable { get; set; }
     
        // ...
      }
    
    
    	Public Class BoolTable
    
    	Public Property Id() As Integer
    		Get
    			Return m_Id
    		End Get
    		Set
    			m_Id = Value
    		End Set
    	End Property
    	Private m_Id As Integer
    
    	Public Property NumberBasedBoolean() As Boolean
    		Get
    			Return m_NumberBasedBoolean
    		End Get
    		Set
    			m_NumberBasedBoolean = Value
    		End Set
    	End Property
    	Private m_NumberBasedBoolean As Boolean
    
    	<Column(TypeName := "yes no char as boolean")> _
    	Public Property CharBasedBoolean() As Boolean
    		Get
    			Return m_CharBasedBoolean
    		End Get
    		Set
    			m_CharBasedBoolean = Value
    		End Set
    	End Property
    	Private m_CharBasedBoolean As Boolean
    
    End Class
    
    Public Class MyContext
    	Inherits DbContext
    
    	Public Property BoolTable() As DbSet(Of BoolTable)
    		Get
    			Return m_BoolTable
    		End Get
    		Set
    			m_BoolTable = Value
    		End Set
    	End Property
    	Private m_BoolTable As DbSet(Of BoolTable)
    
    	' ...
    End Class
    

    The following DDL will be generated for such a class:

    CREATE TABLE "BoolTables" ( 
      "Id" INTEGER  NOT NULL,
      "NumberBasedBoolean" SMALLINT NOT NULL,
      "CharBasedBoolean" CHAR(1) NOT NULL,
      PRIMARY KEY ("Id")
    )

    Specifying Type via Convention

    Entity Framework 6 allows using conventions for mapping.

    Conventions are convenient when, for example, all the System.Boolean properties in all model classes must be mapped to CHAR(1) columns. In such a case you may write a lightweight convention instead of specifying data type for each boolean property with ColumnAttribute.

      public class MyContext : DbContext {
     
        protected override void OnModelCreating(DbModelBuilder modelBuilder) {
     
          modelBuilder
            .Properties()
            .Where(p => p.PropertyType == typeof(Boolean))
            .Configure(p => p.HasColumnType("yes no char as boolean"));
        }
     
        // ...
      }
    
    
    Public Class MyContext
    	Inherits DbContext
    
    	Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
    
    		modelBuilder.Properties().Where(Function(p) p.PropertyType = GetType([Boolean])).Configure(Function(p) p.HasColumnType("yes no char as boolean"))
    	End Sub
    
    	' ...
    End Class
    End Sub
    
    

    See Also

    Entity Framework  | Entity Framework Data Type Mapping