How to configure Entity Framework to map some text to Boolean and vice-versa

Question

Let's say that a table have columns that are of type varchar just to save binary values like YES/NO, TRUE/FALSE, STATIC/DCHP,... I believe strongly that I could rather use Boolean in the Entity Framework.

This is the table

+---------------------+-------------+
|  AccessibleRemotely |  IPType     |
+ --------------------+-------------+
|       YES           |  Static     |
+---------------------+-------------+
|        NO           |  DHCP       |
+---------------------+-------------+

This is my entity

public class Device
{
    public bool? AccessibleRemotely { get; set; }
    public bool? IPType { get; set; }
}

This is my configuration

public class DeviceConfiguration : EntityTypeConfiguration<Device>
{
    public DeviceConfiguration()
    {
        //mapping here...
    }
 }

How do I transform text to Boolean and vice-versa?

Thanks for helping

Why not fix the database rather than trying to coerce EF into doing it? — DavidG, Jul 20 '17 at 14:33
@DavidG, I think that would be a nice and easy fix, but I don't own the database. The team that owns it claims that they've already wrote tone of SPROC against it to produce reports. — Richard77, Jul 20 '17 at 14:47
Have them write you a sproc to hit that does the translation. — Kevin, Jul 20 '17 at 14:53

Zerghouni Idriss · Answer 1 · 2017-07-20T15:16:34.377

I don't think there is such a config that do that. Even if it exists, I wouldn't use it. However, I had the same issue with IsActive column and this is how I solve it:

public string IsActiveString{ get; set; }

[NotMapped]
public bool IsActive
{ 
    get { return IsActiveString== "Yes"; }
    set { IsActiveString= value ? "Yes" : "No" }
}

The Not mapped attribute will prevent entity frame duplicating the same column with different value format.

This way, in your Database you will stay working with the string column as you were used to do. However all over the code you should be calling IsActive and not IsActiveString

score 1 · Accepted Answer · answered Jul 20 '17 at 15:03

1

I think you can map your column to some private property and than just convert it to bool:

    public class Device
    {
        private string AccessibleRemotelyStr { get; set; }

        public bool AccessibleRemotely
        {
            get
            {
                return string.Equals(AccessibleRemotelyStr, "Yes", StringComparison.OrdinalIgnoreCase);
            }
            set
            {
                AccessibleRemotelyStr = value ? "Yes" : "No";
            }
        }
    }

    public class DeviceConfiguration : EntityTypeConfiguration<Device>
    {
        public DeviceConfiguration()
        {
            Property(p => p.AccessibleRemotelyStr).HasColumnName("AccessibleRemotely");
            Ignore(p => p.AccessibleRemotely);
        }
    }

answered Jul 20 '17 at 15:03

Roman Koliada

4,286
2
30
59

How do you send back data to the database? Let's say user selects a true, then I want be able to send it back to the database if I ignore the column. I'm a little confuse. – Richard77 Jul 20 '17 at 15:18
When the user changes value for `AccessibleRemotely` then related value for `AccessibleRemotelyStr` will be set and stored in the DB after `SaveChanges()` call. Interaction with DB goes through `AccessibleRemotelyStr` property, not `AccessibleRemotely` – Roman Koliada Jul 20 '17 at 15:24
I'm getting this error: `The specified type member 'AccessibleRemotely' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.` – Richard77 Jul 20 '17 at 19:39
I guess that you're using `AccessibleRemotely` in `Where` or `OrderBy` clause. You cannot do this for `IQueryable` because EF cannot translate it to SQL. So you have 2 options: use `AccessibleRemotelyStr` for filtering and sorting on DB side or use `AccessibleRemotely` only after results are loaded to the memory(so you will have `IList` instead of `IQueryable`) to filtering and sorting in memory. – Roman Koliada Jul 21 '17 at 10:52

How to configure Entity Framework to map some text to Boolean and vice-versa

2 Answers2