ASP.NET Core Web API从MySQL数据库获取数据返回Null问题求助

Hey there! Let's walk through some practical fixes to troubleshoot the null data issue you're facing when querying MySQL with Pomelo EF Core in your ASP.NET Core Web API.

1. Fix the Obvious Typo First #

Looking at your controller code, you have a typo in the method call:

var someoneNinja = _repoWrapper.Ninja.FinfByCondition(x => x.Name.Equals("Matej ninja"));

That should be FindByCondition (you missed a 'd'). A misspelled method name will almost certainly result in null (or even a runtime error if the method doesn't exist). Double-check your repository interface and implementation to ensure the method name is consistent everywhere.

2. Verify Your DbContext Configuration #

Your ConfigureMySqlContext extension method needs to correctly set up the Pomelo provider and connection string. Make sure it looks something like this:

public static void ConfigureMySqlContext(this IServiceCollection services, IConfiguration config)
{
    var connectionString = config["mysqlconnection:connectionString"];
    services.AddDbContext<ApplicationDbContext>(options => 
        options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString)));
}

Also, confirm your ApplicationDbContext includes the required DbSet properties for your entities:

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }

    public DbSet<Ninja> Ninja { get; set; }
    public DbSet<Clan> Clan { get; set; }
}

MySQL is case-sensitive by default on most systems, so if your database tables are lowercase (e.g., ninja, clan), add the [Table] attribute to your entities to match:

[Table("ninja")]
public class Ninja { /* ... */ }

[Table("clan")]
public class Clan { /* ... */ }

3. Check Your Repository Implementation #

Your RepositoryWrapper and underlying repositories need to properly execute queries against the DbContext. For example, the FindAll method in your Clan repository should materialize the query (not just return an IQueryable):

public class ClanRepository : IClanRepository
{
    private readonly ApplicationDbContext _context;

    public ClanRepository(ApplicationDbContext context)
    {
        _context = context ?? throw new ArgumentNullException(nameof(context));
    }

    public IEnumerable<Clan> FindAll()
    {
        // Use ToList() to execute the query immediately
        return _context.Clan.ToList();
    }
}

Also, ensure your RepositoryWrapper correctly initializes the repository instances:

public class RepositoryWrapper : IRepositoryWrapper
{
    private readonly ApplicationDbContext _context;
    private IClanRepository _clan;
    private INinjaRepository _ninja;

    public RepositoryWrapper(ApplicationDbContext context)
    {
        _context = context;
    }

    public IClanRepository Clan
    {
        get
        {
            return _clan ??= new ClanRepository(_context);
        }
    }

    public INinjaRepository Ninja
    {
        get
        {
            return _ninja ??= new NinjaRepository(_context);
        }
    }
}

4. Validate Your MySQL Connection and Data #

• Double-check your appsettings.json connection string: confirm the server is localhost (correct if MySQL runs locally), the username/password are valid, and the ninjas database exists.
• Use MySQL Workbench (or another client) to connect with the same credentials and run manual queries:

  SELECT * FROM clan;
  SELECT * FROM ninja WHERE name = 'Matej ninja';
  

  If these queries return no results, the issue isn't with your code—it's that there's no data in your tables to fetch.

5. Ensure Entities Have Primary Keys #

EF Core requires entities to have a primary key to track and query them correctly. Add the [Key] attribute to your entity classes:

public class Ninja
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    // Other properties
}

public class Clan
{
    [Key]
    public int Id { get; set; }
    public string ClanName { get; set; }
    // Other properties
}

Without a primary key, EF Core won't recognize the entity properly, leading to empty or null query results.

6. Enable Logging to Debug the Query #

Add EF Core logging to see exactly what SQL is being generated. Update your DbContext configuration in ConfigureMySqlContext:

services.AddDbContext<ApplicationDbContext>(options => 
    options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString))
           .LogTo(Console.WriteLine, LogLevel.Information));

Run your API, trigger the endpoint with Postman, and check the console for the generated SQL. Copy that SQL and run it directly in MySQL to see if it returns data—this will help you pinpoint if the issue is with the query logic or data availability.

Content的提问来源于stack exchange，提问作者Nevera