How to get many grouping functions in only one query with LINQ?

Behind the EF magic, there is the SQL reality. I’m sticking to my guns, if you don’t know SQL, you can’t write best Entity Framework queries.

I find a good sample with grouping functions.

Imagine that you want to get the last order date and the number of orders.

In SQL, you will probably write something like this:

SELECT COUNT(1), MAX(OrderDate)

FROM Orders

But how to do the same in L2E?

Of course we can write two queries but there is a very important point to note: the cost to get the count or the max or the count and the max is the same:

image

It means that even if we ignore the two DB connections cost, having two queries to get the number of orders and the last order date is twice more expensive than getting these information on only one query.

Now if you think SQL, you know that COUNT and MAX are some grouping functions.

We can have the same execution plan with a group by on a constant:

SELECT COUNT(1), MAX(OrderDate)

FROM

(

      SELECT OrderDate, 1 AS G

      FROM Orders

) O

GROUP BY O.G

image

And this is writable using L2E:

from o in context.Orders
group o by 1 into g
select new { Count = g.Count(), MaxOrderDate = g.Max(o => o.OrderDate) };
This entry was posted in 12253, 7671, 7674, 9104. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>