EF4.1 multiple nested entity Includes gets NotSupportedException?

Question

Edit: Updated problem description based on testing - 12 Sep 2011.

I have this query that throws a NotSupportedException ("Specified method is not supported.") whenever I call .ToList().

IQueryable<FileDefinition> query = db
    .FileDefinitions
    .Include(x => x.DefinitionChangeLogs)
    .Include(x => x.FieldDefinitions.Select(y => y.DefinitionChangeLogs)) // bad
    .Include(x => x.FieldDefinitions.Select(y => y.FieldValidationTables)) // bad
    .Where(x => x.IsActive);
List<FileDefinition> retval = query.ToList();

If I comment out either line that I have commented as "bad", then the query works. I have also tried including different nested entities in my object model with the same effect. Including any 2 will cause a crash. By nested, I mean a navigation property of a navigation property. I also tried using the .Include methods with a string path: same result.

My table structure looks like this:

This is using MySQL 5.1 (InnoDB tables obviously) as the database store with MySQL Connector/NET 6.3.4.

So my question is: Why doesn't this work?

Note: I can get it to work if I explicitly load the related entities like in this link. But I want to know why EF hates my data model.

ANSWER: MySQL Connector is apparently not capable of handling the 2nd nested entity include. It throws the NotSupportedException, not .NET EF. This same error was also present when I tried this using EF4.0, but my research at the time led me to believe it was self-tracking entities causing the issue. I tried upgrading to latest Connector, but it started causing an Out of Sync error. This is yet another reason for me to hate MySQL.

Answer 1

Maybe a little late to the party but i found the following workaround fairly useful in a current project:

IQueryable<FileDefinition> query = db.FileDefinitions
    .Include(x => x.FieldDefinitions.Select(y => y.DefinitionChangeLogs.Select(z => z.FieldDefinition.FieldValidationTables)))

Where rather than using a second row of includes, use Select to get back to the original navigation property and another Select to go forwards to the property you need to include.

Answer 2

I have made a little console application to test your scenario and this test application works:

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

namespace EFIncludeTest
{
    public class Parent
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<ChildLevel1> ChildLevel1s { get; set; }
    }

    public class ChildLevel1
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<ChildLevel2a> ChildLevel2as { get; set; }
        public ICollection<ChildLevel2b> ChildLevel2bs { get; set; }
    }

    public class ChildLevel2a
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class ChildLevel2b
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<Parent> Parents { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            // Create entities to test
            using (var ctx = new MyContext())
            {
                var parent = new Parent
                {
                    Name = "Parent",
                    ChildLevel1s = new List<ChildLevel1>
                    {
                        new ChildLevel1
                        {
                            Name = "FirstChildLevel1",
                            ChildLevel2as = new List<ChildLevel2a>
                            {
                                new ChildLevel2a { Name = "FirstChildLevel2a" },
                                new ChildLevel2a { Name = "SecondChildLevel2a" }
                            },
                            ChildLevel2bs = new List<ChildLevel2b>
                            {
                                new ChildLevel2b { Name = "FirstChildLevel2b" },
                                new ChildLevel2b { Name = "SecondChildLevel2b" }
                            }
                        },

                        new ChildLevel1
                        {
                            Name = "SecondChildLevel1",
                            ChildLevel2as = new List<ChildLevel2a>
                            {
                                new ChildLevel2a { Name = "ThirdChildLevel2a" },
                                new ChildLevel2a { Name = "ForthChildLevel2a" }
                            },
                            ChildLevel2bs = new List<ChildLevel2b>
                            {
                                new ChildLevel2b { Name = "ThirdChildLevel2b" },
                                new ChildLevel2b { Name = "ForthChildLevel2b" }
                            }
                        },
                    }
                };

                ctx.Parents.Add(parent);
                ctx.SaveChanges();
            }

            // Retrieve in new context
            using (var ctx = new MyContext())
            {
                var parents = ctx.Parents
                    .Include(p => p.ChildLevel1s.Select(c => c.ChildLevel2as))
                    .Include(p => p.ChildLevel1s.Select(c => c.ChildLevel2bs))
                    .Where(p => p.Name == "Parent")
                    .ToList();

                // No exception occurs
                // Check in debugger: all children are loaded

                Console.ReadLine();
            }
        }
    }
}

My understanding was that this basically represents your model and the query you are trying (taking also your comments to your question into account). But somewhere must be an important difference which is not visible in the code snippets in your question and which makes your model fail to work.

Edit

I have tested the working console application above with MS SQL provider (SQL Server 2008 R2 Express DB), not MySQL Connector. Apparently this was the "important difference".