Not able to query database using Entity Framework because of IDbAsyncEnumerable error

Question

Originally, I was getting this error:

System.InvalidOperationException occurred
HResult=-2146233079
Message=The source IQueryable doesn`t implement IDbAsyncEnumerable. Only sources that implement IDbAsyncEnumerable can be used for Entity Framework asynchronous operations. For more details see http://go.microsoft.com/fwlink/?LinkId=287068.

Source=EntityFramework

StackTrace: at System.Data.Entity.QueryableExtensions.AsDbAsyncEnumerable[T](IQueryable1 source)
at System.Data.Entity.QueryableExtensions.ToListAsync[TSource](IQueryable1 source)
at Cpr.Apps.Tam.Model.Utilities.DataSnapshot`1.d__18.MoveNext() in C:\Projects\MyApp\MyAppModel\Cpr.Apps.Tam.Model\Utilities\DataSnapshot.cs:line 160

InnerException:

So I attempt to use this code (see @Tony O'Hagan's answer in IDbAsyncEnumerable not implemented, from the page he referenced, https://msdn.microsoft.com/en-us/data/dn314429#async):

Here is the source code:

public static class AsyncQueryableExtensions
{
    public static IQueryable<TElement> AsAsyncQueryable<TElement>(this IEnumerable<TElement> source)
    {
        return new DbAsyncEnumerable<TElement>(source);
    }

    public static IDbAsyncEnumerable<TElement> AsDbAsyncEnumerable<TElement>(this IEnumerable<TElement> source)
    {
        return new DbAsyncEnumerable<TElement>(source);
    }

    public static EnumerableQuery<TElement> AsAsyncEnumerableQuery<TElement>(this IEnumerable<TElement> source)
    {
        return new DbAsyncEnumerable<TElement>(source);
    }

    public static IQueryable<TElement> AsAsyncQueryable<TElement>(this Expression expression)
    {
        return new DbAsyncEnumerable<TElement>(expression);
    }

    public static IDbAsyncEnumerable<TElement> AsDbAsyncEnumerable<TElement>(this Expression expression)
    {
        return new DbAsyncEnumerable<TElement>(expression);
    }

    public static EnumerableQuery<TElement> AsAsyncEnumerableQuery<TElement>(this Expression expression)
    {
        return new DbAsyncEnumerable<TElement>(expression);
    }
}

internal class DbAsyncQueryProvider<TEntity> : IDbAsyncQueryProvider
{
    private readonly IQueryProvider _inner;

    internal DbAsyncQueryProvider(IQueryProvider inner)
    {
        _inner = inner;
    }

    public IQueryable CreateQuery(Expression expression)
    {
        return new DbAsyncEnumerable<TEntity>(expression);
    }

    public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
    {
        return new DbAsyncEnumerable<TElement>(expression);
    }

    public object Execute(Expression expression)
    {
        return _inner.Execute(expression);
    }

    public TResult Execute<TResult>(Expression expression)
    {
        return _inner.Execute<TResult>(expression);
    }

    public Task<object> ExecuteAsync(Expression expression, CancellationToken cancellationToken)
    {
        return Task.FromResult(Execute(expression));
    }

    public Task<TResult> ExecuteAsync<TResult>(Expression expression, CancellationToken cancellationToken)
    {
        return Task.FromResult(Execute<TResult>(expression));
    }
}

internal class DbAsyncEnumerable<T> : EnumerableQuery<T>, IDbAsyncEnumerable<T>, IQueryable<T>
{
    public DbAsyncEnumerable(IEnumerable<T> enumerable)
        : base(enumerable)
    { }

    public DbAsyncEnumerable(Expression expression)
        : base(expression)
    { }

    public IDbAsyncEnumerator<T> GetAsyncEnumerator()
    {
        return new DbAsyncEnumerator<T>(this.AsEnumerable().GetEnumerator());
    }

    IDbAsyncEnumerator IDbAsyncEnumerable.GetAsyncEnumerator()
    {
        return GetAsyncEnumerator();
    }

    IQueryProvider IQueryable.Provider
    {
        get { return new DbAsyncQueryProvider<T>(this); }
    }
}

internal class DbAsyncEnumerator<T> : IDbAsyncEnumerator<T>
{
    private readonly IEnumerator<T> _inner;

    public DbAsyncEnumerator(IEnumerator<T> inner)
    {
        _inner = inner;
    }

    public void Dispose()
    {
        _inner.Dispose();
    }

    public Task<bool> MoveNextAsync(CancellationToken cancellationToken)
    {
        return Task.FromResult(_inner.MoveNext());
    }

    public T Current
    {
        get { return _inner.Current; }
    }

    object IDbAsyncEnumerator.Current
    {
        get { return Current; }
    }
}

Please note that I am getting the following error:

System.NullReferenceException was unhandled by user code HResult=-2147467261 Message=Object reference not set to an instance of an object.
Source=Anonymously Hosted DynamicMethods Assembly

StackTrace:
at lambda_method(Closure , DefectSummary )
at System.Linq.Enumerable.WhereListIterator1.MoveNext()
at System.Linq.Enumerable.<DistinctIterator>d__641.MoveNext()
at System.Linq.Buffer1..ctor(IEnumerable1 source)
at System.Linq.OrderedEnumerable1.<GetEnumerator>d__1.MoveNext()
at System.Linq.Enumerable.<DistinctIterator>d__641.MoveNext()
at System.Linq.Enumerable.d__251.MoveNext()
at MyApp.Model.Utilities.DbAsyncEnumerator1.MoveNextAsync(CancellationToken cancellationToken) in C:\Projects\MyApp\Model\Utilities\AsyncQueryableExtensions.cs:line 125
at System.Data.Entity.Infrastructure.IDbAsyncEnumerableExtensions.d__5`1.MoveNext() InnerException:

Does anyone have any suggestions to determine how I can find the null value that is causing the exception? It is occurring in this function:

    public Task<bool> MoveNextAsync(CancellationToken cancellationToken)
    {
        return Task.FromResult(_inner.MoveNext());
    }

When I check _inner, it is not clear to me which part of the variable is causing the null error:

-       _inner  {System.Linq.Enumerable.<TakeIterator>d__25<MyApp.Model.Entities.DefectSummary>}    System.Collections.Generic.IEnumerator<MyApp.Model.Entities.DefectSummary> {System.Linq.Enumerable.<TakeIterator>d__25<MyApp.Model.Entities.DefectSummary>}
+       Non-Public members      
-       Results View    Expanding the Results View will enumerate the IEnumerable   
-       Data    {System.Collections.ListDictionaryInternal} System.Collections.IDictionary {System.Collections.ListDictionaryInternal}
        Count   0   int
        IsFixedSize false   bool
        IsReadOnly  false   bool
        IsSynchronized  false   bool
-       Keys    {System.Collections.ListDictionaryInternal.NodeKeyValueCollection}  System.Collections.ICollection {System.Collections.ListDictionaryInternal.NodeKeyValueCollection}
+       Non-Public members      
-       Results View    Expanding the Results View will enumerate the IEnumerable   
        Empty   "Enumeration yielded no results"    string
        SyncRoot    {object}    object
+       Values  {System.Collections.ListDictionaryInternal.NodeKeyValueCollection}  System.Collections.ICollection {System.Collections.ListDictionaryInternal.NodeKeyValueCollection}
+       Non-Public members      
+       Results View    Expanding the Results View will enumerate the IEnumerable   
        HResult -2147467261 int
        HelpLink    null    string
+       InnerException  null    System.Exception
        Message "Object reference not set to an instance of an object." string
        Source  "Anonymously Hosted DynamicMethods Assembly"    string
        StackTrace  "   at lambda_method(Closure , DefectSummary )\r\n   at System.Linq.Enumerable.WhereListIterator`1.MoveNext()\r\n   at System.Linq.Enumerable.<DistinctIterator>d__64`1.MoveNext()\r\n   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)\r\n   at System.Linq.OrderedEnumerable`1.<GetEnumerator>d__1.MoveNext()\r\n   at System.Linq.Enumerable.<DistinctIterator>d__64`1.MoveNext()\r\n   at System.Linq.Enumerable.<TakeIterator>d__25`1.MoveNext()\r\n   at System.Linq.SystemCore_EnumerableDebugView`1.get_Items()"   string
+       TargetSite  {Boolean lambda_method(System.Runtime.CompilerServices.Closure, MyApp.Model.Entities.DefectSummary)}    System.Reflection.MethodBase {System.Reflection.Emit.DynamicMethod.RTDynamicMethod}
+       Static members      
+       Non-Public members      

UPDATE:

FYI, this is the higher level function where the query is setup:

    public IQueryable<Defect> GetLoadQuery()
    {
        IQueryable<Defect> query = null;
        // Call to stored procedure GetDefects
        var defectList = DbContext.GetDefects(null, null).ToList();
        var defects = new List<Defect>();

        using (IDefectDataService dataService = ServiceLocator.Current.GetInstance<IDefectDataService>())
        {
            foreach (var i in defectList)
            {
                dataService.DefectId = i.NotificationNo;
                NotificationLinearAsset notificationLam = dataService.GetNotificationLinearAsset();
                decimal? startPoint = notificationLam.StartPoint;
                decimal? endPoint = notificationLam.EndPoint;
                defects .Add(new Defect()
                {
                    NotificationNo = i.NotificationNo,
                    Priority = i.Priority,
                    ReportedBy = i.ReportedBy,
                    MeasuringDocument = i.MeasuringDocument,
                    AssetTypeDescription = i.AssetTypeDescription,
                    StartPoint = startPoint,
                    EndPoint = endPoint,
                    Description = i.Description,
                    AssetId = i.AssetId,
                });
            }
            query = defectSummaries.AsQueryable();
            // Other code here for filtering results...
        }

        return query.AsAsyncQueryable();
    }

UPDATE 2:

It is getting even more odd, because if I comment out these two lines, the query works and returns results:

                //StartPoint = startPoint,
                //EndPoint = endPoint,

But when I put a conditional break point in the foreach to stop if either startPoint or endPoint is null, the debugger never stops.

UPDATE 3:

Thanks for the suggestion, @Ivan Stoev. Please note that I performed another test today without changing any code other than uncommenting StartPoint/EndPoint, and it works without the error, so I'm not sure why that is yet. Perhaps something was cached in Visual Studio?

Answer 1

Resolved the problem by instead of using the stored procedure results, which creates a GetDefect_Result type, and then converting it to a Defect type to be used by the MVVM app in the above foreach loop, I instead created a view and use the results of that view which directly are directly mapped to Defect type results. This solution also involved removing all references to IDbAsyncEnumerable.

    public IQueryable<Defect> GetLoadQuery()
    {
        IDbSet<Defect> dbSet = DbContext.Defects;

        IQueryable<Defect> query = dbSet.AsQueryable();

        // Other code here for filtering results...
        return query;
    }

FYI, I had wanted to use a stored procedure because the database I'm using usually is unable to optimize a view if it has subviews, but ultimately I was able to use the view by adding more indexes to the associated tables to improve database performance.