MySQL Bugs: #68886: Problem connector.net entity framework eager load issue

Bug #68886	Problem connector.net entity framework eager load issue
Submitted:	8 Apr 2013 14:05	Modified:	1 Jul 2016 12:57
Reporter:	Henry Zhou	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.6.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have a class named Employee which has the following definition:

    public class Employee : TraceableBaseEntity
    {
        public Employee()
        {
            Contracts = new List<Contract>();
            LongLeaves = new List<LongLeave>();
            EmployeeSettlementSettings = new List<EmployeeSettlementSetting>();
        }

        [Key]
        public string BadgeNo { get; set; }
        public string Name { get; set; }
        public List<Contract> Contracts { get; set; }
        public List<LongLeave> LongLeaves { get; set; }
        public List<EmployeeSettlementSetting> EmployeeSettlementSettings { get; set; }
    }

When I use "include" to eager load Contracts,LongLeaves and EmployeeSettlementSettings as below:

            db.Employees.AsNoTracking()
                .Include(i => i.Contracts)
                .Include(i => i.LongLeaves)
                .Include(i => i.EmployeeSettlementSettings)
                .ToList();

I got strange exception "String was not recognized as a valid Boolean.", but if I only eager load 2 of them, anything will be fine. 

These codes are working fine with Microsoft SQL Server. I try to migrate database to MySQL. So I got this issue. Please help, thanks!

I also reported this bug at http://bugs.mysql.com/bug.php?id=46142&thanks=3&notify=67

How to repeat:
see description

Suggested fix:
After track the back sql using EF Profiler, I believe this issue is caused by MySQL connector Net entity framework part.

Note:  The class Employee's base class "TraceableBaseEntity" doesn't matter this issue, it is just has a little simple data members.

Thanks for your bug report,

However, I cannot reproduce this, can you provide more details on the classes Contract, LongLeave & EmployeeSettlementSetting? 

I am using the following test case and no errors happens (notice the three mentioned classes are basically placeholders in this test):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;

namespace MySql.Data.Entity.CodeFirst.Tests
{
  public class Employee
  {
        public Employee()
        {
            Contracts = new List<Contract>();
            LongLeaves = new List<LongLeave>();
            EmployeeSettlementSettings = new List<EmployeeSettlementSetting>();
        } 

        [Key]
        public string BadgeNo { get; set; }
        public string Name { get; set; }
        public List<Contract> Contracts { get; set; }
        public List<LongLeave> LongLeaves { get; set; }
        public List<EmployeeSettlementSetting> EmployeeSettlementSettings { get; set; }
  }

  public class EmployeeSettlementSetting
  {
    [Key]
    public int EmployeeSettlementSettingId { get; set; }
  }

  public class Contract
  {
    [Key]
    public int ContractId { get; set; }
  }

  public class LongLeave
  {
    [Key]
    public int LongLeaveId { get; set; }
  }

  public class EmployeeContext : DbContext
  {
    public DbSet<Employee> Employees { get; set; }

    public EmployeeContext()
    {
      Database.SetInitializer(new DropCreateDatabaseAlways<EmployeeContext>());
    }
  }
}

Then, this test rig:

/// <summary>
    /// Test for bug Problem connector.net entity framework eager load issue (MySql bug #68886).
    /// </summary>
    [Fact]
    public void EagerLoadIssue()
    {
      using (EmployeeContext ctx = new EmployeeContext())
      {
        ctx.Database.Initialize(true);
        Employee e = new Employee() { Name = "John", BadgeNo = "123333" };
        e.LongLeaves.Add(new LongLeave() { LongLeaveId = 1 });
        e.EmployeeSettlementSettings.Add(new EmployeeSettlementSetting() { EmployeeSettlementSettingId = 1 });
        e.Contracts.Add(new Contract() { ContractId = 1 });
        ctx.Employees.Add( e );
        ctx.SaveChanges();
      }
      using (EmployeeContext ctx = new EmployeeContext())
      {
        var q = ctx.Employees.AsNoTracking()
                .Include(i => i.Contracts)
                .Include(i => i.LongLeaves)
                .Include(i => i.EmployeeSettlementSettings)
                .ToList();
        foreach (var row in q)
        {
            // one row is returned here as expected
        }
      }
    }

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm having the same problem. Connector version 6.8.3?
Any idea to help me?

Hi, I get this bug with 6.8.3.0.  Let me know if you need any additional information.

Hi,

Just tried it with the following packages:

  <package id="EntityFramework" version="6.0.0" targetFramework="net451" />
  <package id="MySql.Data" version="6.9.1-beta1" targetFramework="net451" />
  <package id="MySql.Data.Entity" version="6.9.1-beta1" targetFramework="net451" />

and the problem is still exists.  Does anyone know of a work around / when this will be fixed?

Here is my workaround.  Instead of:

return await dbSet.Include(c => c.modules)
                  .Include(c => c.radeditortools)
                  .Include(c => c.radeditormodules)
                  .SingleOrDefaultAsync(c => c.id == id);

I used:

cmaeadministrator record = await dbSet.Include(c => c.modules)
                                        .Include(c => c.radeditortools)
                                        .SingleOrDefaultAsync(c => c.id == id);

await db.Entry(record).Collection("radeditormodules").LoadAsync();

return record;

Hope this helps someone!

I am also getting this issue.

I have the following query that works fine:

            var usersQuizzes = this.Context.EntitySet<Quiz>()
                                        .Include(x => x.Questions)
                                        .Include(x => x.QuizVersion)
                                        .Include(x => x.QuizPasswords)
                                        .Include(x => x.QuizVersion.Questions)
                                        .Take(20)
                                        .ToList();

But when I add either one of the navigation properties `QuizVersions` or `ResultDescriptions` as an Include(), I get the error.

So for example, this causes the error:

            var usersQuizzes = this.Context.EntitySet<Quiz>()
                                        .Include(x => x.Questions)
                                        .Include(x => x.QuizVersion)
                                        .Include(x => x.QuizPasswords)
                                        .Include(x => x.QuizVersion.Questions)
                                        .Include(x => x.QuizVersions) // Causes error
                                        .ToList();

Please see my stackoverflow question here for the models etc:

http://stackoverflow.com/questions/28685226/formatexception-when-using-multiple-include-me...

the same problem

var machines = ctx.Machines
.Include(a => a.MachineGames.Select(b => b.MachineGameSettings))
.Include(a => a.MachineGames.Select(b => b.MachineGameSymbols))
.Include(a => a.MachineGames.Select(b => b.MachineGameLines))
.Include(a => a.MachineGameActual)
.Include(a => a.MachineGameStake)                     
.ToList();

I had to remove two rows
.Include(a => a.MachineGames.Select(b => b.MachineGameSymbols))
.Include(a => a.MachineGames.Select(b => b.MachineGameLines))

and load them separately

In LINQPad 4

from au in Users
                        .Include("CoachProfile.TeamList.TeamSwimmerList.Swimmer")
                        .Include("BaseProfile.FollowedTeamList")
                        //.Include("BaseProfile.FollowedSwimmerList")
                        .Include("Swimmer")
                        //.Include("ParentProfile.SwimmerList")
                    where au.Id == "988b984f-cd82-48ed-b850-43e5e9cf7c9c"
                        select au

If I uncomment any of those, I will get "String was not recognized as a valid Boolean."

Hello Henry Zhou,

Thank you for the bug report.
This is most likely duplicate of Bug #76466, please see Bug #76466.

Thanks,
Chiranjeevi.