Queries in which a table is joined to itself throw a duplicate parameter exception

[danports]

This appears to be a regression from 1.x, as it handled these queries without a problem. I was rather surprised to encounter this issue after upgrading from 1.x since this type of query seems like a pretty common scenario. I don't think this issue is specific to the MySQL database provider since I'm guessing the parameter name generation is handled by the framework and not the providers.

Unhandled Exception: MySql.Data.MySqlClient.MySqlException: Parameter '@__user_0_Id' has already been defined.
   at MySql.Data.MySqlClient.MySqlParameterCollection.AddParameter(MySqlParameter parameter) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlParameterCollection.cs:line 158
   at MySql.Data.MySqlClient.MySqlParameterCollection.Add(Object value) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlParameterCollection.cs:line 33
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypeMappedRelationalParameter.AddDbParameter(DbCommand command, Object value)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalParameterBase.AddDbParameter(DbCommand command, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.CreateCommand(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, 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.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
   at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator`2.MoveNextCore(CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\Select.cs:line 106
   at System.Linq.AsyncEnumerable.AsyncIterator`1.MoveNext(CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\AsyncIterator.cs:line 98
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
   at System.Linq.AsyncEnumerable.Aggregate_[TSource,TAccumulate,TResult](IAsyncEnumerable`1 source, TAccumulate seed, Func`3 accumulator, Func`2 resultSelector, CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\Aggregate.cs:line 120
   at EntityFrameworkBug.Program.Main() in C:\Users\danpo\source\repos\EntityFrameworkBug\EntityFrameworkBug\Program.cs:line 43

Steps to reproduce

public class ApplicationDbContext : DbContext
{
    public DbSet<ApplicationUser> Users { get; set; }
    public DbSet<Investment> Investments { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.UseMySql("...");
    }
}

public class Investment
{
    public int Id { get; set; }
    public ApplicationUser Owner { get; set; }
    public int OwnerId { get; set; }
}

[Table("AspNetUsers")]
public class ApplicationUser
{
    public int Id { get; set; }
    public ApplicationUser Manager { get; set; }
    public int? ManagerId { get; set; }
}

public class Program
{
    public static async Task Main()
    {
        var context = new ApplicationDbContext();
        var user = await context.Users.FirstAsync();
        await context.Investments.Where(i => i.Owner == user || i.Owner.Manager == user).ToListAsync(); // Throws!
    }
}

Further technical details

EF Core version: 2.2.4
Database Provider: Pomelo.EntityFrameworkCore.MySql 2.2.0
Operating system: Windows 10
IDE: Visual Studio 2019

[danports]

It appears that it is possible to work around this problem for now by referencing the ID properties rather than the reference properties in the Where condition - i.e. await context.Investments.Where(i => i.OwnerId == user.Id || i.Owner.ManagerId == user.Id).ToListAsync() works.

[smitpatel]

Duplicate of #14645