Upgrade .NET 6 to .NET 7 query throws SqlException (0x80131904): The correlation name 'c0' is specified multiple times in a FROM clause.

[steffbeckers]

EF Core version: 7.0.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: Windows 11 22H2 (OS Build 22621.1702)
IDE: Visual Studio 2022 17.5.5

The following test case was working in .NET 6, after upgrade to .NET 7 the query translation breaks.

Project:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
	<OutputType>Exe</OutputType>
	<TargetFramework>net7.0</TargetFramework>
	<ImplicitUsings>enable</ImplicitUsings>
	<Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
	<!-- Works in .NET 6 -->
	<!--<PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.16" />
	<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.16" />
	<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.16">
	  <PrivateAssets>all</PrivateAssets>
	  <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
	</PackageReference>-->

	<!-- Doesn't work in .NET 7 -->
	<PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.5" />
	<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.5" />
	<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="7.0.5">
	  <PrivateAssets>all</PrivateAssets>
	  <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
	</PackageReference>
  </ItemGroup>

</Project>

Models:

namespace EFCoreQueryIssue;

public class Address
{
	public string? StreetAndNumber { get; set; }

	public string? PostalCode { get; set; }

	public string? City { get; set; }

	public string? Country { get; set; }
}

public class Company
{
	public Guid Id { get; set; }

	public string Name { get; set; }

	public Address? Address { get; set; }

	public bool IsDeleted { get; set; }

	public Guid? TenantId { get; set; }
}

public class Contact
{
	public Guid Id { get; set; }

	public string FirstName { get; set; }

	public string LastName { get; set; }

	public Address? Address { get; set; }

	public bool IsDeleted { get; set; }

	public Guid? TenantId { get; set; }
}

public class Customer
{
	public Guid Id { get; set; }

	public Guid? CompanyId { get; set; }

	public Guid? ContactId { get; set; }

	public bool IsDeleted { get; set; }

	public Guid? TenantId { get; set; }
}

Db context:

using Microsoft.EntityFrameworkCore;

namespace EFCoreQueryIssue;

public class EFCoreQueryIssueDbContext : DbContext
{
	public EFCoreQueryIssueDbContext()
	{
	}

	public EFCoreQueryIssueDbContext(DbContextOptions<EFCoreQueryIssueDbContext> options)
		: base(options)
	{
	}

	public DbSet<Company> Companies { get; set; }

	public DbSet<Contact> Contacts { get; set; }

	public DbSet<Customer> Customers { get; set; }

        // TODO: Edit connection string before testing
	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
		=> optionsBuilder.UseSqlServer("Server=localhost;Database=EFCoreQueryIssue;Trusted_Connection=True;TrustServerCertificate=True", options =>
		{
			options.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);
		})
		.EnableSensitiveDataLogging();

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder.Entity<Company>(b =>
		{
			b.ToTable("Companies");
			b.HasKey(x => x.Id);
			b.OwnsOne(
				x => x.Address,
				x =>
				{
					x.ToTable("CompanyAddresses");
				});
		});

		modelBuilder.Entity<Contact>(b =>
		{
			b.ToTable("Contacts");
			b.HasKey(x => x.Id);
			b.OwnsOne(
				x => x.Address,
				x =>
				{
					x.ToTable("ContactAddresses");
				});
		});

		modelBuilder.Entity<Customer>(b =>
		{
			b.ToTable("Customers");
			b.HasKey(x => x.Id);
			b.HasOne<Company>().WithMany().HasForeignKey(x => x.CompanyId);
			b.HasOne<Contact>().WithMany().HasForeignKey(x => x.ContactId);
		});
	}
}

Program.cs:

using EFCoreQueryIssue;
using Microsoft.EntityFrameworkCore;

EFCoreQueryIssueDbContext dbContext = new EFCoreQueryIssueDbContext();

await dbContext.Database.EnsureDeletedAsync();
await dbContext.Database.MigrateAsync();

Guid tenantId = Guid.Parse("661451E6-A9F1-4E30-B3B5-BDEE8EF6EAA3");

Company company1 = new Company()
{
	Id = Guid.Parse("FB21BC0D-7DB3-4779-9FE1-E95EED310D1D"),
	Name = "Test company 1 name",
	Address = new Address()
	{
		StreetAndNumber = "Test company 1 address street and number",
		City = "Test company 1 address city",
		PostalCode = "Test company 1 address postal code",
		Country = "Test company 1 address country",
	},
	TenantId = tenantId
};

Company company2 = new Company()
{
	Id = Guid.Parse("6E590B41-229B-4BDD-83D7-46FEDD69513D"),
	Name = "Test company 2 name",
	Address = new Address()
	{
		Country = "Test company 2 address country",
	},
	TenantId = tenantId
};

Company company3 = new Company()
{
	Id = Guid.Parse("4DD7185E-F013-430B-89B8-D189D5766FB3"),
	Name = "Test company 3 name",
	TenantId = tenantId
};

if (!await dbContext.Companies.AnyAsync())
{
	await dbContext.Companies.AddRangeAsync(new List<Company>()
	{
		company1,
		company2,
		company3
	});
}

Contact contact1 = new Contact()
{
	Id = Guid.Parse("28B842C6-C9C8-463C-AF26-51F0FB239A46"),
	FirstName = "Test contact 1 first name",
	LastName = "Test contact 1 last name",
	Address = new Address()
	{
		StreetAndNumber = "Test contact 1 address street and number",
		City = "Test contact 1 address city",
		PostalCode = "Test contact 1 address postal code",
		Country = "Test contact 1 address country",
	},
	TenantId = tenantId
};

Contact contact2 = new Contact()
{
	Id = Guid.Parse("E137BBEA-976A-4E0B-A3E4-4E585423E1AE"),
	FirstName = "Test contact 2 first name",
	LastName = "Test contact 2 last name",
	Address = new Address()
	{
		Country = "Test contact 2 address country",
	},
	TenantId = tenantId
};

Contact contact3 = new Contact()
{
	Id = Guid.Parse("1F6D058C-6B02-450E-8DF3-9E88B3F876BD"),
	FirstName = "Test contact 3 first name",
	LastName = "Test contact 3 last name",
	TenantId = tenantId
};

if (!await dbContext.Contacts.AnyAsync())
{
	await dbContext.Contacts.AddRangeAsync(new List<Contact>()
	{
		contact1,
		contact2,
		contact3
	});
}

Customer customer1 = new Customer()
{
	Id = Guid.Parse("3EF7ACCB-50D3-4B68-B16D-79D9DC990759"),
	CompanyId = company1.Id,
	TenantId = tenantId
};

Customer customer2 = new Customer()
{
	Id = Guid.Parse("2D127243-B450-4DB5-AB37-79853F9BD6DD"),
	CompanyId = company2.Id,
	TenantId = tenantId
};

Customer customer3 = new Customer()
{
	Id = Guid.Parse("DCD07E02-FF8D-4D6C-9B78-1576F23B26D2"),
	CompanyId = company3.Id,
	TenantId = tenantId
};

Customer customer4 = new Customer()
{
	Id = Guid.Parse("75B078B0-2C59-4654-A255-8FDE2B89A5D0"),
	ContactId = contact1.Id,
	TenantId = tenantId
};

Customer customer5 = new Customer()
{
	Id = Guid.Parse("ED750EDA-5E14-46D7-A86A-FE0762B0630A"),
	ContactId = contact2.Id,
	TenantId = tenantId
};

Customer customer6 = new Customer()
{
	Id = Guid.Parse("FCD03FB1-ABE7-457F-95C2-DD21E312CE2D"),
	ContactId = contact3.Id,
	TenantId = tenantId
};

if (!await dbContext.Customers.AnyAsync())
{
	await dbContext.Customers.AddRangeAsync(new List<Customer>()
	{
		customer1,
		customer2,
		customer3,
		customer4,
		customer5,
		customer6,
	});
}

await dbContext.SaveChangesAsync();

IQueryable<Company> companyQueryable = dbContext.Companies.AsQueryable()
	// Adding the following Where's breaks the query.
	.Where(x => x.IsDeleted == false)
	.Where(x => x.TenantId == tenantId);
IQueryable<Contact> contactQueryable = dbContext.Contacts.AsQueryable()
	.Where(x => x.IsDeleted == false)
	.Where(x => x.TenantId == tenantId);
IQueryable<Customer> customerQueryable = dbContext.Customers.AsQueryable()
	.Where(x => x.IsDeleted == false)
	.Where(x => x.TenantId == tenantId);

var customersQuery = customerQueryable.SelectMany(
	x => companyQueryable.Where(y => y.Id == x.CompanyId).DefaultIfEmpty(),
	(customer, company) => new
	{
		Customer = customer,
		Company = company
	})
	.SelectMany(
		x => contactQueryable.Where(y => y.Id == x.Customer.ContactId).DefaultIfEmpty(),
		(x, contact) => new
		{
			x.Customer,
			x.Company,
			Contact = contact
		});

// The following line triggers the exception.
var customers = await customersQuery.AsNoTracking().ToListAsync();

Console.WriteLine("Test succeeded!");

Output:

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): The correlation name 'c0' is specified multiple times in a FROM clause.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__208_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   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.SplitQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Program.<Main>$(String[] args) in C:\Dev\fuzed\EFCoreQueryIssue\EFCoreQueryIssue\Program.cs:line 182
   at Program.<Main>(String[] args)
ClientConnectionId:266ceec1-a586-4760-8d51-b56c49d1d9c6
Error Number:1011,State:1,Class:16
(process 33196) exited with code -532462766.

Make sure to update the connection string in the EFCoreQueryIssueDbContext class before testing.
I've included my test case as an console app.

EFCoreQueryIssue.zip

[roji]

Confirmed regression from 6.0 to 7.0.

Simplified repro:

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

var tenantId = Guid.Parse("661451E6-A9F1-4E30-B3B5-BDEE8EF6EAA3");

_ = await ctx.Customers
    .SelectMany(
        // Without the Where the query works
        x => ctx.Companies.Where(x => x.TenantId == tenantId),
        (customer, company) => new
        {
            Customer = customer,
            Company = company
        })
    .ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Company> Companies { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Company>().OwnsOne(
                x => x.Address,
                x => x.ToTable("CompanyAddresses")); // This ToTable is necessary for the repro
    }
}

public class Customer
{
    public Guid Id { get; set; }
    public Guid? CompanyId { get; set; }
    public Guid? ContactId { get; set; }
    public bool IsDeleted { get; set; }
    public Guid? TenantId { get; set; }
}

public class Company
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public Address? Address { get; set; }
    public bool IsDeleted { get; set; }
    public Guid? TenantId { get; set; }
}

public class Address
{
    public string? StreetAndNumber { get; set; }
    public string? PostalCode { get; set; }
    public string? City { get; set; }
    public string? Country { get; set; }
}

Resulting SQL:

SELECT [c].[Id], [c].[CompanyId], [c].[ContactId], [c].[IsDeleted], [c].[TenantId], [t].[Id], [t].[IsDeleted], [t].[Name], [t].[TenantId], [t].[CompanyId], [t].[City], [t].[Country], [t].[PostalCode], [t].[StreetAndNumber]
FROM [Customers] AS [c]
CROSS JOIN (
    SELECT [c0].[Id], [c0].[IsDeleted], [c0].[Name], [c0].[TenantId], [c0].[CompanyId], [c0].[City], [c0].[Country], [c0].[PostalCode], [c0].[StreetAndNumber]
    FROM [Companies] AS [c0]
    LEFT JOIN [CompanyAddresses] AS [c0] ON [c0].[Id] = [c0].[CompanyId]
    WHERE [c0].[TenantId] = @__tenantId_0
) AS [t]

Error:

The correlation name 'c0' is specified multiple times in a FROM clause.

@maumar I think you were investigating similar alias uniquification issues recently...

[steffbeckers]

@maumar Any idea when this issue will be fixed?

[roji]

@steffbeckers it can take us a while to investigate and fix bugs, so it's hard to say. Even when this is fixed for 8.0, it's not sure whether that fix would be backported to 7.0 - that depends on the complexity of the fix and what kind of risk it involves.

[maumar]

Looks like it works fine in current bits, likely dupe of one of the alias uniquification bugs. Current sql:

SELECT [c].[Id], [c].[CompanyId], [c].[ContactId], [c].[IsDeleted], [c].[TenantId], [t].[Id], [t].[IsDeleted], [t].[Name], [t].[TenantId], [t].[CompanyId], [t].[City], [t].[Country], [t].[PostalCode], [t].[StreetAndNumber]
FROM [Customers] AS [c]
CROSS JOIN (
    SELECT [c0].[Id], [c0].[IsDeleted], [c0].[Name], [c0].[TenantId], [c1].[CompanyId], [c1].[City], [c1].[Country], [c1].[PostalCode], [c1].[StreetAndNumber]
    FROM [Companies] AS [c0]
    LEFT JOIN [CompanyAddresses] AS [c1] ON [c0].[Id] = [c1].[CompanyId]
    WHERE [c0].[TenantId] = @__tenantId_0
) AS [t]

[maumar]

also works on current 7.0, so looks like the fix was ported

@steffbeckers you can try 7.0.7 version - fix should be available there

SELECT [c].[Id], [c].[CompanyId], [c].[ContactId], [c].[IsDeleted], [c].[TenantId], [t].[Id], [t].[IsDeleted], [t].[Name], [t].[TenantId], [t].[CompanyId], [t].[City], [t].[Country], [t].[PostalCode], [t].[StreetAndNumber]
FROM [Customers] AS [c]
CROSS JOIN (
    SELECT [c0].[Id], [c0].[IsDeleted], [c0].[Name], [c0].[TenantId], [c1].[CompanyId], [c1].[City], [c1].[Country], [c1].[PostalCode], [c1].[StreetAndNumber]
    FROM [Companies] AS [c0]
    LEFT JOIN [CompanyAddresses] AS [c1] ON [c0].[Id] = [c1].[CompanyId]
    WHERE [c0].[TenantId] = @__tenantId_0
) AS [t]