EF Query Parsing

The following table lists the time in milliseconds to execute with the NwindOrdersEdmCS.sln (or VB)

project cached Entity SQL and T - SQL queries that return the same amount of data. It ’ s reasonable for

Entity SQL queries to be slower to execute than T - SQL Queries, because T - SQL doesn ’ t require creating

and translating the CQT or handling hierarchical resultsets.

380

Text without Order Details 10 ms. 7 ms.

XML without Order Details 17 ms. 11 ms.

Text with Order Details 27 ms. 17 ms.

XML with Order Details 42 ms. 21 ms.

 

Entity SQL from ecmdNwind.CommandText

SELECT o, o.Order_Details FROM NorthwindEntities.Orders AS o

WHERE o.ShipCountry = @Country ORDER BY o.OrderDate DESC

T - SQL from ToTraceString()

SELECT

[Project1].[OrderID] AS [OrderID],

[Project1].[CustomerID] AS [CustomerID],

[Project1].[EmployeeID] AS [EmployeeID],

[Project1].[OrderDate] AS [OrderDate],

[Project1].[RequiredDate] AS [RequiredDate],

[Project1].[ShippedDate] AS [ShippedDate],

[Project1].[ShipVia] AS [ShipVia],

[Project1].[Freight] AS [Freight],

[Project1].[ShipName] AS [ShipName],

[Project1].[ShipAddress] AS [ShipAddress],

[Project1].[ShipCity] AS [ShipCity],

[Project1].[ShipRegion] AS [ShipRegion],

[Project1].[ShipPostalCode] AS [ShipPostalCode],

[Project1].[ShipCountry] AS [ShipCountry],

[Project1].[C1] AS [C1],

[Project1].[C2] AS [C2],

[Project1].[OrderID1] AS [OrderID1],

[Project1].[ProductID] AS [ProductID],

[Project1].[UnitPrice] AS [UnitPrice],

[Project1].[Quantity] AS [Quantity],

[Project1].[Discount] AS [Discount]

FROM ( SELECT

[Extent1].[OrderID] AS [OrderID],

[Extent1].[CustomerID] AS [CustomerID],

[Extent1].[EmployeeID] AS [EmployeeID],

[Extent1].[OrderDate] AS [OrderDate],

[Extent1].[RequiredDate] AS [RequiredDate],

[Extent1].[ShippedDate] AS [ShippedDate],

[Extent1].[ShipVia] AS [ShipVia],

[Extent1].[Freight] AS [Freight],

[Extent1].[ShipName] AS [ShipName],

[Extent1].[ShipAddress] AS [ShipAddress],

[Extent1].[ShipCity] AS [ShipCity],

381

[Extent1].[ShipRegion] AS [ShipRegion],

[Extent1].[ShipPostalCode] AS [ShipPostalCode],

[Extent1].[ShipCountry] AS [ShipCountry],

1 AS [C1],

[Extent2].[OrderID] AS [OrderID1],

[Extent2].[ProductID] AS [ProductID],

[Extent2].[UnitPrice] AS [UnitPrice],

[Extent2].[Quantity] AS [Quantity],

[Extent2].[Discount] AS [Discount],

CASE WHEN ([Extent2].[OrderID] IS NULL) THEN CAST(NULL AS int)

ELSE 1 END AS [C2]

FROM [dbo].[Orders] AS [Extent1]

LEFT OUTER JOIN [dbo].[Order Details] AS [Extent2]

ON [Extent1].[OrderID] = [Extent2].[OrderID]

WHERE [Extent1].[ShipCountry] = @Country

) AS [Project1]

ORDER BY [Project1].[OrderDate] DESC, [Project1].[OrderID] ASC, [Project1].[C2] ASC

Executing the preceding batch in SQL Server Management Studio sends an extraordinarily long

statement to generate a conventional LEFT OUTER JOIN that ’ s the same as ordinarily generated by:

T - SQL

SELECT o.*, d.*

FROM Orders AS o LEFT OUTER JOIN [Order Details] AS d ON d.OrderID = o.OrderID

WHERE ShipCountry = ‘USA’

ORDER BY o.OrderID DESC