Nested $expand writes wrong SQL when PageSize is present on [EnableQuery]

[rwb196884]

trafficstars

I have Customer and ImageData entities and a link table CustomerImage that models a many-to-may relationship. Therefore I can use an $expand inside an $expand to get the images for a customer. For example:

/odata/customer?$top=3&$expand=CustomerImages($expand=ImageData)

This works when the controller action has the attribute [EnableQuery] but not [EnableQuery(PageSize = 100)]. It appears that different SQL is produced which doesn't even parse in the latter case.

EF model:

    public class DbContextCustomers : DbContext
    {
        public DbContextCustomers(DbContextOptions<DbContextCustomers> options) : base(options) { }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            // Configuration.
            modelBuilder.Entity<CustomerImage>().HasKey(z => new { z.CustomerID, z.ImageDataID });
            modelBuilder.Entity<CustomerImage>().HasOne(z => z.ImageData);

            modelBuilder.Entity<Customer>().HasMany<CustomerImage>(z => z.CustomerImages);
        }

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

    }

    public class Customer
    {
        [Key]
        public virtual int CustomerID { get; set; }
        public virtual int Number { get; set; }
        public virtual string Surname { get; set; }
        public virtual IEnumerable<CustomerImage> CustomerImages { get; set; }
    }

    public class ImageData
    {
        [Key]
        public virtual int ImageDataID { get; set; }
        public virtual DateTime ImageDateTime { get; set; }
        public virtual int PropertyID { get; set; }
        public virtual int? UserID { get; set; }

        // Will come out base64 encoded.
        [Column("ImageData")]// CS0542: member names cannot be the same as their enclosing type.
        public virtual byte[] Image { get; set; }
    }

    public class CustomerImage
    {
        [Key]
        public virtual int CustomerID { get; set; }

        [Key]
        public virtual int ImageDataID { get; set; }

        public virtual ImageData ImageData { get; set; }
    }

Controller:

    [Authorize(Roles = Role.RightToBeForgottenCreate)]
    [Route("odata/[controller]")]
    public class CustomerController : ControllerBase
    {
        private readonly ILogger<CustomerController> _logger;
        private readonly DbContextCustomers _DbContextCustomers;

        public CustomerController(
            ILogger<CustomerController> logger,
             DbContextCustomers dbContextCustomers
           )
        {
            _logger = logger;
            _DbContextCustomers = dbContextCustomers;
        }

        [EnableQuery] // Adding (PageSize = 100) makes expanding ImageData break.
        [HttpGet]
        public IQueryable<Customer> Get()
        {
            return _DbContextCustomers.Customer;
        }
    }

The invald query is:

SELECT [t0].[Number], [t0].[Surname], [t0].[CustomerID], [t2].[ModelID], [t2].[CustomerID], [t2].[ImageDataID], [t2].[ImageType], [t2].[UseInstanceForProperties], [t2].[Name], [t2].[ModelID0], [t2].[ImageDataID0], [t2].[ImageData], [t2].[ImageDateTime], [t2].[PropertyID], [t2].[UserID], [t2].[IsNull]
FROM (
    SELECT TOP(@__TypedProperty_6) [t].[Number], [t].[Surname], [t].[CustomerID]
    FROM (
        SELECT TOP(@__TypedProperty_5) [c].[CustomerID], [c].[Number], [c].[Surname]
        FROM [Customer] AS [c]
        ORDER BY [c].[CustomerID]
    ) AS [t]
    ORDER BY [t].[CustomerID]
) AS [t0]
OUTER APPLY (
    SELECT @__TypedProperty_3 AS [ModelID], [t].[CustomerID], [t].[ImageDataID], [t].[ImageType], CAST(1 AS bit) AS [UseInstanceForProperties], N'ImageData' AS [Name], @__TypedProperty_4 AS [ModelID0], [i].[ImageDataID] AS [ImageDataID0], [i].[ImageData], [i].[ImageDateTime], [i].[PropertyID], [i].[UserID], CAST(0 AS bit) AS [IsNull]
    FROM (
        SELECT TOP(@__TypedProperty_2) [c0].[CustomerID], [c0].[ImageDataID], [c0].[ImageType]
        FROM [CustomerImage] AS [c0]
        WHERE ([t0].[CustomerID] = [c0].[CustomerID]) AND ([c0].[ImageType] = @__TypedProperty_1)
        ORDER BY [c0].[CustomerID], [c0].[ImageDataID]
    ) AS [t1]
    INNER JOIN [ImageData] AS [i] ON [t].[ImageDataID] = [i].[ImageDataID]
) AS [t2]
ORDER BY [t0].[CustomerID], [t2].[CustomerID], [t2].[ImageDataID], [t2].[ImageDataID0]

[omnilogix]

I am experiencing the same issue

[Gigabyte0x1337]

OData only generates expression trees, check if the expression tree is correct by doing it on a static list with the same data shape. if so it's an ef issue.