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

[smitpatel]

From @nimion at dotnet/core#3495 (comment)

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;
    }

[smitpatel]

@nimion - I cannot reproduce the error you are seeing. The repro code I used

using System;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

namespace EFSampleApp
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (var db = new MyContext())
            {
                // Recreate database
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                // Seed database


                db.SaveChanges();
            }

            using (var db = new MyContext())
            {
                // Run queries
                var start = DateTime.Now;
                var end = DateTime.Now;
                var clientCode = "Client";
                IQueryable<Transaction> transactions = db.Transactions
                    .Include(i => i.User)
                    .Include(i => i.Client)
                    .Where(a => a.Timestamp >= start && a.Timestamp <= end && a.Status == "Failed" && a.Client.ClientCode == clientCode);

                transactions.ToList();
            }
            Console.WriteLine("Program finished.");
        }
    }


    public class MyContext : DbContext
    {
        private static ILoggerFactory ContextLoggerFactory
            => LoggerFactory.Create(b =>
            {
                b
                .AddConsole()
                .AddFilter("", LogLevel.Debug);
            });

        // Declare DBSets
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Transaction> Transactions { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // Select 1 provider
            optionsBuilder
                .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;Connect Timeout=5;ConnectRetryCount=0")
                //.UseSqlite("filename=_modelApp.db")
                //.UseInMemoryDatabase(databaseName: "_modelApp")
                //.UseCosmos("https://localhost:8081", @"C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==", "_ModelApp")
                .EnableSensitiveDataLogging()
                .UseLoggerFactory(ContextLoggerFactory);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure model
        }
    }

    public class Blog
    {
        public int Id { get; set; }
    }

    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;
    }

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

    public class Site
    {
        public int Id { get; set; }
    }
}

[nimion]

Interesting.
The only other difference I see here is that you're using the SqlServer data provider.
We're using the new Cosmos data provider that was released - I should've noted that in my original post but it slipped my mind.

[smitpatel]

Duplicate of #16920