MySQL Bugs: #68898: Detecting Db Joins incorrectly in Entity Framework

Bug #68898	Detecting Db Joins incorrectly in Entity Framework
Submitted:	9 Apr 2013 8:18	Modified:	29 May 2013 14:33
Reporter:	Aron Tsang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.6.4	OS:	Any
Assigned to:	Roberto Ezequiel Garcia Ballesteros	CPU Architecture:	Any
Tags:	entity framework

Description:
The entity framework parser REQUIRES that the linq expression includes the DbContext/ObjectContext, when joining. This is a serious bug as it makes it impossible to abstract away the Database. Correct usage of EntityFramework should allow the calling code to work directly with the IQueryable/DbSet/ObjectSet and join correctly.

How to repeat:
class Program
    {
        static void Main(string[] args)
        {
            using (var db = new Context())
            {
                var set = db.Foos;

                var query = from x in set
                            join y in set on x.Id equals y.Id + 1
                            select new {x, y};
                var bars = query.ToList();

                var workingQuery = from x in db.Foos
                                   join y in db.Foos on x.Id equals y.Id + 1
                                   select new {x, y};
            }
        }
    }

    public class Context : DbContext
    {
        public DbSet<Foo> Foos { get; set; }

        static Context()
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<Context>());
        }

    }

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

At present, the example code will do a in memory join for the first query, and do a database join for the second query.

Dear Aron,

I was testing your code and couldn’t find anything wrong with it.

As you can see here, both queries have the same result and are executed on database to get data. This is the database log after execute your code:

		 4391 Init DB	tests
		 4391 Query	SELECT
`Extent1`.`Id`, 
`Extent1`.`Name`, 
`Extent2`.`Id` AS `Id1`, 
`Extent2`.`Name` AS `Name1`
FROM `Foos` AS `Extent1` INNER JOIN `Foos` AS `Extent2` ON `Extent1`.`Id` = (`Extent2`.`Id` + 1)
		 4391 Init DB	tests
		 4391 Query	SELECT
`Extent1`.`Id`, 
`Extent1`.`Name`, 
`Extent2`.`Id` AS `Id1`, 
`Extent2`.`Name` AS `Name1`
FROM `Foos` AS `Extent1` INNER JOIN `Foos` AS `Extent2` ON `Extent1`.`Id` = (`Extent2`.`Id` + 1)

Could you please provide the Entity Framework, .NET Framework and Visual Studio versions you are using?

Thank you.

Apologies...

Seems I misdiagnosed the issue.
Reran the code, you are right in that it works...

The problem query is... 

var query = from x in set
                            from y in set 
                            where x.Id == y.Id + 1
                            select new { x, y };

I suspect from the exception that the somewhere along the way one of the sets are being converted to an ObjectSet for some reason...

Whilst it works with,

                var workingQuery = from x in db.Foos
                                   from y in db.Foos 
                                   where x.Id == y.Id + 1
                                   select new { x, y };

Just so you know, not all inner joins can be joined using the join syntax. Since I need to use the .StartsWith operation as opposed to .Equals operation.

Hi Aron,

I tested the code and it throws the exception. Unfortunately this is an Entity Framework issue and is out of our scope because we only implement the provider for MySql but you can report the issue in the Entity Framework codeplex page here:

http://entityframework.codeplex.com/workitem/list/basic

A workaround for this issue is to define the variable as queryable:

var set = db.Foos.AsQueryable();

Regards.