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://stackoverflow.com/a/6709186/1053611

You use reverse engineering from database first, right ? So your class Employee is auto-generated and partial ? — Raphaël Althaus, Oct 14 '13 at 22:21
possible duplicate of [Convert from to string in database to boolean property Entity Framework 4.1](http://stackoverflow.com/questions/6708996/convert-from-to-string-in-database-to-boolean-property-entity-framework-4-1) — Gaui, Oct 14 '13 at 22:48
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"`... — Stephen Byrne, Oct 14 '13 at 23:36

score 47 · Accepted Answer · edited May 23 '17 at 11:54

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

Thank you. Knowing I can map a private property suddenly saves me a ton of code. — ProfK, Nov 28 '13 at 02:19
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? — Mykroft, Sep 30 '14 at 13:25
@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`. — Colin, Oct 22 '14 at 07:58

score -2 · Answer 2 · answered Aug 23 '18 at 10:25

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?

Convert value when mapping

2 Answers2

Linked

Related