Bug #62670 Entity Framework Many to Many and Eager Loading
Submitted: 10 Oct 2011 15:30 Modified: 9 Sep 2017 7:36
Reporter: Nick H Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.4.4 OS:Microsoft Windows
Assigned to: Assigned Account CPU Architecture:Any

[10 Oct 2011 15:30] Nick H
Description:
When using Entity Framework 4.1 (and possibly other versions) with MySql Connector  6.4 eager loading of a many-to-many relationship doesn't work correctly in some cases.

This works fine with SQL Server and DevArt MySql dotConnect so I am confident it's a MySql Connector bug.

Worked example below and at http://stackoverflow.com/questions/7712620/entity-framework-many-to-many-and-eager-loading

How to repeat:
Tables
----------

CabinCategory - CabinCategoryId
CabinGrade - CabinGradeId, CabinCategoryId
Deck - DeckId
DeckCabinGrades - DeckId, CabinGradeId <--- Join table

Mapping
---------

modelBuilder.Entity<CabinGrade>()
            .HasMany(c => c.Decks)
            .WithMany(d => d.CabinGrades)
            .Map(m =>
                {
                    m.MapLeftKey("CabinGradeId");
                    m.MapRightKey("DeckId");
                    m.ToTable("DeckCabinGrades");
                }
            );

Query
------

context.CabinCategories.Include("CabinGrades.Decks").ToList();

If every CabinGradeId is present in DeckCabinGrades, the query works fine.
If every CabinGradeId is not present in DeckCabinGrades, the query fails with "NullReferenceException: Object reference not set to an instance of an object."
[10 Oct 2011 15:33] Nick H
I should add I'm using Entity Framework code first
[10 Oct 2011 17:48] Nick H
Changed to Critical as "there is no available workaround". I've had to move to DevArt dotConnect for now so I can continue work.
[14 Nov 2011 9:48] Bogdan Degtyariov
Hi Nick,

Can you send the database dump for the tables you used in your example along with the data, which causes NullPointerException?
Thanks.
[15 Dec 2011 7:00] Bugs System
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".
[8 Apr 2013 8:08] Henry Zhou
Hi,

This issue is still alive in 6.6.5!! and 6.7 alpha!!
Please fix it, thanks.

Henry
[10 May 2013 5:59] Bogdan Degtyariov
As indicated in my previous comment, we need the database dump to repeat this error. If we spotted it in our test, the issue would have been fixed already.
[28 May 2013 19:30] Fernando Gonzalez.Sanchez
Can someone upload a full sample project that shows how to reproduce the problem?

Or alternatively comment in the sample test case provided below:
(otherwise I'll have to close it as not reproducible or already fixed in source).

The following test case should reproduce the issue according to the bug description, however it works just fine:

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 CabinCategory
  {
    [Key]
    public int CabinCategoryId { get; set; }
    public List<CabinGrade> CabinGrades { get; set; }
  }

  public class CabinGrade
  {
    [Key]
    public int CabinGradeId { get; set; }
    public int CabinCategoryId { get; set; }
    public virtual ICollection<Deck> Decks { get; set; }
  }

  public class Deck
  {
    [Key]
    public int DeckId { get; set; }
    public virtual ICollection<CabinGrade> CabinGrades { get; set; }
  }  

  public class CabinContext : DbContext
  {
    public DbSet<CabinCategory> CabinCategories { get; set; }
    public DbSet<CabinGrade> CabinGrades { get; set; }
    public DbSet<Deck> Decks { get; set; }    

    public CabinContext()
    {
      // This just recreates the db on each test run, otherwise can be ignored
      Database.SetInitializer(new DropCreateDatabaseAlways<CabinContext>());
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      modelBuilder.Entity<CabinGrade>()
            .HasMany(c => c.Decks)
            .WithMany(d => d.CabinGrades)
            .Map(m =>
            {
              m.MapLeftKey("CabinGradeId");
              m.MapRightKey("DeckId");
              m.ToTable("DeckCabinGrades");
            }
            );
    }
  }

}

Then using the following unit test:

/// <summary>
    /// Test for bug Entity Framework Many to Many and Eager Loading (MySql Bug #62670).
    /// </summary>
    [Fact]
    public void Many2ManyEagerLoading()
    {
      using (CabinContext ctx = new CabinContext())
      {
        ctx.Database.Initialize(true);
        CabinCategory cc = new CabinCategory() { CabinCategoryId = 1 };
        ctx.CabinCategories.Add(cc);
        CabinGrade cg = new CabinGrade() { CabinCategoryId = 1, CabinGradeId = 1 };
        CabinGrade cg2 = new CabinGrade() { CabinCategoryId = 1, CabinGradeId = 2 };
        Deck d = new Deck() { DeckId = 1 };
        Deck d2 = new Deck() { DeckId = 2 };
        ctx.CabinGrades.Add(cg);
        ctx.CabinGrades.Add(cg2);
        ctx.Decks.Add(d);
        ctx.Decks.Add(d2);
        ctx.SaveChanges();
        ctx.Entry(d).Collection("CabinGrades").Load();
        d.CabinGrades.Add(cg);
        ctx.SaveChanges();
      }
      using (CabinContext ctx = new CabinContext())
      {
        var q = ctx.CabinCategories.Include("CabinGrades").Include("CabinGrades.Decks").ToList();
        foreach (var row in q)
        {
        }
      }
    }

The code runs fine and the query returns one row as expected (this was tested in dev version of Connector/NET 6.5.6).
[28 May 2013 19:31] Fernando Gonzalez.Sanchez
See my previous comment on required feedback.
[29 Jun 2013 1:00] Bugs System
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".
[25 Jan 2017 16:58] Tim Kemp
This is still a problem in 6.9.9.  The test in the previous post is not quite correct as we want to define a many to many table WITHOUT navigation properties. The OnModelCreating should be as follows.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      modelBuilder.Entity<CabinGrade>()
            .HasMany(c => c.Decks)
            .WithMany()  // NOTE THE MISSING PARAMETER - WE DON'T NEED NAVIGATION PROPERTIES
            .Map(m =>
            {
              m.MapLeftKey("CabinGradeId");
              m.MapRightKey("DeckId");
              m.ToTable("DeckCabinGrades");
            }
            );
    }
[25 Jan 2017 18:39] Fernando Gonzalez.Sanchez
Hi, Thanks for the feedback. I am no longer in the Connector/NET Team, I will forward this to the current members..

Regards
[9 Aug 2017 7:36] Chiranjeevi Battula
Hello Tim,

Thank you for the feedback.
Could you please provide repeatable test case (exact steps/sample project, create tables statements/database etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.
[10 Sep 2017 1:00] Bugs System
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".