I am using Entity framework on a forum website and have two query examples with questions.
Query #1) This query is supposed to get a topic count of all topics in the forum. Is it executing a count in SQL so that I only get a number back? Or is it pulling all the topics into memory and then counting the topics in the collection?
return DBContext.Topics
.Where(x => !x.ModsOnly)
.Where(x => !x.Board.ModsOnly)
.Where(x => !x.Board.Hidden)
.Count();
Query #2) This query is supposed to get all topics, order them by last reply date (or topic date if no replies). Then it counts the results, and pages the results. How much of this query is executed in the database? This query takes FOREVER so I think it is pulling all topics into memory at some point, likely before the paging takes affect.
var query = DBContext.Topics
.Where(x => !x.ModsOnly)
.Where(x => !x.Board.ModsOnly)
.Where(x => !x.Board.Hidden)
.OrderByDescending(x => x.GlobalSticky)
.ThenByDescending(x => x.Replies
.Where(r => !r.ModsOnly)
.Any() ? x.Replies
.Where(r => !r.ModsOnly)
.Max(r => r.PostedDate) : x.PostedDate);
int totalTopics = query.Count();
if (totalTopics > itemsPerPage)
return query.Skip((page - 1) * itemsPerPage).Take(itemsPerPage);
else
return query;
I am not a LINQ guru. This is a complicated query I know. Where did I go wrong?
Note: I am trying to avoid creating a column in topics called "LastReplyDate" and ordering topics by that. For my purposes I would like the ordering to be by the last reply, and not by an arbitrary column on topic that I could have to update with every added/deleted reply.
However, if readers think there is no other way to accomplish my goal, I will consider that route.
Your first example will use one DB query. Yes, it does the count in SQL.
Your second will use two. One for the int totalTopics = query.Count(); and one for the paged results.
Neither one of them will do the restriction, etc., in memory.