CODEWITHSUNDEEP
c#mappingentity-framework-6type-conversionnpgsql
Sven-Michael Stübe
Sven-Michael Stübe

Reputation: 14760

Store bool Property as integer with Npgsql and Entity Framework

I'am using Entity Framework 6.1 with the Npgsql 2.2.5 driver.

Entity

public class MyEntity
{
    public bool Deprecated { get; set; }
}

Mapping

public class MyEntityMap : EntityTypeConfiguration<MyEntity>
{
    public MyEntityMap ()
    {
        Property(t => t.Deprecated)
            .HasColumnName("status")
            .HasColumnType("Integer")
            .IsOptional();
    }
}

When I try to read something from the Database, I get an exception, that is not directly related to something with mapping:

InvalidOperationException "Sequence doesn't contain any matching element" (translated from german, don't know the exact english text)

Is it possible to store a boolean property as an integer? I did a Workaround with introducing a new property Status of type int, which is mapped to the status column. Then I added the NotMappedattribute to Deprecated, made it return Status != 0 int its getter and setting Status to 1 or 0. It is working, but now I can't use Deprecated in linq queries.

I'd simply change the datatype of the column, but there is a legacy system using this database as well. Introducing a new column and keep both in sync with some database triggers would be a solution, but my model has some of these issues. So I'd like to have a more generic solution.

Is there a better way?

Upvotes: 4

Views: 1329

Answers (1)

C. Tewalt
C. Tewalt

Reputation: 2509

Yeah... same problem here.

I don't think there's a clean way to do it unless you modify the source for the npgsql EF provider. 

public static class DbValue
{
    public const int FALSE = 0; //or something like this
}
public class MyEntity
{

    [Column("Deprecated")]
    public integer DeprecatedStatus { get; set; }
    [NotMapped]
    public bool DeprecatedBool 
    { 
        get { this.DeprecatedStatus != 0 }
        set { this.DeprecatedStatus = (value ? 1 : 0) }
    }
}

//Then in Linq
db.MyEntities.Where(e => e.DeprecatedStatus == DbValue.FALSE);

//and
db.MyEntities.Where(e => e.DeprecatedStatus != DbValue.FALSE);

Oh, hey I just thought of another idea. You could write Expression objects in the code and pass them into your Linq (since IQueryable<> uses expressions)

so like this:

public class MyEntity
{
    public static Expression<Func<MyEntity, bool>> IsDeprecated = (myEntity) => myEntity.Deprecated != 0;
    public static Expression<Func<MyEntity, bool>> IsNotDeprecated = (myEntity) => myEntity.Deprecated == 0;
    public integer Deprecated{ get; set; }

}
//Then in Linq
db.MyEntities.Where(MyEntity.IsDeprecated);

//and
db.MyEntities.Where(MyEntity.IsNotDeprecated);

The reason for using Expressions instead of Func stuff can be a little confusing for novices, but the pattern is certainly easy to follow if you're comfortable with lambda expressions. I've done this kind of thing before and it works. What doesn't work is trying to dynamically create your Expression objects at runtime because something goes awry in the EF code. (only compiler geeks would think to do that anyway)

So the disadvantage here is every time you have an expression in your LINQ that you want to use the Deprecated property, you have to create another static expression object.

Upvotes: 1

Related Questions