Pomelo.entityframeworkcore.mysql: System.InvalidOperationException: The provider for the source IQueryable doesn't implement IDbAsyncQueryProvider.

[zaneclaes]

Steps to reproduce

I'm wondering if I missed something or did something wrong.

The issue

I came to this package after first discovering MySqlConnector while looking for an async/await approach to DB queries in my dotnetcore 3.1 API. However, I still get the same error as when running MySql.Data.EntityFrameworkCore:

await DbContext.Users.FirstAsync(u => u.Id == userId);

Causes:

System.InvalidOperationException: The provider for the source IQueryable doesn't implement IDbAsyncQueryProvider.

If this is intended, is the expectation that the thread/connection pool(s) obviate the need for async functions? That is to say, if two APIs are executing concurrently, they will be on different threads using different connections.

Further technical details

MySQL version: 5.7
Operating system: Ubuntu
Pomelo.EntityFrameworkCore.MySql version: 3.2.1
Microsoft.AspNetCore.App version: 3.1.8

Other details about my project setup:

services.AddDbContextPool<OWSData>(options => {
        options.UseMySql(connStr, mySqlOptions => {
          mySqlOptions.ServerVersion(new Version(5, 7, 0), ServerType.MySql)
                      .MigrationsAssembly(typeof(OWSData).Assembly.FullName);
        }).EnableSensitiveDataLogging().EnableDetailedErrors();
      });

[lauxjpn]

Pomelo should (and does) support async methods without any known issues. From the code provided, I don't see any issues (though it would be interesting to know, why you explicitly need to specify the MigrationsAssembly()).

@zaneclaes Please provide us with some kind of fully functional project, so we can dive deeper into this. (You can also start by posting the project file content (csproj file) of the failing project, while you prepare a reproducable project.)

---

Oracle's implementation should usually also work for simple queries like the one you have shown (though with less performance than MySqlConnector). So the fact that even their implementation throws in the same way is a very strong indication that the issues lies in your code, project setup (e.g. referenced packages) or environment.

In case you want to troubleshoot this on your own, I would start by making sure, that you are only using the officially supported package reference versions (instead of the newest ones available).

[mguinness]

@zaneclaes Below is a working example that you can base your issue on.

```C#
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System;
using System.Threading.Tasks;

namespace IssueConsoleTemplate
{
class Program
{
static async Task Main(string[] args)
{
using var context = new Context();

        await context.Database.EnsureDeletedAsync();
        await context.Database.EnsureCreatedAsync();

        var item = await context.IceCreams.FirstAsync(i => i.IceCreamId == 1);

        Console.WriteLine($"Flavor is {item.Name}");
    }
}

public class IceCream
{
    public int IceCreamId { get; set; }
    public string Name { get; set; }
}

public class Context : DbContext
{
    public DbSet<IceCream> IceCreams { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseMySql(
                "server=127.0.0.1;port=3306;user=root;password=;database=test",
                b => b.ServerVersion("8.0.11-mysql"))
            .UseLoggerFactory(
                LoggerFactory.Create(
                    b => b
                        .AddConsole()
                        .AddFilter(level => level >= LogLevel.Information)))
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<IceCream>(
            entity =>
            {
                entity.HasData(
                    new IceCream
                    {
                        IceCreamId = 1,
                        Name = "Vanilla",
                    });
            });
    }
}

}
```

[zaneclaes]

Wow, that was interesting. Turns out, my IDE imported the FirstAsync from System.Data.Entity.QueryableExtensions.

The bad news is I spent all day because of that.

The good news is that it is what led me to switch from Oracle :)

[lauxjpn]

@zaneclaes Thanks for taking the time to report what the actual issue was, since this might happen to somebody else in the future as well!