OData / OData/AspNetCoreOData

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

Open
#62 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
505
Forks
186
PR merge metrics
No merged PRs in 30d

Description

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]

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start at the CustomerController.Get entry point and compare the nested $expand behavior with [EnableQuery] versus [EnableQuery(PageSize = 100)] using the supplied EF model. Reproduce the generated SQL and inspect the OUTER APPLY section, where the shown query references an alias that is not defined. Done means the PageSize variant produces valid SQL and returns the nested ImageData results.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
api, backend
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.