entity framework 6 - EF-MySQL: Multiple Include Statements with TinyInt(1) Field - Throws System.FormatException

[ad_1]

Quite a strange behavior by Entity Framework while trying to execute a query with multiple include statements! The database has been setup using code-first. All the boolean properties are created as tinyint(1) by default and everything works fine as long as the include statements are limited.

There is already an answer here. But that forces the EF to use BIT column type instead of tinyint(1). Unfortunately, the column type needs to be retained as tinyint(1) due to some constraints.

I have created a sample project where the error could be reproduced.

Model:

public abstract class Entity

 public Guid Id get; set; 

 public string Name get; set; 

 public bool IsActive get; set; 

 public Entity()
 
 this.Id = Guid.NewGuid();
 


public class Designation : Entity

 public ICollection<Employee> Employees get; set; 

 public Designation()
 
 this.Employees = new HashSet<Employee>();
 


public class Address : Entity

 public ICollection<Employee> Employees get; set; 

 public Address()
 
 this.Employees = new HashSet<Employee>();
 


public class Department : Entity

 public ICollection<Employee> Employees get; set; 

 public Department()
 
 this.Employees = new HashSet<Employee>();
 


public class Task : Entity

 public Guid EmployeeId get; set; 

 public Employee Employee get; set; 


public class Employee : Entity

 public Guid DepartmentId get; set; 

 public Department Department get; set; 

 public Guid AddressId get; set; 

 public Address Address get; set; 

 public Designation Designation get; set; 

 public ICollection<Task> Tasks get; set; 

 public Employee()
 
 this.Tasks = new HashSet<Task>();
 

Now when I run the following query, it raises the exception. If one of the include statements is commented out, the query runs to completion without any errors!

using (var context = new TestContext())
 
 var employees = context.Set<Employee>()
 .Include(it => it.Department)
 .Include(it => it.Department.Employees)
 .Include(it => it.Address)
 .Include(it => it.Designation)
 .Include(it => it.Designation.Employees)
 .Include(it => it.Tasks)
 .ToList();
 

Here's the exception & stack trace:

An unhandled exception of type 'System.FormatException' occurred in mscorlib.dll
Additional information: String was not recognized as a valid Boolean.

 at System.Boolean.Parse(String value)
 at System.String.System.IConvertible.ToBoolean(IFormatProvider provider)
 at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
 at MySql.Data.Entity.EFMySqlDataReader.ChangeType(Object sourceValue, Type targetType)
 at MySql.Data.Entity.EFMySqlDataReader.GetValue(Int32 ordinal)
 at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetUntypedValueDefault(DbDataReader reader, Int32 ordinal)
 at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal)
 at lambda_method(Closure , Shaper )
 at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly[TEntity](Func`2 constructEntityDelegate, EntityKey entityKey, EntitySet entitySet)
 at lambda_method(Closure , Shaper )
 at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
 at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.RowNestedResultEnumerator.MaterializeRow()
 at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.RowNestedResultEnumerator.MoveNext()
 at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.ObjectQueryNestedEnumerator.TryReadToNextElement()
 at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.ObjectQueryNestedEnumerator.ReadElement()
 at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.ObjectQueryNestedEnumerator.MoveNext()
 at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
 at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
 at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
 at EF.Console.Program.Main(String[] args) in C:Usersashin_000DocumentsVisual Studio 2015ProjectsConsoleApplication1EF.ConsoleProgram.cs:line 52
 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
 at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
 at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
 at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
 at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
 at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
 at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
 at System.Threading.ThreadHelper.ThreadStart()

As a work around, I had to segregate the include statements into multiple queries and run them separately, which isn't so optimal.

Please suggest a better solution! Thanks in advance!

Versions used: EF - 6.1.3 MySQL - 5.7.17 MySQL Connector - 6.9.9

[ad_2]

لینک منبع