CODEWITHSUNDEEP
c#entity-frameworkmapping
Sonic Soul
Sonic Soul

Reputation: 24969

Entity Framework: mapping tinyint to boolean

by default Entity Framework maps tinyint to byte.

i tried changing the underlying type after it was generated to Boolean, but getting compilation error

Member Mapping specified is not valid. The type 'Edm.Boolean[Nullable=False,DefaultValue=]' of member blah...

is this possible in 4.0?

it wasn't my idea to use tinyint column as boolean. this was done automatically by another team using hibernate which apparently does it that way for mysql compatibility. obviously tinyint has more values than 2. I am looking for a way to map it so that anyting accept for 1 is false, or anything accept for 0 is true. either would work for me

is there a way to plug in a type translator of sorts into EF?

Upvotes: 11

Views: 22878

Answers (4)

scipilot
scipilot

Reputation: 7467

I have found that setting the display width of the column in MySQL controls whether a TINYINT will be mapped as a boolean or byte.

I was puzzled why a TINYINT field was being mapped to a boolean by default when updating the model from the database, until I realised someone had originally set the display value to (1) in the MySQL schema.

i.e.

TINYINT (3) ==> byte or SByte

TINTYINT(1) ==> boolean

TINTYINT(1) can still store values greater than 1 so interpretation is still an issue, but at least this gives you some control over the import process without having to re-edit any generated models.

https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html

Upvotes: 0

Frank Rem
Frank Rem

Reputation: 3738

If your existing database has a tinyint column that you wish to represent as a Boolean property of your C# class, then you can do this as follows:

public class Subscription
{
  public int Id { get; set; }
  public string Name { get; set; }

  // the column of your database
  public byte? autoRenew { get; set; }

  // the property you want
  [NotMapped]
  public bool Autorenew
  {
    get => autoRenew > 0;
    set { this.autoRenew = (byte)(value ? 1 : 0);  }
  }
}

Obviously this assumes that 0 and 1 correspond to false and true, respectively. In this sample, the autoRenew is nullable and null is interpreted as false.

Upvotes: 9

Swordfish
Swordfish

Reputation: 51

Actually the main reason why often integers are used in database has to due with the fact that a lot of database engines don't allow indexes on bit fields. Most database engines try to group multiple bit fields in one 'internal' byte to safe space. As a result the bit field isn't really available for indexing..

The defacto standard is that 0 equals to false and all other values equal to true. However EF does not have support for this kind of mapping. The best method is using a private shadow field declared as byte which is mapped to EF. Second you create a alias boolean property which is used by your code.

Mapping private properties with EF requires some reflection code.

Upvotes: 5

Donut
Donut

Reputation: 112885

From MSDN's page on integer types, we see that the tinyint type represents an integer ranging from 0 to 255.

A bool, in contrast, represents only a binary 0 or 1.

Changing the default mapping from byte to bool (if it were even possible, which according to this page it seems like it's not) does not make sense -- how, for example, would you represent the value 42 (a valid tinyint) as a bool?

If you need an entity with a property of type bool, I'd suggest mapping it to a column of type bit.

Upvotes: 6

Related Questions