dotConnect for Oracle Documentation
Using CHAR(1) mapped to System.Boolean in Entity Framework
support@devart.com

Oracle 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 NUMBER(1) or CHAR(1) columns for storing boolean values and setting them to 0 or 1 for NUMBER(1) fields or to 'Y' or 'N' for CHAR(1) fields.

dotConnect for Oracle Entity Framework provider supports both mapping NUMBER(1) 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 Oracle. 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 NUMBER(1) 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 -> Oracle 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 NUMBER(1) (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":

C#csharpCopy Code
  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; }
 
    // ...
  }
Visual BasicCopy Code
	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" NUMBER(10)  NOT NULL,
  "NumberBasedBoolean" NUMBER(1) 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.

C#csharpCopy Code
  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"));
    }
 
    // ...
  }
Visual BasicCopy Code
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