Pomelo.entityframeworkcore.mysql: Bi-directional one to many mapping on non-primary key results in MySqlException Unknown column 'u.CountryId' in 'field list'

Created on 27 Jan 2020 · 2Comments · Source: PomeloFoundation/Pomelo.EntityFrameworkCore.MySql

Steps to reproduce

I have a user entity and a country entity with a one to many relationship (a user has one country, a country can belong to many users). The entities both have an int primary key but are referenced through a foreign key named code which is an enum and unique.

The entities:

public class User
{
    public int Id { get; set; }
    public CountryCode CountryCode { get; set; }
    public Country Country { get; set; }
}

public class Country
{
    public int Id { get; set; }
    public CountryCode Code { get; set; }
    public IList<User> Users { get; set; }
}

public enum CountryCode
{
     AD,
     AE,
     AF,
     AG
}

The mappings:

modelBuilder.Entity<User>(entity =>
{
     entity.ToTable("User");
     entity.HasKey(e => e.Id);
     entity.Property(e => e.CountryCode).HasConversion(new EnumToStringConverter<CountryCode>());
     entity.HasOne(e => e.Country).WithMany().HasForeignKey(c => c.CountryCode).HasPrincipalKey(x => x.Code);
});

modelBuilder.Entity<Country>(entity =>
{
     entity.ToTable("Country");
     entity.HasKey(e => e.Id);
     entity.Property(e => e.Code).HasConversion(new EnumToStringConverter<CountryCode>());
     entity.HasMany(e => e.Users).WithOne().HasForeignKey(x => x.CountryCode).HasPrincipalKey(x => x.Code);
});

The issue

This setup results in a MySqlException Unknown column 'u.CountryId' in 'field list' when I try to load a user. It somehow tries to map u.CountryId on user which doesn't exist. It should be u.CountryCode only.
However, when I remove the Users property on Country and the corresponding mapping it works. Somehow it's the bi-directional mapping that causes the issue.

Exception message:
Stack trace:

Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (87ms) [CommandType='Text', CommandTimeout='30']
      SELECT `u`.`Id`, `u`.`CountryCode`, `u`.`CountryId`, 
      FROM `User` AS `u`
      LIMIT 1
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'TestApi.DatabaseContext'.
      MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'u.CountryId' in 'field list'
       ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'u.CountryId' in 'field list'
         at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs:line 774
         at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
         at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 130
         at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 391
         at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs:line 62
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
         at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'u.CountryId' in 'field list'
 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'u.CountryId' in 'field list'
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs:line 774
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 130
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 391
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs:line 62
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
      An unhandled exception has occurred while executing the request.
MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'u.CountryId' in 'field list'
 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'u.CountryId' in 'field list'
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs:line 774
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 130
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 391
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs:line 62
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Microsoft.AspNetCore.Identity.UserManager`1.FindByNameAsync(String userName)
   at Microsoft.AspNetCore.Identity.SignInManager`1.PasswordSignInAsync(String userName, String password, Boolean isPersistent, Boolean lockoutOnFailure)
   at TestApi.Controllers.UserAccountController.Login(Model model) in C:\_GIT\TestProject\TestProjectm\DotNet\TestApi\Controllers\UserAccountController.cs:line 56
   at lambda_method(Closure , Object )
   at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

Further technical details

MySQL version: 8.0.16
Operating system: App on Windows, Database on Linux (AWS)
Pomelo.EntityFrameworkCore.MySql version: 3.1.0
Microsoft.AspNetCore.App version: 3.1

Other details about my project setup:

closed-question type-question

Source

search8

All 2 comments

I was able to replicate this issue with the following code:

```c#
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Storage;

namespace IssueConsoleTemplate
{
public class User
{
public int Id { get; set; }
public CountryCode CountryCode { get; set; }
public Country Country { get; set; }
}

public class Country
{
    public int Id { get; set; }
    public CountryCode Code { get; set; }
    public IList<User> Users { get; set; }
}

public enum CountryCode
{
    AD,
    AE,
    AF,
    AG
}

public class Context : DbContext
{
    public virtual DbSet<User> Users { get; set; }
    public virtual DbSet<Country> Countries { get; set; }

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(entity =>
        {
            entity.ToTable("User");
            entity.HasKey(e => e.Id);
            entity.Property(e => e.CountryCode).HasConversion(new EnumToStringConverter<CountryCode>());
            entity.HasOne(e => e.Country).WithMany().HasForeignKey(c => c.CountryCode).HasPrincipalKey(x => x.Code);
        });

        modelBuilder.Entity<Country>(entity =>
        {
            entity.ToTable("Country");
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Code).HasConversion(new EnumToStringConverter<CountryCode>());
            entity.HasMany(e => e.Users).WithOne().HasForeignKey(x => x.CountryCode).HasPrincipalKey(x => x.Code);
        });
    }
}

internal class Program
{
    private static void Main()
    {
        // Setup:
        using (var context = new Context())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Countries.AddRange(
                new Country {Code = CountryCode.AD},
                new Country {Code = CountryCode.AE},
                new Country {Code = CountryCode.AF},
                new Country {Code = CountryCode.AG}
            );

            context.Users.AddRange(
                new User {CountryCode = CountryCode.AD},
                new User {CountryCode = CountryCode.AD},
                new User {CountryCode = CountryCode.AE},
                new User {CountryCode = CountryCode.AF}
            );

            context.SaveChanges();
        }

        // Users with countries:
        using (var context = new Context())
        {
            var users = context.Users
                .Include(u => u.Country)
                .OrderBy(u => u.CountryCode)
                .ToList();

            Debug.Assert(users.Count == 4);
            Debug.Assert(users[0].Country != null); // not working
            Debug.Assert(users[0].CountryCode == CountryCode.AD); // not working
            Debug.Assert(users[0].CountryCode == users[0].Country.Code);
        }
    }
}

}


This is not a bug, but just a model definition inconsistency in you code:

```c#
modelBuilder.Entity<User>(entity =>
{
    entity.HasOne(e => e.Country)
        .WithMany() // <-- No navigation property defined
        .HasForeignKey(u => u.CountryCode)
        .HasPrincipalKey(c => c.Code);
});

modelBuilder.Entity<Country>(entity =>
{
    entity.HasMany(e => e.Users)
        .WithOne() // <-- No navigation property defined
        .HasForeignKey(u => u.CountryCode)
        .HasPrincipalKey(c => c.Code);
});

For one entity definition, you define just one end of the mapping, for the other, just the other end.

Because of that, the model should now contain two independent navigation relationships, both configured to use the same foreign key property, which is ambiguous.

So EF Core ends up using a shadow property foreign key to the principal key instead, which does not exist, and therefore fails.

Fix

To fix you code, correct your model definition, so the foreign key is used only once:

```c#
modelBuilder.Entity(entity =>
{
entity.HasOne(e => e.Country)
.WithMany(e => e.Users) // <-- define navigation property
.HasForeignKey(u => u.CountryCode)
.HasPrincipalKey(c => c.Code);
});

modelBuilder.Entity(entity =>
{
entity.HasMany(e => e.Users)
.WithOne(e => e.Country) // <-- define navigation property
.HasForeignKey(u => u.CountryCode)
.HasPrincipalKey(c => c.Code);
});
```