MySQL Bugs: #64749: Entity Framework - Take().Count() fails with EntityCommandCompilationException

Bug #64749	Entity Framework - Take().Count() fails with EntityCommandCompilationException
Submitted:	23 Mar 2012 17:47	Modified:	21 Nov 2012 23:17
Reporter:	Richard Deeming	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.5.4	OS:	Windows
Assigned to:	Fernando Gonzalez.Sanchez	CPU Architecture:	Any

Description:
I have an Entity Framework Code First query which uses ".Take(0)" to return no data under certain circumstances. The control which consumes the query is calling LongCount to get the total number of records.

The query works with MSSQL and SQLCE. With MySql, the query fails with a "System.Data.EntityCommandCompilationException".

If I replace the ".Take(0)" with a ".Where(x => false)", the query compiles and executes as expected.

While investigating the issue, I discovered that any sequence of ".Take(n)" followed by Count or LongCount will result in the error.

System.Data.EntityCommandCompilationException was unhandled by user code
Message=An error occurred while preparing the command definition. See the inner exception for details.
  at System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory, DbCommandTree)
  at System.Data.EntityClient.EntityProviderServices.CreateCommandDefinition(DbProviderFactory, DbCommandTree)
  at System.Data.EntityClient.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest, DbCommandTree)
  at System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree)
  at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Prepare(ObjectContext, DbQueryCommandTree, Type, MergeOption, Span, ReadOnlyCollection`1)
  at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1)
  at System.Data.Objects.ObjectQuery.ToTraceString()
  at System.Data.Entity.Internal.Linq.InternalQuery`1.ToString()
  at System.Data.Entity.Infrastructure.DbQuery`1.ToString()
  at (my code)

InnerException: System.ArgumentNullException
Message=Value cannot be null.
Parameter name: key
  at System.Collections.Generic.Dictionary`2.FindEntry(TKey)
  at System.Collections.Generic.Dictionary`2.ContainsKey(TKey)
  at MySql.Data.Entity.Scope.GetFragment(String)
  at MySql.Data.Entity.SelectStatement.AddDefaultColumns(Scope)
  at MySql.Data.Entity.SelectStatement.Wrap(Scope)
  at MySql.Data.Entity.SelectGenerator.WrapJoinInputIfNecessary(InputFragment, Boolean)
  at MySql.Data.Entity.SelectGenerator.HandleJoinExpression(DbExpressionBinding, DbExpressionBinding, DbExpressionKind, DbExpression)
  at MySql.Data.Entity.SelectGenerator.Visit(DbJoinExpression)
  at System.Data.Common.CommandTrees.DbJoinExpression.Accept[TResultType](DbExpressionVisitor`1)
  at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression, String, TypeUsage)
  at MySql.Data.Entity.SelectGenerator.HandleJoinExpression(DbExpressionBinding, DbExpressionBinding , DbExpressionKind, DbExpression)
  at MySql.Data.Entity.SelectGenerator.Visit(DbJoinExpression)
  at System.Data.Common.CommandTrees.DbJoinExpression.Accept[TResultType](DbExpressionVisitor`1)
  at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression, String, TypeUsage)
  at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression, String, TypeUsage)
  at MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression)
  at System.Data.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1)
  at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression, String, TypeUsage)
  at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression, String, TypeUsage)
  at MySql.Data.Entity.SelectGenerator.Visit(DbSortExpression)
  at System.Data.Common.CommandTrees.DbSortExpression.Accept[TResultType](DbExpressionVisitor`1)
  at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression, String, TypeUsage)
  at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression, String, TypeUsage)
  at MySql.Data.Entity.SelectGenerator.Visit(DbLimitExpression)
  at System.Data.Common.CommandTrees.DbLimitExpression.Accept[TResultType](DbExpressionVisitor`1)
  at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression, String, TypeUsage)
  at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression, String, TypeUsage)
  at MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression)
  at System.Data.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1)
  at MySql.Data.Entity.SelectGenerator.GenerateSQL(DbCommandTree)
  at MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest, DbCommandTree)
  at System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree)
  at System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory, DbCommandTree)

How to repeat:
public class Person
{
   [Key]
   public virtual Guid Id { get; set; }
}

public class TestContext : DbContext
{
   public DbSet<Person> People
   {
      get { return Set<Person>(); }
   }
}

using (var context = new TestContext())
{
   // Works:
   foreach (var person in context.People.Take(0))
   {
      Console.WriteLine(person.Id);
   }
   
   // Fails:
   context.People.Take(0).Count();
   context.People.Take(0).LongCount();
   context.People.Take(10).Count();
   context.People.Take(10).LongCount();
}

Suggested fix:
The connector should support a Count or LongCount call after a Take call. In the case of ".Take(0)", it should return 0; in all other cases, it should return the minimum of the number of matching records and the parameter passed to the Take method.

Sample project to reproduce the bug

Attachment: MySql-TakeCountBug.zip (application/zip, text), 4.03 KiB.

Thanks for your report, this fix will be released in Connector/NET versions 6.4.5 & 6.5.5.

I have a very similar problem when querying a table with a call to .Includes().

I've discovered that, in the example shown in the post, if you remove the call to .Take(), the query runs just fine. 

Example:

                var groupsAndGroupUsers = (from o in context.Groups.Include("GroupUsers")
                              select o);

                foreach (var group in groupsAndGroupUsers)
                {
                    Debug.WriteLine(string.Format("GroupName: {0}", group.Name));
                    foreach (var groupUser in group.GroupUsers)
                    {
                        Debug.WriteLine(string.Format("\tUser: {0}", groupUser.UserId));
                    }
                }

Correct Bryan, and the fix also applies for .Include scenarios
We are about to release a 6.6 alpha which will also include the fix, 1-2 weeks from now.

This was fixed in Connector/NET versions 6.4.5, 6.5.5, 6.6.0.

With the fix there's no longer an exception.