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

Question

I am using EntityFramework 6 in my C # model-first project that uses a MySQL database. Everything was fine, and I could easily create my database.

Then I changed my .edmx file using the constructor, and here the problems that I have started started.

• At first, the developer does not update the CSDL content and CS mapping sections in the .edmx file. So I updated the content myself and was able to finally compile the project.

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

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

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

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

     à 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 .. à. Boolean.Parse (String value) à System.String.System.IConvertible.ToBoolean (IFormatProvider provider) à System.Convert.ChangeType (Object value, Type conversionType, IFormatProvider provider) à MySql.Data.Entity.EFMySqlDataReaderChangeValue , Type targetType) à MySql.Data.Entity.EFMySqlDataReader.GetValue (Int32 ordinal) à System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetUntypedValueDefault reader, Db. Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue (DbDataReader reader, Int32 ordinal) à System.Data.Entity.Core.Common.Internal.Materialization.Shaper.GetPropertyValueWithErperinalling] , Str  ing propertyName, String typeName) à lambda_method (Closure, Shaper) à System.Data.Entity.Core.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly [TEntity] (Func`2 constructEntityDelegate, EntityKey entityKey, Entity et entityambet , 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.TryReadToNextlement 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 () à Sys  tem.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 expression) à System.Data.Entity.Internal.Linq.DbQueryProvider.Execute [TResult] (Expression expression) à System.Linq.Queryable.SingleOrDefault [TSource] (IQueryable`1 source) à ArmoireOutils.Services.DataService.GetCidboard 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 .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 think that EF iterates over all the users from the database that are in cupboarduser (table linking the user with a many-to-many relationship cabinet ), and when it comes to setting Active for the first user, he gets 1 from the database, getting it as a String first and then try to parse this string in logical mode using System.Boolean .Parse , but the thaat method does not support numbers like "1" for true (the field in the database is tinyint (1) ).

So why can't EF understand that it was tinyint, so it can't use it in System.Boolean.Parse ?

I tried to restore the entire .edmx file from the database => The same exception

I tried to restore the entire .edmx file from scratch => The same exception

I do not understand why, because I did not change the User model, so the Active field was already working and is working fine.

Sorry for the long post and thanks in advance.

Regards, theCivilian

Answer 1

Set the data type for a specific object:

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

or even:

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

Answer 2

If you do this database first, just change the TINYINT (1) types to BIT (1) if you really want a boolean. You may need to update the defaults as well (for bit syntax like b'0 '). EF still translates them into logical values ​​in your entities.