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:Microsoft Windows
Assigned to: Fernando Gonzalez.Sanchez CPU Architecture:Any

[23 Mar 2012 17:47] Richard Deeming
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.
[23 Mar 2012 17:59] Richard Deeming
Sample project to reproduce the bug

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

[14 May 2012 16:18] Fernando Gonzalez.Sanchez
Thanks for your report, this fix will be released in Connector/NET versions 6.4.5 & 6.5.5.
[8 Jun 2012 13:11] Bryan Slatner
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));
                    }
                }
[8 Jun 2012 14:37] Fernando Gonzalez.Sanchez
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.
[21 Nov 2012 23:17] Fernando Gonzalez.Sanchez
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.