The multi-part identifier "o.Id" could not be bound

[craig-wagner]

We are using .NET Core 3.1 with EF Core 3.1.3.

I have the following LINQ query. This is a scaled down version of the full query that still illustrates the issue.

var foo = await _primaryRepository.Orders
    .Where(o => o.ExternalReferenceId == referenceId && o.CustomerId == CustomerId)
    .Select(o => new
    {
        IdentityDocuments = o.IdentityDocuments.Select(id => new
        {
            Images = o.IdentityDocuments
                .SelectMany(id => id.Images)
                .Select(i => new
                {
                    DocumentSide = i.DocumentSide,
                    Image = i.Image
                }),
        })
    }).SingleOrDefaultAsync(ct);

It is generating the following SQL.

exec sp_executesql N'SELECT [t].[Id], [t1].[Id], [t1].[DocumentSide], [t1].[Image], [t1].[Id0], [t1].[Id00]
FROM (
    SELECT TOP(2) [o].[Id]
    FROM [Orders] AS [o]
    WHERE ([o].[ExternalReferenceId] = @__referenceId_0) AND ([o].[CustomerId] = @__CustomerId_1)
) AS [t]
LEFT JOIN (
    SELECT [i].[Id], [t0].[DocumentSide], [t0].[Image], [t0].[Id] AS [Id0], [t0].[Id0] AS [Id00], [i].[OrderId]
    FROM [IdentityDocuments] AS [i]
    OUTER APPLY (
        SELECT [i1].[DocumentSide], [i1].[Image], [i0].[Id], [i1].[Id] AS [Id0]
        FROM [IdentityDocuments] AS [i0]
        INNER JOIN [IdentityDocumentImages] AS [i1] ON [i0].[Id] = [i1].[IdentityDocumentId]
        WHERE [o].[Id] = [i0].[OrderId]  -- < this line is causing the problem
    ) AS [t0]
) AS [t1] ON [t].[Id] = [t1].[OrderId]
ORDER BY [t].[Id], [t1].[Id], [t1].[Id0], [t1].[Id00]',N'@__referenceId_0 nvarchar(100),@__CustomerId_1 uniqueidentifier',@__referenceId_0=N'20200424081335',@__CustomerId_1='DFACC852-D449-417B-5254-08D7C4717E98'

The problem is that the WHERE clause in the OUTER APPLY is referencing [o] but the scope of [o] is the subselect inside the origin FROM clause. Basically, that WHERE clause has no idea what [o] is referring to.

My (minimal) entity definitions are shown below. I had to manually hack out all the unrelated cruft. I'm pretty sure I didn't hack out too much but let me know if something is missing.

    public class Order
    {
        private ICollection<IdentityDocument> _identityDocuments;

        public Guid Id { get; set; }

        public Guid CustomerId { get; set; }

        public string ExternalReferenceId { get; set; }

        public ICollection<IdentityDocument> IdentityDocuments
        {
            get => _identityDocuments = _identityDocuments ?? new Collection<IdentityDocument>();
            set => _identityDocuments = value;
        }
    }

    public class IdentityDocument
    {
        private ICollection<IdentityDocumentImage> _images;

        public Guid Id { get; set; }

        [ForeignKey(nameof(Order))]
        public Guid OrderId { get; set; }

        public Order Order { get; set; }

        public ICollection<IdentityDocumentImage> Images
        {
            get => _images = _images ?? new Collection<IdentityDocumentImage>();
            set => _images = value;
        }
    }

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

        [ForeignKey(nameof(IdentityDocument))]
        public Guid IdentityDocumentId { get; set; }

        public eDocumentSide DocumentSide { get; set; }

        public byte[] Image { get; set; }

        public IdentityDocument IdentityDocument { get; set; }
    }

Any suggestions or assistance would be greatly appreciated. I'm happy to try anything that anyone might suggest.

I should also add that our unit tests use an in-memory SQLite database to actually exercise the query (yes, I know, that makes them integration tests, not unit tests). They also fail but with a different error message (although I suspect it's for the same reason).

Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'near "(": syntax error'.

I haven't had a chance to try to extract the generated SQL statement but will update this issue once I've been able to do so.

[joakimriedel]

Workaround: Try ToListAsync() instead of SingleOrDefaultAsync() (perhaps related #17809)

[craig-wagner]

I'll give the workaround a shot and report back.

I was able to get the SQLite query that is failing as well. The error that it is reporting is:

Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'near "(": syntax error'.

SELECT "o"."Id", "t0"."Id", "t0"."DocumentSide", "t0"."Image", "t0"."Id0", "t0"."Id00"
FROM "Orders" AS "o"
OUTER APPLY (
    SELECT "i"."Id", "t"."DocumentSide", "t"."Image", "t"."Id" AS "Id0", "t"."Id0" AS "Id00"
    FROM "IdentityDocuments" AS "i"
    OUTER APPLY (
        SELECT "i1"."DocumentSide", "i1"."Image", "i0"."Id", "i1"."Id" AS "Id0"
        FROM "IdentityDocuments" AS "i0"
        INNER JOIN "IdentityDocumentImages" AS "i1" ON "i0"."Id" = "i1"."IdentityDocumentId"
        WHERE "o"."Id" = "i0"."OrderId"
    ) AS "t"
    WHERE "o"."Id" = "i"."OrderId"
) AS "t0"
WHERE ("o"."ExternalReferenceId" = @__referenceId_0) AND ("o"."CustomerId" = @__CustomerId_1)
ORDER BY "o"."Id", "t0"."Id", "t0"."Id0", "t0"."Id00"

[craig-wagner]

Workaround: Try ToListAsync() instead of SingleOrDefaultAsync() (perhaps related #17809)

The workaround solved the issue for SQL Server and Azure SQL but SQLite still throws the same exception.

[maumar]

APPLY is not supported on sqlite. #19178 tracks the improvement of the exception message and #20608 tracks translation improvement for some scenarios

[maumar]

another related issue: #20505