TotalCount query and result incorrect when using required navigation properties

[warmfire540]

Product

Hot Chocolate

Version

13.9.0

Link to minimal reproduction

tbd

Steps to reproduce

Have a query expose IQueryable that is the result of JOINs as well as using EF Navigation properties and letting EF figure it out.

public IQueryable<ActiveRecall> GetOpenVehicleRecalls(
    [Service] MyContext myContext,
    [Service] VehicleRecallOptions options)
{
   return myContext.PsInvoiceListingLaborItems
        .Join(myContext.PsInvoiceVehs, li => li.InvoiceHeader.Id, v => v.Invid, (li, v) => new { li, v })
        .Where(p => p.li.Status != QueryConstants.CompletedStatus
            && p.li.InvoiceHeader.StatusExternal != QueryConstants.VoidedStatus
            && options.RepairCodes.Contains(p.li.RepairCode!))
        .Select(i => new ActiveRecall
        {
            ...
        }));
}

I have a nav property that produces an inner join

Here is the EF query produced

SELECT [t].[PTQuote], [t].[DateTimeIn], [p2].[Description], [t].[CompanyNumber], [t].[CompanyName], [t].[FirstName], [t].[LastName], [t].[RepairCode], [t].[RepairNumber], [p3].[ItemDescription], [p4].[Description], [t].[Complaint], [t].[Cause], [t].[Correction], [v].[Stock Number], [v].[Make], [v].[Model], [v].[Year], [v].[Primary Serial Number], [v].[On Hold], [v].[Status], CASE
    WHEN [v].[Custom6] = N'ACTIVE RECALL' AND [v].[Custom6] IS NOT NULL THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END, [t].[AdvisorNumbers], [e0].[FirstName], [e0].[LastName]
FROM (
    SELECT [p].[Cause], [p].[Complaint], [p].[Correction], [p].[ExternalLaborStatus], [p].[RepairCode], [p].[RepairNumber], [p].[RepairTypeId], [p0].[AdvisorNumbers], [p0].[CompanyName], [p0].[CompanyNumber], [p0].[DateTimeIn], [p0].[FirstName], [p0].[LastName], [p0].[PTQuote], [p0].[StatusExternal], [p1].[StockNumber]
    FROM [dbo].[PS_InvoiceLaborItems] AS [p]
    LEFT JOIN [dbo].[PS_InvoiceHeader] AS [p0] ON [p].[INVID] = [p0].[ID]
    INNER JOIN [dbo].[PS_InvoiceVeh] AS [p1] ON [p0].[ID] = [p1].[INVID]
    WHERE ([p].[Status] <> 5 OR [p].[Status] IS NULL) AND ([p0].[StatusExternal] <> 4 OR [p0].[StatusExternal] IS NULL) AND [p].[RepairCode] IN ('Recall-Final Ch', 'Fire Ext-Recall', 'Recall-refer ch', 'Recall-warr che', 'AR', 'Recall-Geni Che')
    ORDER BY (SELECT 1)
    OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY
) AS [t]
LEFT JOIN [dbo].[PS_ExternalStatuses] AS [p2] ON [t].[StatusExternal] = [p2].[Counter]
LEFT JOIN [dbo].[PS_ItemType] AS [p3] ON [t].[RepairTypeId] = [p3].[ID]
LEFT JOIN [dbo].[PS_ExternalLaborStatuses] AS [p4] ON [t].[ExternalLaborStatus] = [p4].[Counter]
--- this inner join is limiting rows as desired
INNER JOIN [dbo].[Veh_Inventory] AS [v] ON [t].[StockNumber] = [v].[Stock Number]
LEFT JOIN [dbo].[Employee] AS [e] ON [t].[AdvisorNumbers] = [e].[EmployeeNumber]
LEFT JOIN [dbo].[Entity] AS [e0] ON [e].[EntityID] = [e0].[EntityId]

What is expected?

The query to get the count should be identical to the query used to pull results in case joins are used to limit results.

i.e i'd expect this query to be produced

SELECT COUNT(1)
FROM (
    SELECT [p].[Cause], [p].[Complaint], [p].[Correction], [p].[ExternalLaborStatus], [p].[RepairCode], [p].[RepairNumber], [p].[RepairTypeId], [p0].[AdvisorNumbers], [p0].[CompanyName], [p0].[CompanyNumber], [p0].[DateTimeIn], [p0].[FirstName], [p0].[LastName], [p0].[PTQuote], [p0].[StatusExternal], [p1].[StockNumber]
    FROM [dbo].[PS_InvoiceLaborItems] AS [p]
    LEFT JOIN [dbo].[PS_InvoiceHeader] AS [p0] ON [p].[INVID] = [p0].[ID]
    INNER JOIN [dbo].[PS_InvoiceVeh] AS [p1] ON [p0].[ID] = [p1].[INVID]
    WHERE ([p].[Status] <> 5 OR [p].[Status] IS NULL) AND ([p0].[StatusExternal] <> 4 OR [p0].[StatusExternal] IS NULL) AND [p].[RepairCode] IN ('Recall-Final Ch', 'Fire Ext-Recall', 'Recall-refer ch', 'Recall-warr che', 'AR', 'Recall-Geni Che')
    -- ORDER BY (SELECT 1)
    -- OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY
) AS [t]
LEFT JOIN [dbo].[PS_ExternalStatuses] AS [p2] ON [t].[StatusExternal] = [p2].[Counter]
LEFT JOIN [dbo].[PS_ItemType] AS [p3] ON [t].[RepairTypeId] = [p3].[ID]
LEFT JOIN [dbo].[PS_ExternalLaborStatuses] AS [p4] ON [t].[ExternalLaborStatus] = [p4].[Counter]
INNER JOIN [dbo].[Veh_Inventory] AS [v] ON [t].[StockNumber] = [v].[Stock Number]
LEFT JOIN [dbo].[Employee] AS [e] ON [t].[AdvisorNumbers] = [e].[EmployeeNumber]
LEFT JOIN [dbo].[Entity] AS [e0] ON [e].[EntityID] = [e0].[EntityId]

What is actually happening?

Here is the query produced, only in the inner part of the above query is used

SELECT COUNT(*)
FROM [dbo].[PS_InvoiceLaborItems] AS [p]
LEFT JOIN [dbo].[PS_InvoiceHeader] AS [p0] ON [p].[INVID] = [p0].[ID]
INNER JOIN [dbo].[PS_InvoiceVeh] AS [p1] ON [p0].[ID] = [p1].[INVID]
WHERE ([p].[Status] <> 5 OR [p].[Status] IS NULL) AND ([p0].[StatusExternal] <> 4 OR [p0].[StatusExternal] IS NULL) AND [p].[RepairCode] IN ('Recall-Final Ch', 'Fire Ext-Recall', 'Recall-refer ch', 'Recall-warr che', 'AR', 'Recall-Geni Che')

Relevant log output

No response

Additional context

I will need to produce a repo... this may take a moment.

[glen-84]

Please add a link to a minimal reproduction of this issue.

[glen-84]

Closing, for lack of a minimal reproduction.