Entity Framework 6 Code First on SQL Server: Map “

Question

Forward warning #0: upgrading to EF core is not an option in the near future.

Forward warning #1: I can't change the column type to bit because this could potentially break legacy VB apps that employ the very same db I'm developing a new app for.

Forward warning #2: I also can't employ the int property ==> hidden bool property approach because the very same model needs to work when targeting an Oracle database (in Oracle decimal(1,0) does indeed get mapped to bool without issues - I need to make the same thing happen in SQL Server).

Let's assume we have a simple table like this one:

CREATE TABLE FOOBAR 
(
    FB_ID NUMERIC(11,0) PRIMARY KEY,
    FB_YN NUMERIC(1,0) NOT NULL
);

INSERT INTO FOOBAR (FB_ID, FB_YN)
VALUES (1, 1), (2, 0);

A simple poco class:

public class FOOBAR 
{
     public long FB_ID {get; set;}

     // [Column(TypeName = "numeric(1,0)")]
     // ^--- doesn't work in ef6  =>  'The store type 'numeric(1,0)' could not be found in the SQL Server provider manifest'
     // ^--- allegedly this works in EF core with Microsoft.EntityFrameworkCore.Relational nuget package installed
     // ^--- https://docs.microsoft.com/en-us/ef/core/modeling/relational/data-types
     // ^--- but I couldn't find anything similar for EF 6
     public bool FB_YN {get; set;}
}

And an equally simple fluent config class:

public class FOOBAR_FluentConfiguration : EntityTypeConfiguration<FOOBAR>
{
    public FOOBAR_FluentConfiguration()
    {
        ToTable(tableName: "FOOBAR");

        HasKey(x => x.FB_ID);

        // Property(x => x.FB_YN).HasColumnType("numeric(1,0)");
        // ^--- doesn't work in ef6  =>  'The store type 'numeric(1,0)' could not be found in the SQL Server provider manifest'
        // ^--- allegedly this works in EF core with Microsoft.EntityFrameworkCore.Relational nuget package installed
        // ^--- but I couldn't find anything similar for EF 6
    }
}

As mentioned in the comments any of the attempt to convince ef6 to map <bool> to the <numeric(1,0)> column in table fail miserably at runtime. I have also tried achieving the desired effect via EF conventions:

public sealed class MsSqlConventions : Convention
{
    public MsSqlConventions()
    {
        Properties<bool>().Configure(p => p.HasColumnType("numeric(1,0)")); //fails
    }
}

This fails with the following message:

The store type 'numeric(1,0)' could not be found in the SQL Server provider manifest

While this one:

public sealed class MsSqlConventions : Convention
{
    public MsSqlConventions()
    {
        Properties<bool>().Configure(p => p.HasColumnType("numeric").HasPrecision(1, 0)); //fails
    }
}

This fails with the following message:

Precision and scale have been configured for property 'FB_YN'. Precision and scale can only be configured for Decimal properties.

I also tried to toy around with (enrich) the SQL Server provider manifest a la:

DbProviderServices.GetProviderManifest();

but I can't make heads or tails out of it (yet). Any insights appreciated.

Answer 1

Here's a way to arm-twist EF6 into handling numeric(1,0) columns as BIT columns. It's not the best thing ever and I've only tested it in the scenarios shown at the bottom but it works reliably as far as my testing goes. If someone detects a corner case where things do not go as planned feel free to drop a comment and I will improve upon this approach:

<!-- add this to your web.config / app.config -->
<entityFramework>
    [...]
    <interceptors>
        <interceptor type="[Namespace.Path.To].MsSqlServerHotFixerCommandInterceptor, [Dll hosting the class]">
        </interceptor>
    </interceptors>
</entityFramework>

And the implementation of the interceptor:

// to future maintainers     the reason we introduced this interceptor is that we couldnt find a way to persuade ef6 to map numeric(1,0) columns in sqlserver into bool columns
// to future maintainers     we want this sort of select statement
// to future maintainers     
// to future maintainers        SELECT 
// to future maintainers           ...
// to future maintainers           [Extent2].[FB_YN]  AS [FB_YN], 
// to future maintainers           ...
// to future maintainers        FROM  ...
// to future maintainers     
// to future maintainers     to be converted into this sort of select statement
// to future maintainers     
// to future maintainers        SELECT 
// to future maintainers           ...
// to future maintainers           CAST ([Extent2].[FB_YN]  AS BIT) AS [FB_YN],    -- the BIT cast ensures that the column will be mapped without trouble into bool properties
// to future maintainers           ...
// to future maintainers        FROM  ...
// to future maintainers
// to future maintainers     note0   the regex used assumes that all boolean columns end with the _yn postfix   if your boolean columns follow a different naming scheme you
// to future maintainers     note0   have to tweak the regular expression accordingly
// to future maintainers
// to future maintainers     note1   notice that special care has been taken to ensure that we only tweak the columns that preceed the FROM part  we dont want to affect anything
// to future maintainers     note1   after the FROM part if the projects involved ever get upgraded to employ efcore then you can do away with this approach by simply following
// to future maintainers     note1   the following small guide
// to future maintainers
// to future maintainers                                           https://docs.microsoft.com/en-us/ef/core/modeling/relational/data-types
// to future maintainers
public sealed class MsSqlServerHotFixerCommandInterceptor : IDbCommandInterceptor
{
    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        HotfixFaultySqlCommands(command, interceptionContext);
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        HotfixFaultySqlCommands(command, interceptionContext);
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        HotfixFaultySqlCommands(command, interceptionContext);
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    static private void HotfixFaultySqlCommands<TResult>(IDbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
    {
        if (!command.CommandText.TrimStart().StartsWith("SELECT", ignoreCase: true, culture: CultureInfo.InvariantCulture))
            return;

        command.CommandText = BooleanColumnSpotter.Replace(command.CommandText, "CAST ($1 AS BIT)");
    }

    static private readonly Regex BooleanColumnSpotter = new Regex(@"((?<!\s+FROM\s+.*)([[][a-zA-Z0-9_]+?[]][.])?[[][a-zA-Z0-9_]+[]])(?=\s+AS\s+[[][a-zA-Z0-9_]+?_YN[]])", RegexOptions.IgnoreCase);
}

And some quick testing:

{
  // -- DROP TABLE FOOBAR;
  // 
  // CREATE TABLE FOOBAR (
  // FB_ID NUMERIC(11,0) PRIMARY KEY,
  // FB_YN NUMERIC(1,0) NOT NULL,
  // FB2_YN NUMERIC(1,0) NULL
  // );
  // 
  // INSERT INTO FOOBAR (FB_ID, FB_YN, FB2_YN)
  // VALUES             (1, 0, 0);
  // 
  // INSERT INTO FOOBAR (FB_ID, FB_YN, FB2_YN)
  // VALUES             (2, 1, 1);
  // 
  // INSERT INTO FOOBAR (FB_ID, FB_YN, FB2_YN)
  // VALUES             (3, 1, null);

  var mainDatabaseContext = new YourContext(...);

  var test1 = mainDatabaseContext.Set<FOOBAR>().ToList();
  var test2 = mainDatabaseContext.Set<FOOBAR>().Take(1).ToList();
  var test3 = mainDatabaseContext.Set<FOOBAR>().Take(10).ToList();
  var test4 = mainDatabaseContext.Set<FOOBAR>().FirstOrDefault();
  var test5 = mainDatabaseContext.Set<FOOBAR>().OrderBy(x => x.FB_ID).ToList();
  var test6 = mainDatabaseContext.Set<FOOBAR>().Take(10).Except(mainDatabaseContext.Set<FOOBAR>().Take(10)).SingleOrDefault();
  var test7 = mainDatabaseContext.Set<FOOBAR>().Where(x => x.FB_ID == 1).ToList();
  var test8 = mainDatabaseContext.Set<FOOBAR>().Where(x => x.FB_YN).ToList();
  var test9 = (
      from x in mainDatabaseContext.Set<FOOBAR>()
      join y in mainDatabaseContext.Set<FOOBAR>() on x.FB_ID equals y.FB_ID into rightSide
      from r in rightSide.DefaultIfEmpty()
      select r
  ).ToList();

  var test10 = (
          from x in mainDatabaseContext.Set<FOOBAR>()
          join y in mainDatabaseContext.Set<FOOBAR>() on new {x.FB_YN, FB_YN2 = x.FB2_YN} equals new {y.FB_YN, FB_YN2 = y.FB2_YN} into rightSide
          from r in rightSide.DefaultIfEmpty()
          select r
      ).ToList();
}