.netCore Upgrade from 2.2 -> 3.0 has caused using Include to throw exceptions

[nimion]

Issue Title

.netCore Upgrade from 2.2 -> 3.0 has caused using Include to throw exceptions

General

I am having an issue with a piece of code that was working in 2.2 but now throws on 3.0 in that iterating over an IQueryable that was constructed with .Include causes an exception.

Statement:

IQueryable<Transaction> transactions = _dbContext.Transactions
		.Include(i => i.User)
		.Include(i => i.Client)
		.Where(a => a.Timestamp >= start && a.Timestamp <= end && a.Status == TransactionStatus.Failed && a.Client.ClientCode == clientCode);

Exception:

The LINQ expression 'LeftJoin<Transaction, Client, string, TransparentIdentifier<Transaction, Client>>(
outer: DbSet,
inner: DbSet,
outerKeySelector: (b) => Property(b, "Clientid"),
innerKeySelector: (c) => Property(c, "id"),
resultSelector: (o, i) => new TransparentIdentifier<Transaction, Client>(
Outer = o,
Inner = i
))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

To isolate the problem I removed and tried various combinations of the query statement:

Removed the Where clause:

	 IQueryable<Transaction> transactions = _dbContext.Transactions
		.Include(i => i.User)
		.Include(i => i.Client);

Verdict: Exception

The LINQ expression 'LeftJoin<Transaction, User, string, TransparentIdentifier<Transaction, User>>(
outer: DbSet,
inner: DbSet,
outerKeySelector: (b) => Property(b, "Userid"),
innerKeySelector: (p) => Property(p, "id"),
resultSelector: (o, i) => new TransparentIdentifier<Transaction, User>(
Outer = o,
Inner = i
))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Removed the Where clause & Client Include

	IQueryable<Transaction> transactions = _applicationDbContext.Transactions
		.Include(i => i.Player);

Verdict: Exception

The LINQ expression 'LeftJoin<Transaction, User, string, TransparentIdentifier<Transaction, User>>(
outer: DbSet,
inner: DbSet,
outerKeySelector: (b) => Property(b, "Userid"),
innerKeySelector: (p) => Property(p, "id"),
resultSelector: (o, i) => new TransparentIdentifier<Transaction, User>(
Outer = o,
Inner = i
))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Removed Where clause & User Include

	IQueryable<Transaction> transactions = _applicationDbContext.Transactions
		.Include(i => i.Client);

Verdict: Exception

The LINQ expression 'LeftJoin<Transaction, Client, string, TransparentIdentifier<Transaction, Client>>(
outer: DbSet,
inner: DbSet,
outerKeySelector: (b) => Property(b, "Clientid"),
innerKeySelector: (c) => Property(c, "id"),
resultSelector: (o, i) => new TransparentIdentifier<Transaction, Client>(
Outer = o,
Inner = i
))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Removed both .Includes and added the Where clause back, but modified to not check for clientCode

	IQueryable<Transaction> transactions = _applicationDbContext.Transactions
		.Where(a => a.Timestamp >= start && a.Timestamp <= end && a.Status == TransactionStatus.Failed);

Veridct: WORKS

Conclusion
The issue seems isolated to the .Include chaining. I tried searching all the documents on 3.0s release that I could for any changes to how .Include works but my search yielded no results. I have a hard time believing this is was an uncaught defect with 3.0 as .Includes are a pretty important feature, and there may have been some underlying framework changes that make the original use of .Include in my code erroneous.

What would I be doing wrong in 3.0 that would cause this?

Below are my Transaction, Client, and User classes

public class Transaction
    {
        [Key]
        public string id { get; set; }
        public DateTime Timestamp { get; set; }
        public string ErrorMessage { get; set; } = string.Empty;
        public decimal Amount { get; set; }
        public User User { get; set; }
        public Client Client { get; set; }
        public Site Site { get; set; }
        public string Status { get; set; }
        public string Type { get; set; }
	}
	
public class Player
    {
        [Key]
        public string id { get; set; }
        public string ClientDefinedAccountNumber { get; set; } = string.Empty;
        public DateTime CreationDate { get; set; } = DateTime.MinValue;
        public string CurrencyCode { get; set; } = string.Empty;
        public string CountryCode { get; set; } = string.Empty;
    }

public class Client
    {
        [Key]
        public string id { get; set; }
        public string ClientCode { get; set; } = string.Empty;
        public string FriendlyName { get; set; } = string.Empty;
        public string CurrencyCode { get; set; } = string.Empty;
    }

[davidfowl]

cc @smitpatel

[smitpatel]

Filed dotnet/efcore#18130 to track on EF Core repo.