Entity Framework 6 - Mapping negative 1 to C# true

Question

I have an existing system which for some reason, which I can't fathom for the life of me, has negative 1 (-1) as the representation of true in the database (Oracle 12.C)

I would really like to be able to map this in EF 6 to a boolean value without the pain of having a wrapping property.

Anyone know of a way to support this scenario? The trouble I am seeing is that linq queries like Where(v => !v.IsDeleted) will generate SQL which is positive 1 orientated i.e. WHERE (item.IS_DELETED <> 1))

If I could get the SQL generated to be more interested in the relationship to false (Zero value) then I can keep my C# code agnostic

Any help or pointers would be greatly appreciated!

Answer 1

I settled on using an Enum to represent the boolean

public enum BooleanType
{
    False = 0,
    True = -1
}

As EF doesn't correctly support Enum Properties (It will CAST in ORACLE) I then added a Linq Extension method to remove the unnessary casts.

public class RemoveCastVisitor : ExpressionVisitor
{
    protected override Expression VisitBinary(BinaryExpression node)
    {
        if (node.NodeType == ExpressionType.Equal) {
            var leftUnary = node.Left as UnaryExpression;
            var rightUnary = node.Right as UnaryExpression;

            if (leftUnary == Null || rightUnary == Null)
                return base.VisitBinary(node);

            if (leftUnary.NodeType != ExpressionType.Convert)
                return base.VisitBinary(node);

            if (rightUnary.NodeType != ExpressionType.Convert)
                return base.VisitBinary(node);

            return Expression.Equal(leftUnary.Operand, rightUnary.Operand);
        }
        return base.VisitBinary(node);
    }
}


public static class QueryableExtensions
{
    /// <summary>
    /// Removes ANY Casting from the Query Expression
    /// </summary>
    public static IQueryable<T> RemoveCasts<T>(this IQueryable<T> q)
    {
        var visitor = new RemoveCastVisitor();
        Expression original = q.Expression;
        var expr = visitor.Visit(original);
        return q.Provider.CreateQuery<T>(expr);
    }
}

And here is my test showing it working

IQueryable<int> query = context.Set<UnitTest>().Where(v => v.IsDeleted == BooleanType.True).Select(v => v.Id);
query = query.RemoveCasts();
var sql = ((DbQuery<int>)query).ToString();

var expected =
    "SELECT \r\n" +
    "\"Extent1\".\"ID\" AS \"ID\"\r\n" +
    "FROM \"EZIAUS\".\"ZZ_UNIT_TEST\" \"Extent1\"\r\n" +
    "WHERE (-1 = \"Extent1\".\"IsDeleted\")";

Assert.AreEqual(expected, sql);

Answer 2

I will take adavantage of TPH (Table Per Hierarchy) ORM pattern with EF. With this pattern all your base class and derived classes witl share same table. Each row of the table will be differentiate by a discriminator.

First I will have this abstract class so let's name it WeirdModel:

public abstract class WeirdModel
{
    public int Id { get; set; }
    public string SomeProperty { get; set; }
}

Then create a two derived classes from WeirdModel let's name them: TrueWeirdModel and FalseWeirdModel:

public class TrueWeirdModel : WeirdModel
{
}

public class FalseWeirdModel : WeirdModel
{
}

My context's definition will look like:

public class MyContext : DbContext
{
    public DbSet<WeirdModel> Weirds { get; set; }
    public DbSet<FalseWeirdModel> FalseWeirdModels { get; set; }
    public DbSet<TrueWeirdModel> TrueWeirdModels { get; set; }

    public MyContext()
        : base("MyContext")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<WeirdModel>()
            .Map<TrueWeirdModel>(p => p.Requires("WeirdBoolean").HasValue(-1))
            .Map<FalseWeirdModel>(p => p.Requires("WeirdBoolean").HasValue(1));
    }
}

Here I say that WeirdBoolean column on DB will contain

• -1 for TrueWeirdModel
• 1 for FalseWeirdModel

To use them:

using (var context = new MyContext())
{
    var trueWeirdData = new TrueWeirdModel { SomeProperty = "true weird model" };
    var falseWeirdData = new FalseWeirdModel { SomeProperty = "false weird model" };

    context.Weirds.AddRange(new WeirdModel[] { trueWeirdData, falseWeirdData });

    context.SaveChanges();
}

using (var context = new MyContext())
{
    var allWeirdModels = context.Weirds.ToList();

    var allTrueWeirdData = context.TrueWeirdModels.ToList();

    var allFalseWeidData = context.FalseWeirdModels.ToList();
}

In your database we will have this:

With TPH ORM pattern you will never manipulate the weird column WeirdBoolean which is used as a discriminator. BTW you still can create a property that map to it.

In your whole application you will never have to use this column as a filter in your Linq To Entities queries. The where filter for WeirfBoolean will be automatically added when using TruesWeirdModels or FalseWeirdModels Dbset. To get them all you just use the abstract class WeirdModels DbSet. Because WeirdModel is an abstract class you can't instantiate it, you must use FalseWeirdModelor TrueWeirdModel if you want to create a new row on your table.