Convert value when mapping

Question

I'm using EF Code-First to an existing database method and have a IsActive field in my database. The problem is that the field is VARCHAR when it should be a boolean. I can't change the Database schema.

Example value in the database are "Y" (true) or "N" (false)

When mapping, I want to convert those values to either true/false and keep my Entity class with the boolean value.

Is this possible?

My Entity and mapping classes are the following but I would like to change the IsActive field to be a boolean.

public class Employee
{
    public int ID { get; set; }
    public string SSN { get; set; }
    public string Email { get; set; }
    public string IsActive { get; set; }
}

public class EmployeeMap : EntityTypeConfiguration<Employee>
{
    public EmployeeMap()
    {
        this.ToTable("Employees");

        this.HasKey(t => t.ID);

        this.Property(t => t.ID).HasColumnName("ID_Employee");
        this.Property(t => t.SSN).HasColumnName("sReference");
        this.Property(t => t.Email).HasColumnName("Email");
        this.Property(t => t.IsActive).HasColumnName("IsActive");
    }
}

EDIT: I found no other solution than this: https://mcmap.net/q/409951/-convert-from-to-string-in-database-to-boolean-property-entity-framework-4-1

Meraree

You use reverse engineering from database first, right ? So your class Employee is auto-generated and partial ? – Involucrum

Yes, correct, but I map them manually. – Meraree

No sorry, I'm using EF Code-First to an existing database. – Meraree

possible duplicate of Convert from to string in database to boolean property Entity Framework 4.1 – Meraree

Yes, pretty much your only option is to make a new property whose getter returns IsActive=="Y"?true:false and optional setter with IsActive=value?"Y":"N"... – Fasciculus

Answer 1

As others have pointed out you need two properties, but you may be interested to know that you can make one of the properties private and still map it to the database:

    private string isActive { get; set; }

    [System.ComponentModel.DataAnnotations.Schema.NotMapped]
    public bool IsActive
    {
        get { return isActive == "Y"; }
        set { isActive = value ? "Y" : "N"; }
    }

If you are using EF6 you can use a custom convention in the OnModelCreating method to map the private property

modelBuilder.Types().Configure(c =>
{
    //NB the syntax used here will do this for all entities with a 
    //private isActive property
    var properties = c.ClrType.GetProperties(BindingFlags.NonPublic 
                                             | BindingFlags.Instance)
                              .Where(p => p.Name == "isActive");
    foreach (var p in properties)
        c.Property(p).HasColumnName("IsActive");
});

References:

Mapping private properties using custom conventions

Mapping private properties without custom conventions (before EF6)

Edit:

Here's another way of identifying the private properties that should be mapped to the database:

First add the column attribute to the private property:

[System.ComponentModel.DataAnnotations.Schema.Column]
private string isActive { get; set; }

Then use the presence of that attribute to identify private properties in your OnModelCreating method:

modelBuilder.Types().Configure(c =>
{
    var properties = c.ClrType
        .GetProperties(BindingFlags.NonPublic | BindingFlags.Instance)
        .Where(propInfo => 
           propInfo.GetCustomAttributes(typeof(ColumnAttribute), true).Length > 0);

    foreach (var p in properties)
        c.Property(p).HasColumnName(p.Name);
});

Reference: Mapping a private property with entity framework

Gadhelic

Thank you. Knowing I can map a private property suddenly saves me a ton of code. – Montes

Wouldn't this prevent you from writing LINQ queries against the DB with things like where x.IsActive since that public property wouldn't be mapped? – Diarrhea

@Mycroft Yes, it would. But if you keep all your data access code in a separate assembly, you might consider making the property internal rather than private. – Gadhelic

What if I have bool? mapped but I need a private bool – Mussorgsky

Answer 2

I have a solution to actually map the type meaning additional properties are not required. As I had a similar question for DateTimes, but easily convertible for bools:

Is there a simple way using data annotations or a custom type to use a value stored as a string in SQL as a DateTime in EF?

Compton