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.

Database First

EDM Wizard / EDM Designer

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

• Replace the String type of the required properties in the conceptual model with the Boolean type.
• Replace the char type of the corresponding storage model columns with the "yes no char as boolean". It cannot be done in the EDM Designer, and needs manual model editing with a text editor, for example, Visual Studio XML Editor.

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:

• You can open the editor for a storage model column from the editor of the corresponding conceptual model property (see the "Property Editor" topic in the Entity Developer documentation).
• You can right-click the conceptual model property on a diagram and select Select Storage from the shortcut menu to navigate to the corresponding storage model column.

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