Is this query being executed on an in-memory collection, or in the database?

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 so any help is appreciated. This is a complicated query I know, but if someone can take a second to parse it and figure where/if I went wrong somewhere, that would be very helpful.

Note: I am trying to avoid creating a column in topics called "LastReplyDate" and ordering topics by that. For my purposes I would really like the ordering to be done 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 you guys 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.

The queries will be executed on the database, but for the second one I would recommend taking out the order by clauses since they are not necessary for the count operation and are slowing it down.