Query returns "The method or operation is not implemented." error

Question

I have a Lambda expression to display a list of values from my database depending on the foreign key. The initial expression works fine

db.seasons.Where(s => s.Sport_SportID.Equals(Id)).OrderBy(a => a.Identifier).ToPagedList(pageNumber, pageSize)

However, I want to be able to search this list to narrow down my results so I've added this to my controller in order to do this

if (!string.IsNullOrEmpty(search))
{
    string[] splitSearchStr = search.Split(' ');

    return View(db.seasons.Where(s => splitSearchStr.All(t => s.Identifier.Contains(t))).Where(s => s.Sport_SportID.Equals(Id)).OrderBy(s => s.Identifier).ToPagedList(pageNumber, pageSize));
}

But whenever I perform a search which I know will return a result, I'm met with the following error

The method or operation is not implemented.

[NotImplementedException: The method or operation is not implemented.]
    MySql.Data.Entity.SelectStatement.Accept(SqlFragmentVisitor visitor) +36
    MySql.Data.Entity.ExistsFragment.Accept(SqlFragmentVisitor visitor) +30
    MySql.Data.Entity.BinaryFragment.Accept(SqlFragmentVisitor visitor) +30
    MySql.Data.Entity.SqlGenerator.FuseSelectWithInnerSelect(SelectStatement outer, SelectStatement inner) +465
    MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) +152
    MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) +19
    MySql.Data.Entity.SelectGenerator.Visit(DbLimitExpression expression) +34
    MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) +52
    MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) +19
    MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression) +77
    MySql.Data.Entity.SelectGenerator.GenerateSQL(DbCommandTree tree) +86
    MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) +502
    System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory) +686

[EntityCommandCompilationException: An error occurred while preparing the command definition. See the inner exception for details.]
    System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory) +2261
    System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext) +152
    System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext context, DbQueryCommandTree tree) +405
    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) +276
    System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption) +1188
    System.Data.Entity.Core.Objects.<>c__DisplayClass7.<GetResults>b__6() +39
    System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction(Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) +534
    System.Data.Entity.Core.Objects.<>c__DisplayClass7.<GetResults>b__5() +239
    System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +368
    System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0() +11
    System.Data.Entity.Internal.LazyEnumerator`1.MoveNext() +50
    System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +536
    System.Linq.Enumerable.ToList(IEnumerable`1 source) +80
    PagedList.PagedList`1..ctor(IQueryable`1 superset, Int32 pageNumber, Int32 pageSize) +526
    PagedList.PagedListExtensions.ToPagedList(IQueryable`1 superset, Int32 pageNumber, Int32 pageSize) +85
    MVC_CMS.Areas.Admin.Controllers.SeasonsController.Index(Nullable`1 id, String search, String slug) in c:\Websites\MVC-CMS\MVC-CMS\Areas\Admin\Controllers\SeasonsController.cs:43
    lambda_method(Closure , ControllerBase , Object[] ) +220
    System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +242
    System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +39
    System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState) +12
    System.Web.Mvc.Async.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult) +139
    System.Web.Mvc.Async.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() +112
    System.Web.Mvc.Async.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41() +452
    System.Web.Mvc.Async.<>c__DisplayClass33.<BeginInvokeActionMethodWithFilters>b__32(IAsyncResult asyncResult) +15
    System.Web.Mvc.Async.<>c__DisplayClass2b.<BeginInvokeAction>b__1c() +37
    System.Web.Mvc.Async.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult) +241
    System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) +29
    System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +111
    System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +53
    System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +19
    System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__5(IAsyncResult asyncResult, ProcessRequestState innerState) +51
    System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +111
    System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +606
    System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +288

But if the search returns no results, no error appears... what's the problem with this and how can I go about getting it to work?

Answer 1

According to Bug #70722 as mentioned by Alex.Ritna, the problem is caused by the use of All() and OrderBy().

The source code for the SelectStatement class includes this:

internal override void Accept(SqlFragmentVisitor visitor)
{
  throw new System.NotImplementedException();
}

From what I gather the Accept() method is involved in query optimisation. It looks like the functionality is incomplete.

Unfortunately there aren't any comments in the source code that I can see to fully explain this method or when it might be implemented. With any luck a MySql developer could shed some light on this.

You might have to look for a different way of executing your logic.

A side note: One alternative is that, since the method returns nothing (void), you could simply alter the source code to not throw an exception and continue on. I have no idea what the consequences of this would be, however.