From Rob’s post, I decided to double-check how you would transform his ‘semi-complex query’ into EF syntax and see what the resulting SQL was.
His initial query was:
var results - DynamicModel.Open(connectionString).Query(@"
SELECT Orders.OrderNumber, Categories.Name FROM Products
INNER JOIN Categories ON Categories.CategoryID = Products.CategoryID
INNER JOIN OrderItems ON OrderItems.ProductID = Products.ID
INNER JOIN Orders ON Orders.OrderID = OrderItems.OrderID
WHERE Orders.OrderDate > @0", DateTime.Now.AddYears(-1));
Now, I have no idea what database he was hitting against. To make it easy, I decided to run it against our BFF Northwind, and so I had to reformulate it as follows:
SELECT Orders.OrderDate, Categories.CategoryName FROM Products
INNER JOIN Categories
ON Categories.CategoryID = Products.CategoryID
INNER JOIN [Order Details]
ON [Order Details].ProductID = Products.ProductID
INNER JOIN Orders
ON Orders.OrderID = [Order Details].OrderID
order by orders.OrderID, Categories.CategoryName
Since it wasn’t relevant, I left off the the where clause (our BFF Northwind has order dates from 1996, etc.). This query returns 2155 rows.
I created a simple console application and created a simple EF model from our BFF Northwind.
The equivalent EF query is this:
var db = new NorthwindEntities();
var results = (from o in db.Orders
join od in db.Order_Details on o.OrderID equals od.OrderID
join p in db.Products on od.ProductID equals p.ProductID
join c in db.Categories on p.CategoryID equals c.CategoryID
select new {o.OrderDate, c.CategoryName}).ToList();
This also returns the same 2155 rows.
Two things of interest:
Look at the beginning of the C# code:
from o in db.Orders
join od in db.Order_Details on o.OrderID equals od.OrderID
In a normal SQL statement, on an inner join, the order of the statements is irrelevant. But, in C# if you reverse this statement to:
join od in db.Order_Details on od.OrderID equals o.OrderID
the EF query won’t compile, so the order does matter.
The resulting SQL run on the server is this:
SELECT
[Extent1].[OrderID] AS [OrderID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent4].[CategoryName] AS [CategoryName]
FROM [dbo].[Orders] AS [Extent1]
INNER JOIN [dbo].[Order Details] AS [Extent2] ON [Extent1].[OrderID] = [Extent2].[OrderID]
INNER JOIN [dbo].[Products] AS [Extent3] ON [Extent2].[ProductID] = [Extent3].[ProductID]
INNER JOIN [dbo].[Categories] AS [Extent4] ON [Extent3].[CategoryID] = [Extent4].[CategoryID]
Even though the select in the EF query doesn’t include OrderID, it is included in the SQL that gets executed. Interesting. I feel like I should know why, but I don’t.
Since I’m lazy, I won’t put in the specifics of adding back in the where clause. There’s some interesting things there as well, but it returns the same resultset, without anything all that shocking.
So, there you go. If you want to know how to write a ‘semi-complex’ query in EF, it is pretty straightforward. And it won’t cause “your DBA storming down the aisles, threatening you with bodily harm with the SQL that hit his DB.”