EF6 with SQL Server and MYSQL, with mismatch datatype in database

Question

In my project, I am trying to use Entity Framework 6 with SQL Server and MySql.

Now I have a column in database named ISENABLED, now this column is bool in SQL Server and Char(1) in MYSQL, ideally it should be BIT in MYSQL but since this is legacy database, it uses Char for this column.

Now whenever I try to fetch this table from DB using EF6, I have successful result in case of SQL Server, because in code I have a bool property mapped for that database but for Mysql it throws an error saying string cannot be converted to bool. Singh char is converted to string in EF6.

Here is my code:

Property mapped in code for database column:

  public bool IsEnabled { get; set; }

Now what I did try for MySQL is:

    private string IsCharBasedEnabled { get; set; }
[NotMapped]
public bool IsEnabled
{
    get { return IsCharBasedEnabled == "0"; }
    set { IsCharBasedEnabled = value ? "1" : "0"; }
}

I mapped IsCharBasedEnabled to the database column, Char column of MySQL is mapped as string and then converted to bool as shown above.

But this solution won't work for SQL Server.

Can I have something which work for both the database with same property?

I can't change the schema of my MySQL database.

What should I do here?

Answer 1

Ok i found the Solution:

     private bool IsEnabled1 { get; set; }

     private string IsCharBasedEnabled { get; set; }


     [NotMapped]
     public bool IsEnabled
     {
         get { return IsCharBasedEnabled == "True" || IsCharBasedEnabled == "Y" || IsEnabled1; }
         set {
             if (value.ToString() == "Y" || value.ToString() == "N")
             {
                 IsCharBasedEnabled = value ? "Y" : "N";
             }
             else if (value.ToString() == "True" || value.ToString() == "False")
             {
                 IsCharBasedEnabled = value ? "True" : "False";
             }
             else if(value)
             {
                 IsEnabled1 = true;
             }
         }
     }

Work with all the three database, map the property dynamically in OnModelCreating Function.

Code for binding private property is:

modelBuilder.Types().Configure(c =>
            {
                var properties = c.ClrType.GetProperties(BindingFlags.NonPublic
                                                        | BindingFlags.Instance)
                                          .Where(p => p.Name == "IsEnabled1");
                foreach (var p in properties)
                    c.Property(p).HasColumnName("FeatureEnable").IsRequired();
            });

So for Sql server bind IsEnabled1, for MySQL and postgres bind ISCHarBasedEnabled.

It is working fine for all the three databases.