EF4.1 multiple nested object Enables receiving NotSupportedException?

Question

Edit: Updated test-based problem description September 12, 2011

I have this request that throws a NotSupportedException exception ("The 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 on any line that I commented as “bad,” then the query works. I also tried to include various nested objects in my object model with the same effect. Turning on any 2 will crash. By nested, I mean the navigation property of the navigation property. I also tried using .Include methods with a string path: same result.

My table structure is as follows:

It uses MySQL 5.1 (InnoDB tables, obviously) as a database repository with MySQL Connector / NET 6.3.4.

So my question is: why does this not work?

Note. I can make it work if I explicitly load related objects, for example, into this link . But I want to know why EF hates my data model.

ANSWER: The MySQL Connector is apparently not capable of handling the second nested object. It throws a NotSupportedException, not a .NET EF. The same error was also present when I tried it using EF4.0, but my research at that time led me to think that these were self-monitoring objects that caused the problem. I tried updating to the last connector, but it started to cause an error in the absence of synchronization . This is another reason for me to hate MySQL.

Answer 1

I made a small console application to test your script, 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(); } } } } 

I realized that this basically represents your model and the request you are trying (considering also your comments on your question). But somewhere there should be an important difference that is not visible in the code snippets in your question and what makes your model work.

Edit

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

Answer 2

Maybe a little late for the party, but I found the following workaround, quite useful in the current project:

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

Instead of using the second line of inclusions, use "Select" to return to the original navigation property, and another select "Select" to go to the property that you want to include.