Optimize include queries to not rerun original query

[Rora]

When including relations I noticed that the original query that was used to fetch the parent is ran again when retrieving the child relations. I would've expected that the child relations would be selected by navigating the parent-child relation.

Also by rerunning the original query when retrieving the included relations we risk missing rows when the result of the original query differs on the include query. This could for example be caused by an update that was executed between the two queries by another process.

Steps to reproduce

Using a DB schema:

Run the following query

_orderContext
    .Orders
    .Where(o => o.CustomerId = customerId)
    .Include(o => o.OrderRows)
    .ToList();

This results in the following SQL

-- initial query
SELECT [o].[OrderId], [o].[CustomerId]
FROM [Order] AS [o]
WHERE ([o].[CustomerId] = @__g_0)
ORDER BY [o].[OrderId]

-- Include query
SELECT [o.OrderRows].[OrderRowId], [o.OrderRows].[OrderId], [o.OrderRows].[ProductId]
FROM [OrderRow] AS [o.OrderRows]
INNER JOIN (
    SELECT [o0].[OrderId], [o0].[CustomerId]
	FROM [Order] AS [o0]
	WHERE ([o0].[CustomerId] = @__g_0)
	ORDER BY [o0].[OrderId]
) AS [t] ON [o.OrderRows].[OrderId] = [t].[OrderId]
ORDER BY [t].[OrderId]

I would suggest passing the PK values from the parent as parameters for the inlcude query. This would (most of the time) result in a faster query. (In this example I used a XML type to pass an array of ids, there are multiple alternatives):

-- Include query
DECLARE @ParentIds XML = '<OrderIds><OrderId>1</OrderId><OrderId>2</OrderId></OrderIds>'

SELECT [o.OrderRows].[OrderRowId], [o.OrderRows].[OrderId], [o.OrderRows].[ProductId]
FROM [OrderRow] AS [o.OrderRows]
INNER JOIN (SELECT o0.OrderId.value('.', 'INT') FROM @ParentIds.nodes('/OrderIds/OrderId') as o0(OrderId)) as orderIds(OrderId) ON orderIds.OrderId = [o.OrderRows].OrderId
ORDER BY [orderIds].[OrderId]

Further technical details

Example DB schema

/****** Object:  Table [dbo].[Customer]    Script Date: 16-11-2018 10:49:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
	[CustomerId] [int] NOT NULL,
	[Name] [nchar](10) NOT NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Order]    Script Date: 16-11-2018 10:49:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Order](
	[OrderId] [int] IDENTITY(1,1) NOT NULL,
	[CustomerId] [int] NOT NULL,
 CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
(
	[OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[OrderRow]    Script Date: 16-11-2018 10:49:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderRow](
	[OrderRowId] [int] IDENTITY(1,1) NOT NULL,
	[OrderId] [int] NOT NULL,
	[ProductId] [int] NOT NULL,
 CONSTRAINT [PK_OrderRow] PRIMARY KEY CLUSTERED 
(
	[OrderRowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_Order_Customer] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([CustomerId])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Customer]
GO
ALTER TABLE [dbo].[OrderRow]  WITH CHECK ADD  CONSTRAINT [FK_OrderRow_Order] FOREIGN KEY([OrderId])
REFERENCES [dbo].[Order] ([OrderId])
GO
ALTER TABLE [dbo].[OrderRow] CHECK CONSTRAINT [FK_OrderRow_Order]
GO

[ajcvickers]

@smitpatel to find duplicate.

[smitpatel]

Duplicate of #12776