Welcome to the home of the Hot Chocolate GraphQL server for .NET, the Strawberry Shake GraphQL client for .NET and Banana Cake Pop the awesome Monaco based GraphQL IDE.
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.
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
JOIN
s as well as using EF Navigation properties and letting EF figure it out.I have a nav property that produces an inner join
Here is the EF query produced
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
What is actually happening?
Here is the query produced, only in the inner part of the above query is used
Relevant log output
No response
Additional context
I will need to produce a repo... this may take a moment.