MySQL Bugs: #80707: Problem with Microsoft EF 6 and MySql

Bug #80707	Problem with Microsoft EF 6 and MySql - null reference on SqlGenerator
Submitted:	11 Mar 2016 16:16	Modified:	14 Mar 2016 6:46
Reporter:	George Domingos dos Ramos	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	.Net Connector 6.9.8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	.net Connector, EF, entityframework, SqlGenerator

Description:
Just Like this unsolved bug: Bug #74361, But i can reproduce to you guys,

I'm having a nullreference excetion on .net connector when i run this query

Linq to entities query used:

            var dengineEntities = new dengineEntities();
            
            var ativo = (bool?) true;
            
            var dsMotoristas = (from m in dengineEntities.motorista_erro
                                where m.IDEMP == 1

                                && (!ativo.HasValue || (ativo == (m.MOT_SIT_FUNC_ID == 1)))

                                orderby m.MOT_NOME
                                select new { m.MOT_NOME, m.MOT_ID });
            var proj = dsMotoristas.ToArray(); // here goes the error on projection.

Here the stack:

System.NullReferenceException

   em MySql.Data.Entity.CaseFragment.Accept(SqlFragmentVisitor visitor)
   em MySql.Data.Entity.BinaryFragment.Accept(SqlFragmentVisitor visitor)
   em MySql.Data.Entity.BinaryFragment.Accept(SqlFragmentVisitor visitor)
   em MySql.Data.Entity.BinaryFragment.Accept(SqlFragmentVisitor visitor)
   em MySql.Data.Entity.BinaryFragment.Accept(SqlFragmentVisitor visitor)
   em MySql.Data.Entity.SqlGenerator.VisitAndReplaceTableName(SqlFragment sf, String oldTable, String newTable, Dictionary`2 dicColumns)
   em MySql.Data.Entity.SqlGenerator.FuseSelectWithInnerSelect(SelectStatement outer, SelectStatement inner)
   em MySql.Data.Entity.SqlGenerator.TryFusingSelect(InputFragment f)
   em MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type)
   em MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type)
   em MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression)
   em System.Data.Entity.Core.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   em MySql.Data.Entity.SelectGenerator.GenerateSQL(DbCommandTree tree)
   em MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
   em System.Data.Entity.Core.Common.DbProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   em System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   em System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory)
   em System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateCommandDefinition(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver)
   em System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   em System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   em System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext context, DbQueryCommandTree tree)
   em System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Boolean streaming, Span span, IEnumerable`1 compiledQueryParameters, AliasGenerator aliasGenerator)
   em System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   em System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
   em System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   em System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   em System.Data.Entity.Infrastructure.DbExecutionStrategy.Execute[TResult](Func`1 operation)
   em System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   em System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   em System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   em System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   em System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   em TesteQuick.Form1.Form1_Load(Object sender, EventArgs xe) na e:\Projetos\TesteQuick\TesteQuick\Form1.cs:linha 33
   em System.Windows.Forms.Form.OnLoad(EventArgs e)
   em System.Windows.Forms.Form.OnCreateControl()
   em System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
   em System.Windows.Forms.Control.CreateControl()
   em System.Windows.Forms.Control.WmShowWindow(Message& m)
   em System.Windows.Forms.Control.WndProc(Message& m)
   em System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   em System.Windows.Forms.Form.WmShowWindow(Message& m)
   em System.Windows.Forms.Form.WndProc(Message& m)
   em System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   em System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   em System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

if i remove the order by, it works. Also if a remove "|| (ativo == (m.MOT_SIT_FUNC_ID == 1)" from the where clause, it works.

This query works on .net Connector 6.6.5, but i need to upgrade the version, but i cant with this error.

How to repeat:
Linq to entities query used:

            var dengineEntities = new dengineEntities();
            
            var ativo = (bool?) true;
            
            var dsMotoristas = (from m in dengineEntities.motorista_erro
                                where m.IDEMP == 1

                                && (!ativo.HasValue || (ativo == (m.MOT_SIT_FUNC_ID == 1)))

                                orderby m.MOT_NOME
                                select new { m.MOT_NOME, m.MOT_ID });
            var proj = dsMotoristas.ToArray(); // here goes the error on projection

Database used was a simple database with a single table:

CREATE TABLE `motorista_erro` (
  `MOT_ID` int(10) NOT NULL,
  `MOT_NOME` varchar(50) DEFAULT NULL,
  `MOT_SIT_FUNC_ID` int(10) DEFAULT NULL,
  `IDEMP` int(10) NOT NULL,
  PRIMARY KEY (`MOT_ID`,`IDEMP`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Use LinqToEntities with EF 6 to create the classes and the context.

You Will ave something like this:

  public partial class dengineEntities : DbContext
    {
        public dengineEntities()
            : base("name=dengineEntities")
        {
        }
    
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
    
        public virtual DbSet<motorista_erro> motorista_erro { get; set; }
   }

    public partial class motorista_erro
    {
        public int MOT_ID { get; set; }
        public string MOT_NOME { get; set; }
        public Nullable<int> MOT_SIT_FUNC_ID { get; set; }
        public int IDEMP { get; set; }
    }

Sorry, i mean like this bug

https://bugs.mysql.com/bug.php?id=77735

Hello George Domingos dos Ramos,

Thank you for the bug report.
Verified this behavior on Visual Studio 2013 (C#.Net) with  MySQL Connector/Net 6.9.8.

Thanks,
Chiranjeevi.

Test Name:	TestMethod1
Test FullName:	_80707.UnitTest1.TestMethod1
Test Source:	d:\Projects\80707\80707\UnitTest1.cs : line 19
Test Outcome:	Failed
Test Duration:	0:00:01.4640299

Result Message:	
Test method _80707.UnitTest1.TestMethod1 threw exception: 
System.NullReferenceException: Object reference not set to an instance of an object.
Result StackTrace:	
at MySql.Data.Entity.CaseFragment.Accept(SqlFragmentVisitor visitor)
   at MySql.Data.Entity.BinaryFragment.Accept(SqlFragmentVisitor visitor)
   at MySql.Data.Entity.BinaryFragment.Accept(SqlFragmentVisitor visitor)
   at MySql.Data.Entity.BinaryFragment.Accept(SqlFragmentVisitor visitor)
   at MySql.Data.Entity.BinaryFragment.Accept(SqlFragmentVisitor visitor)
   at MySql.Data.Entity.SqlGenerator.FuseSelectWithInnerSelect(SelectStatement outer, SelectStatement inner)
   at MySql.Data.Entity.SqlGenerator.TryFusingSelect(InputFragment f)
   at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type)
   at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type)
   at MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression)
   at System.Data.Entity.Core.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at MySql.Data.Entity.SelectGenerator.GenerateSQL(DbCommandTree tree)
   at MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory)
   at System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext context, DbQueryCommandTree tree)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Boolean streaming, Span span, IEnumerable`1 compiledQueryParameters, AliasGenerator aliasGenerator)
   at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at _80707.UnitTest1.TestMethod1() in d:\Projects\80707\80707\UnitTest1.cs:line 29

I am getting the same error. The problem seems to be with the OrderBy lambda expression. Below is the code that gives me the error. However, if I comment out the line with the OrderBy, everything works. This is a somewhat big problem for us because we want to implement paging in our API, which makes use of the .Take() and .Skip() LINQ operators. Howerver, the Take() and Skip() operators only work on a sorted result set (given to us by an OrderBy).

public IList<Document> Post(SearchDocumentsCriteria sc, int pageIndex = 0, int pageSize = 5)
        {
            using (var db = new MyDbContext())
            {
                var query = from q in db.Documents select q;

                if (sc.IsLocked != Code.BooleanFilter.Any)
                {
                    query = query.Where(x => x.IsLocked == (sc.IsLocked == Code.BooleanFilter.Yes));
                }

                if (sc.IsProcessed != Code.BooleanFilter.Any)
                {
                    query = query.Where(x => x.IsProcessed == (sc.IsProcessed == Code.BooleanFilter.Yes));
                }

                if (sc.Type.HasValue)
                {
                    query = query.Where(x => x.DocumentTypeId == sc.Type);
                }

                var results = query
                    .OrderBy(x => x.DocumentId)
                    //.Skip(1)
                    //.Take(1)
                    .ToList();
                return results;
            }
        }

Not 100% if this is applicable, the downloadable source code's Accept method on CaseFragment (SqlFragment.cs) starts with:

Else.Accept(visitor); // line approximately 284
and 5 lines later:
if(Else != null) Else.Accept(visitor);

My question - duplication? And what happened to the if on the first line?  Checked on 7.0.7