Entity Framework MySQL tinyint(1) System.Boolean.Parse FormatException

Question

I'm using EntityFramework 6 in my C# model-first project which using a MySQL database. Everything was fine and I could generate my database without problem.

Then I modified my .edmx file using the designer and here started the problems I have.

First the designer doesn't update the CSDL content and the C-S mapping content sections of the .edmx file anymore. So I updated the content myself and could finally compile the project.

Here's the .edmx file as it is right now and what it looks like in the designer:

EDMX File: http://pastebin.com/Xer9UyNR

And here is the link for the designer view: http://i.stack.imgur.com/Vcv9W.png

Second (and most important one), I get a FormatException when EF tries to get a tinyint coming from my database and change its type into a boolean.

    à ArmoireOutils.App.OnNavigateMessageHandler(OnNavigateMessage message) dans c:\Users\JB\Desktop\CodingFrance\ArmoireOutils\ArmoireOutils\App.xaml.cs:line 101System.FormatException: String was not recognized as a valid Boolean..
    à System.Boolean.Parse(String value)
    à System.String.System.IConvertible.ToBoolean(IFormatProvider provider)
    à System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
    à MySql.Data.Entity.EFMySqlDataReader.ChangeType(Object sourceValue, Type targetType)
    à MySql.Data.Entity.EFMySqlDataReader.GetValue(Int32 ordinal)
    à System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetUntypedValueDefault(DbDataReader reader, Int32 ordinal)
    à System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal)
    à System.Data.Entity.Core.Common.Internal.Materialization.Shaper.GetPropertyValueWithErrorHandling[TProperty](Int32 ordinal, String propertyName, String typeName)
    à lambda_method(Closure , Shaper )
    à System.Data.Entity.Core.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly[TEntity](Func`2 constructEntityDelegate, EntityKey entityKey, EntitySet entitySet)
    à lambda_method(Closure , Shaper )
    à System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
    à System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.RowNestedResultEnumerator.MaterializeRow()
    à System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.RowNestedResultEnumerator.MoveNext()
    à System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.ObjectQueryNestedEnumerator.TryReadToNextElement()
    à System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.ObjectQueryNestedEnumerator.ReadElement()
    à System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.ObjectQueryNestedEnumerator.MoveNext()
    à System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
    à System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)
    à System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.&lt;GetElementFunction&gt;b__2[TResult](IEnumerable`1 sequence)
    à System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
    à System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
    à System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
    à System.Linq.Queryable.SingleOrDefault[TSource](IQueryable`1 source)
    à ArmoireOutils.Services.DataService.GetCupboardByGuid(String guid) dans c:\Users\JB\Desktop\CodingFrance\ArmoireOutils\ArmoireOutils\Services\DataService.cs:line 202

Here is my GetCupboardByGUID method:

public Cupboard GetCupboardByGuid(String guid)
    {
        using (var context = new ArmoireOutilsEntities())
        {
            var cupboard = (from a in context.Cupboards
                where a.GUID.Equals(guid)
                select a)
                .Include("ResidentTools")
                .Include("Tools")
                .Include("Users") //If I remove this, .SingleOrDefault() works fine.
                .SingleOrDefault(); //Throw FormatException when getting the User.Active value from the database.

            if (cupboard != null)
                cupboard.RefreshLists();

            return cupboard;
        }
    }

And here is my User class generated by the .edmx tt:

public partial class User
{
    public User()
    {
        this.Tools = new ObservableCollection<Tool>();
        this.Cupboards = new ObservableCollection<Cupboard>();
        this.Active = true;
    }

    public int Id { get; set; }
    public short Type { get; set; }
    public string Firstname { get; set; }
    public string LastName { get; set; }
    public string Login { get; set; }
    public short Gender { get; set; }
    public short LangId { get; set; }
    public string Photo { get; set; }
    public System.DateTime CreationDate { get; set; }
    public Nullable<System.DateTime> ModificationDate { get; set; }
    public Nullable<System.DateTime> LastConnection { get; set; }
    public Nullable<System.DateTime> DisableDate { get; set; }
    public bool Active { get; set; }

    public virtual Lang Lang { get; set; }
    public virtual IList<Tool> Tools { get; set; }
    public virtual IList<Cupboard> Cupboards { get; set; }
}

So I guess EF is iterating over all users from the DB who are in the cupboarduser (the table linking a user to a cupboard for the many-to-many relationship) and when it comes to set the Active value for the first user, it gets 1 from the DB getting it as a String first and then try to parse that string into a boolean using System.Boolean.Parse but thaat method doesn't support numbers like "1" for true (the field in the DB is a tinyint(1)).

So why EF is not able to understand it was a tinyint so he can't use it in System.Boolean.Parse ?

I tried to regenerate the entire .edmx file from the database => Same exception

I tried to regenerate the entire .edmx file from scratch => Same exception

I don't understand why because I didn't modify the User model so the Active field was already there and was working just fine.

Sorry for the long post and thanks in advance.

Best regards, theCivilian

You have to tell MySql to treat tinyint as boolean, in the connection string. — Gert Arnold
@GertArnold, Thanks for the suggestion but unfortunatly it says "The 'treat tiny as boolean' keyword is not supported." Also this value is true by default according to the documentation. (I'm using MySQL-Connector 6.8.3 — tehCivilian

Rudini Rudini · Accepted Answer · 2015-07-21T09:09:35

Configure the datatype on a specific Entity:

modelBuilder.Entity<User>()
                  .Property(p => p.Active)
                  .HasColumnType("bit");

or general:

modelBuilder.Properties()
            .Where(x => x.PropertyType == typeof(bool))
            .Configure(x => x.HasColumnType("bit"));

Entity Framework MySQL tinyint(1) System.Boolean.Parse FormatException

4 Answers