NotImplementedException when calling ToList()

What I'm seeing is that if I call ToList() on the product of a LINQ query, I'm getting a NotImplementedException being thrown from somewhere. I've got a MySQL DB and each table has a foreign key relationship (no tables are isolated). Also, I've turned off the lazy loading of back- and forward references, though I hadn't done that until I had already run into the exception.

This is an example of one of the calls that would fail, though it'll happen even on simple from-select (select all) queries:

using (TsiModelUnitOfWork unitOfWork = DataContext.Context.CreateUnitOfWork())
{
  return (from set in unitOfWork.Operations where set.Type == type && !set.IsDeleted orderby set.Date descending select set).Take(limit).ToList();
}

This is the stack trace I get from the exception on the above call; doesn't seem too helpful, though maybe you guys might see something of note:

   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
   at Mindscape.LightSpeed.Linq.ProjectionStrategy.ExecuteEntityQuery(Query query, IUnitOfWork unitOfWork)
   at Mindscape.LightSpeed.Linq.ProjectionStrategy.Execute(Query query, IUnitOfWork unitOfWork)
   at Mindscape.LightSpeed.Linq.LinqQueryExecutionPlan.Execute()
   at Mindscape.LightSpeed.Linq.LinqQueryTranslator.Translate(Expression expression, IUnitOfWork unitOfWork, String viewName)
   at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression)
   at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression)
   at Mindscape.LightSpeed.Linq.LinqQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
[...snip call to ToList() and above...]

One thing that seems to work is working on unitOfWork.Operations.AsEnumerable(), though I'm afraid in that case it's going to have to get all the entries in the table, and some of the tables can get rather long.

Are there any good tips or hints on tracking down the root cause and hopefully bypassing it? It seems to be fairly straightforward code, but of course from being in software myself, things can get complicated quickly behind the scenes. And I can especially see entities making things rather complicated, though they should also be fairly common, so it's hard to say.

Thanks for the detailed information.  In this example you give, this appears to be because our LINQ provider does not (yet) handle the ! operator.  If you change this to set.IsDeleted == false then all should be well.

You shouldn't be seeing a NotImplementedException on straight select-all queries, though.  Could you provide an example of such a query that is failing?  Is the underlying exception definitely a NotImplementedException rather than, say, a database exception?

In terms of diagnosing problems like this, there are a couple of things you can try:

1. Check the inner exception.  LINQ tends to generate queries dynamically, so you often end up with a TargetInvocationException which is rather uninformative!  Drilling down a layer or two can be more informative.  (As a corollary, if you end up posting here, it's handy if you can include the inner exception and its stack trace.)

2. Try translating the query into a "traditional" LightSpeed query and running it using IUnitOfWork.Find.  This can help to isolate whether it is a database problem, a LightSpeed problem or a LINQ problem.

3. If the error seems to be coming from the database (e.g. there's a MySqlException lurking near the bottom of the trace), you can turn on logging (LightSpeedContext.Logger = new ConsoleLogger()) to see the generated SQL.

Thanks for the quick response, Ivan. Like I said, seemed like a simple enough query, but things often get complicated behind the scenes. I made that change and the place where that particular call was failing is working now. Awesome. :)

As for the straight select, it's possible that it was a different issue, as I did see more than just the NotImplementedException in my attempts. I was converting the project from using LINQ To SQL on a SQL Server DB to using MySQL, so it's entirely possible that the DB wasn't completely settled and/or I was still doing something the SQL provider way. Seeing as how removing the ! usage made this call work, I'd be surprised if I had indeed gotten a NotImplementedException for the straight select, so that should be good. If I run into it again, then I'll make sure and check.

And thanks for the tips on diagnosing issues (and what could be helpful to post). InnerException is definitely a good property to look at, since many exceptions you see end up having something else as the root exception. In my case, I'm in a WPF app, so I always have to check the inner exception (this one was around 4 levels deep, for example). I'll have to keep in mind the logging capability, should I run across a DB exception where it doesn't provide the SQL. :)