How can I use Entity Framework on an object graph past a depth of 2 with MySQL Connector / NET?

Question

Here is a confirmed bug report with Oracle: http://bugs.mysql.com/bug.php?id=67183

Situation

When using an .Include chain inside of my repository, I noticed that I was getting strange results - mostly that the values queried that were being returned were from the wrong fields (name would end up in description for example - but in the database all the values are correct, they only show up wrong after the query). I changed the names so the relationships are more obvious, but the structure is the same. I keep getting the wrong values for the associated CrewMember and their relative Rank and Clearance. It seems if there is a field name which is the same in CrewMember as Rank, then the value of that field in Rank becomes what the value was in CrewMember. For example, if Rank had a description, and so did CrewMember, then the description of Rank for the CrewMember would be the CrewMember's description.

Entity Framework fails to make well formed queries past a depth of 2 when there are similar fields defined as a result of the MySQL Connector/NET sql provider failing to properly form join statements.

Definitions

This is a class definition which models a database table. I am using C# ASP.NET MVC 3 with the Entity Framework 4.1 and the MySQL Connector/NET version 6.5

public class Harbor
{
 public int HarborId { get; set; }
 public virtual ICollection<Ship> Ships { get; set; }
 public string Description { get; set; }
}

public class Ship
{
 public int ShipId { get; set; }
 public int HarborId { get; set; }
 public virtual Harbor Harbor { get; set; }
 public virtual ICollection<CrewMember> CrewMembers { get; set; }
 public string Description { get; set; }
} 

public class CrewMember
{
 public int CrewMemberId { get; set; }
 public int ShipId { get; set; }
 public virtual Ship Ship { get; set; }
 public int RankId { get; set; }
 public virtual Rank Rank { get; set; }
 public int ClearanceId { get; set; }
 public virtual Clearance Clearance { get; set; }
 public string Description { get; set; }
}

public class Rank
{
 public int RankId { get; set; }
 public virtual ICollection<CrewMember> CrewMembers { get; set; }
 public string Description { get; set; }
}

public class Clearance
{
 public int ClearanceId { get; set; }
 public virtual ICollection<CrewMember> CrewMembers { get; set; }
 public string Description { get; set; }
}

Query

This is the code which queries the database and has the query and .Include calls.

DbSet<Harbor> dbSet = context.Set<Harbor>();
IQueryable<Harbor> query = dbSet;
query = query.Include(entity => entity.Ships);
query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers));
query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Rank)));
query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Clearance)));

Are these .Include calls well formed? Did I miss something?

This is rather complex, so if you have any questions please let me know in comments and I will try to clarify anything I may have left out.

How can I use Entity Framework to get a well formed query on an object graph past a depth of 2 when using MySQL Connector / NET?

Edits

Here is the generated query:

{SELECT
[Project1].[HarborId], 
[Project1].[Description], 
[Project1].[C2] AS [C1], 
[Project1].[ShipId], 
[Project1].[HarborId1], 
[Project1].[Description1], 
[Project1].[C1] AS [C2], 
[Project1].[CrewMemberId], 
[Project1].[ShipId1], 
[Project1].[ClearanceId], 
[Project1].[RankId], 
[Project1].[Description2], 
[Project1].[RankId1], 
[Project1].[Description3], 
[Project1].[ClearanceId1], 
[Project1].[Description4], 
FROM (SELECT
[Extent1].[HarborId], 
[Extent1].[Description], 
[Join3].[ShipId], 
[Join3].[HarborId] AS [HarborId1], 
[Join3].[Description]AS [Description1], 
[Join3].[CrewMemberId], 
[Join3].[ShipId]AS [ShipId1], 
[Join3].[ClearanceId], 
[Join3].[RankId], 
[Join3].[Description] AS [Description2], 
[Join3].[RankId] AS [RankId1], 
[Join3].[Description] AS [Description3], 
[Join3].[ClearanceId] AS [ClearanceId1], 
[Join3].[Description] AS [Description4], 
CASE WHEN ([Join3].[ShipId] IS  NULL) THEN (NULL)  WHEN ([Join3].[CrewMemberId] IS  NULL) THEN (NULL)  ELSE (1) END AS [C1], 
CASE WHEN ([Join3].[ShipId] IS  NULL) THEN (NULL)  ELSE (1) END AS [C2]
FROM [Harbor] AS [Extent1] LEFT OUTER JOIN (SELECT
[Extent2].[ShipId], 
[Extent2].[HarborId], 
[Extent2].[Description], 
[Join2].[CrewMemberId], 
[Join2].[ShipId] AS [ShipID1], 
[Join2].[ClearanceId], 
[Join2].[RankId], 
[Join2].[Description] AS [DESCRIPTION1], 
[Join2].[RankID1], 
[Join2].[DESCRIPTION1] AS [DESCRIPTION11], 
[Join2].[ClearanceID1], 
[Join2].[DESCRIPTION2], 
FROM [Ship] AS [Extent2] LEFT OUTER JOIN (SELECT
[Extent3].[CrewMemberId], 
[Extent3].[ShipId], 
[Extent3].[ClearanceId], 
[Extent3].[RankId], 
[Extent3].[Description], 
[Extent4].[RankId] AS [RankID1], 
[Extent4].[Description] AS [DESCRIPTION1], 
[Extent5].[ClearanceId] AS [ClearanceID1], 
[Extent5].[Description] AS [DESCRIPTION2], 
FROM [CrewMember] AS [Extent3] INNER JOIN [Rank] AS [Extent4] ON [Extent3].[RankId] = [Extent4].[RankId] LEFT OUTER JOIN [Clearance] AS [Extent5] ON [Extent3].[ClearanceId] = [Extent5].[ClearanceId]) AS [Join2] ON [Extent2].[ShipId] = [Join2].[ShipId]) AS [Join3] ON [Extent1].[HarborId] = [Join3].[HarborId]
 WHERE [Extent1].[HarborId] = @p__linq__0) AS [Project1]
 ORDER BY 
[Project1].[HarborId] ASC, 
[Project1].[C2] ASC, 
[Project1].[ShipId] ASC, 
[Project1].[C1] ASC}

Clarification

Using include on 1-1 relationships poses no problem when "drilling down" in this fashion it seems. However, the issue seems to arise when there are 1-many relations as part of the drilling. The drilling is necessary in order to eager load.

The first projection, entity => entity.Ships.Select(s => s.CrewMembers, will return a list of CrewMembers which are related to each ship. This properly returns the graph where a harbor contains a list of ships, each with a list of crew members.

However, the second projection CrewMembers.Select(cm => cm.Rank, does not in fact return the proper piece of the graph. Fields begin to be mixed, and any fields sharing the same name will default for whatever reason to the parent field. This results in inconsistent results and more importantly bad data. The fact that no errors are thrown makes it worse, as this can only be determined through runtime inspection.

If there were a way to somehow get a strongly typed single response (as opposed to a list) from the first projection, perhaps the second would not be necessary. As it is now, I believe that the issue lies in the first projection returning a list. When the second projection attempts to project based on that list instead of from a single object, the logical error is introduced.

If, instead of CrewMembers being an ICollection, it was only one CrewMember, then this nested projection will in fact return the correct data. However, that is a simplified version of this problem and unfortunately it is what almost all testing seems to have been done on from the various blogs, tutorials, posts, articles, and documents which I reviewed trying to solve this issue.

Answer 1

query.Include(entity => entity.Ships);
query.Include(entity => entity.Ships.Select(s => s.CrewMembers));
query.Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Rank)));
query.Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Clearance)));

First of all, you know it has to be query = query.Include(...).Include(...), right?

As long as you are executing the last 2, you don't need the first 2. Both Ships and CrewMembers will be loaded from the second 2. Have you tried just this?

//query.Include(entity => entity.Ships);
//query.Include(entity => entity.Ships.Select(s => s.CrewMembers));
query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Rank)))
    .Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Clearance)));

Also, you can always fire up sql profiler to see exactly what query ef is sending to the db. I wouldn't expect a bug that would swap property values from different objects in the graph if you only run the 3rd and 4th Includes.

Answer 2

Edit

The test below was made with SQL Server and SqlClient as provider. The fact that the problem is not reproducable with SQL Server raises the question if the MySql provider you are using has a bug in that is creates incorrect SQL for your LINQ query. It looks like the same problem as in this question where the problem occured with a MySql provider as well and couldn't be reproduced with SqlClient/SQL Server.

---

I keep getting the wrong values for the associated CrewMember and their relative Rank and Clearance. It seems if there is a field name which is the same in CrewMember as Rank, then the value of that field in Rank becomes what the value was in CrewMember. For example, if Rank had a description, and so did CrewMember, then the description of Rank for the CrewMember would be the CrewMember's description.

I have tested the example in bold (with EF 4.3.1) and can't reproduce the problem:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;

namespace EFInclude
{
    public class Harbor
    {
        public int HarborId { get; set; }
        public virtual ICollection<Ship> Ships { get; set; }

        public string Description { get; set; }
    }

    public class Ship
    {
        public int ShipId { get; set; }
        public int HarborId { get; set; }
        public virtual Harbor Harbor { get; set; }
        public virtual ICollection<CrewMember> CrewMembers { get; set; }

        public string Description { get; set; }
    }

    public class CrewMember
    {
        public int CrewMemberId { get; set; }
        public int ShipId { get; set; }
        public virtual Ship Ship { get; set; }
        public int RankId { get; set; }
        public virtual Rank Rank { get; set; }
        public int ClearanceId { get; set; }
        public virtual Clearance Clearance { get; set; }

        public string Description { get; set; }
    }

    public class Rank
    {
        public int RankId { get; set; }
        public virtual ICollection<CrewMember> CrewMembers { get; set; }

        public string Description { get; set; }
    }

    public class Clearance
    {
        public int ClearanceId { get; set; }
        public virtual ICollection<CrewMember> CrewMembers { get; set; }

        public string Description { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<Harbor> Harbors { get; set; }
        public DbSet<Ship> Ships { get; set; }
        public DbSet<CrewMember> CrewMembers { get; set; }
        public DbSet<Rank> Ranks { get; set; }
        public DbSet<Clearance> Clearances { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>());

            using (var context = new MyContext())
            {
                context.Database.Initialize(true);

                var harbor = new Harbor
                {
                    Ships = new HashSet<Ship>
                    {
                        new Ship
                        {
                            CrewMembers = new HashSet<CrewMember>
                            {
                                new CrewMember
                                {
                                    Rank = new Rank { Description = "Rank A" },
                                    Clearance = new Clearance { Description = "Clearance A" },
                                    Description = "CrewMember A"
                                },
                                new CrewMember
                                {
                                    Rank = new Rank { Description = "Rank B" },
                                    Clearance = new Clearance { Description = "Clearance B" },
                                    Description = "CrewMember B"
                                }
                            },
                            Description = "Ship AB"
                        },
                        new Ship
                        {
                            CrewMembers = new HashSet<CrewMember>
                            {
                                new CrewMember
                                {
                                    Rank = new Rank { Description = "Rank C" },
                                    Clearance = new Clearance { Description = "Clearance C" },
                                    Description = "CrewMember C"
                                },
                                new CrewMember
                                {
                                    Rank = new Rank { Description = "Rank D" },
                                    Clearance = new Clearance { Description = "Clearance D" },
                                    Description = "CrewMember D"
                                }
                            },
                            Description = "Ship CD"
                        }
                    },
                    Description = "Harbor ABCD"
                };

                context.Harbors.Add(harbor);
                context.SaveChanges();
            }

            using (var context = new MyContext())
            {
                DbSet<Harbor> dbSet = context.Set<Harbor>();
                IQueryable<Harbor> query = dbSet;
                query = query.Include(entity => entity.Ships);
                query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers));
                query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Rank)));
                query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers.Select(cm => cm.Clearance)));

                var sqlString = query.ToString();
                // see below for the generated SQL query

                var harbor = query.Single();

                Console.WriteLine("Harbor {0} Description = \"{1}\"",
                    harbor.HarborId, harbor.Description);
                foreach (var ship in harbor.Ships)
                {
                    Console.WriteLine("- Ship {0} Description = \"{1}\"",
                        ship.ShipId, ship.Description);
                    foreach (var crewMember in ship.CrewMembers)
                    {
                        Console.WriteLine("-- CrewMember {0} Description = \"{1}\"", 
                            crewMember.CrewMemberId, crewMember.Description);
                        Console.WriteLine("-- CrewMember {0} Rank Description = \"{1}\"",
                            crewMember.CrewMemberId, crewMember.Rank.Description);
                        Console.WriteLine("-- CrewMember {0} Clearance Description = \"{1}\"",
                            crewMember.CrewMemberId, crewMember.Clearance.Description);
                    }
                }

                Console.ReadLine();
            }
        }
    }
}

The output is:

According to your description in bold I should have: CrewMember 1 Description = "Rank A" and the same mess for the other 3 crew members. But I haven't this.

Is something different in my test program compared to your code where you have the error?

Edit

The generated SQL for the query (see line var sqlString = query.ToString(); in source code above, the following is the content of sqlString) is:

SELECT 
[Project1].[HarborId] AS [HarborId], 
[Project1].[Description] AS [Description], 
[Project1].[C2] AS [C1], 
[Project1].[ShipId] AS [ShipId], 
[Project1].[HarborId1] AS [HarborId1], 
[Project1].[Description1] AS [Description1], 
[Project1].[C1] AS [C2], 
[Project1].[CrewMemberId] AS [CrewMemberId], 
[Project1].[ShipId1] AS [ShipId1], 
[Project1].[RankId] AS [RankId], 
[Project1].[ClearanceId] AS [ClearanceId], 
[Project1].[Description2] AS [Description2], 
[Project1].[RankId1] AS [RankId1], 
[Project1].[Description3] AS [Description3], 
[Project1].[ClearanceId1] AS [ClearanceId1], 
[Project1].[Description4] AS [Description4]
FROM ( SELECT 
    [Extent1].[HarborId] AS [HarborId], 
    [Extent1].[Description] AS [Description], 
    [Join3].[ShipId1] AS [ShipId], 
    [Join3].[HarborId] AS [HarborId1], 
    [Join3].[Description1] AS [Description1], 
    [Join3].[CrewMemberId] AS [CrewMemberId], 
    [Join3].[ShipId2] AS [ShipId1], 
    [Join3].[RankId1] AS [RankId], 
    [Join3].[ClearanceId1] AS [ClearanceId], 
    [Join3].[Description2] AS [Description2], 
    [Join3].[RankId2] AS [RankId1], 
    [Join3].[Description3] AS [Description3], 
    [Join3].[ClearanceId2] AS [ClearanceId1], 
    [Join3].[Description4] AS [Description4], 
    CASE WHEN ([Join3].[ShipId1] IS NULL) THEN CAST(NULL AS int) WHEN ([Join3].[CrewMemberId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
    CASE WHEN ([Join3].[ShipId1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
    FROM  [dbo].[Harbors] AS [Extent1]
    LEFT OUTER JOIN  (SELECT [Extent2].[ShipId] AS [ShipId1], [Extent2].[HarborId] AS [HarborId], [Extent2].[Description] AS [Description1], [Join2].[CrewMemberId], [Join2].[ShipId2], [Join2].[RankId1], [Join2].[ClearanceId1], [Join2].[Description2], [Join2].[RankId2], [Join2].[Description3], [Join2].[ClearanceId2], [Join2].[Description4]
        FROM  [dbo].[Ships] AS [Extent2]
        LEFT OUTER JOIN  (SELECT [Extent3].[CrewMemberId] AS [CrewMemberId], [Extent3].[ShipId] AS [ShipId2], [Extent3].[RankId] AS [RankId1], [Extent3].[ClearanceId] AS [ClearanceId1], [Extent3].[Description] AS [Description2], [Extent4].[RankId] AS [RankId2], [Extent4].[Description] AS [Description3], [Extent5].[ClearanceId] AS [ClearanceId2], [Extent5].[Description] AS [Description4]
            FROM   [dbo].[CrewMembers] AS [Extent3]
            INNER JOIN [dbo].[Ranks] AS [Extent4] ON [Extent3].[RankId] = [Extent4].[RankId]
            LEFT OUTER JOIN [dbo].[Clearances] AS [Extent5] ON [Extent3].[ClearanceId] = [Extent5].[ClearanceId] ) AS [Join2] ON [Extent2].[ShipId] = [Join2].[ShipId2] ) AS [Join3] ON [Extent1].[HarborId] = [Join3].[HarborId]
)  AS [Project1]
ORDER BY [Project1].[HarborId] ASC, [Project1].[C2] ASC, [Project1].[ShipId] ASC, [Project1].[C1] ASC

Answer 3

As it stands, it is not possible to retrieve the graph in one trip with EF when using the MySQLConnector/NET. See this confirmed bug report with Orcale. What must be done is to

DbSet<Harbor> dbSet = context.Set<Harbor>();
IQueryable<Harbor> query = dbSet;
query = query.Include(entity => entity.Ships.Select(s => s.CrewMembers));
var Harbor = query.ToList();
foreach (var S in Harbor.Ships)
{
 foreach (var CM in S.CrewMembers)
 {
  CM.Rank = //get Rank where RankId == CM.RankId
  CM.Clearance = //get Clearance where ClearanceId == CM.ClearanceId
 }
}

This code is in line with the example, but is obviously just as an example and would need better implementation to actually run. This is the approach I am using until I can overload or improve on the .Include EF functionality in order to get the whole graph in one trip.

Getting the data in multiple trips is not ideal, however, it works.