MSSQL recursive list in a Stored Procedure

The following scenario allows one Staff Manager to view all direct reports and their child member's direct reports recursively:

    WITH Hierachy(StaffNo, ManagerStaffNo, PreferredName, Level)

    AS

    (

        SELECT StaffNo, ManagerStaffNo, PreferredName, 0 AS Level

        FROM Staff s

        WHERE s.StaffNo  = @StaffNo



        UNION ALL



        SELECT s.StaffNo, s.ManagerStaffNo, s.PreferredName,  sh.Level  + 1

        FROM Staff s

        INNER JOIN Hierachy sh ON s.ManagerStaffNo = sh.StaffNo

    )

    SELECT StaffNo, ManagerStaffNo, PreferredName, Level

    FROM Hierachy

Dynamic Themes in MVC and handling Page_PreInit

Another thing I learnt at Resonate Solutions:
Generally, when you create an MVC Application the project template will provide a Master Page that can be referenced by any Views you create.
That means when you want to implement generic code that executes for all page loads, typically the most logical place to put it is in the Master Page's code behind.
This would be ideal if you were to implement the following code to set a theme dynamically (based on the themes in the App_Theme folder):

this.Page.Theme = Request.QueryString["Theme"];

The page's theme, however, is processed during the initialization stage of the page's life cycle, and therefore if we need to set it, it must be done before hand i.e. in the OnPreInit event handler.
The problem is that the Master Page doesn't actually have an OnPreInit event because it isn't actually a page in its own right.
It is a user control that merges with the content page during the initialization phase of the page's processing, so just like a UserControl it wouldn't have an OnPreInit.
So therein lies our problem. Rather than copying and pasting the above code for every single View. We would need to create a BaseViewPage that is inherited by all the Views.
Take note that all Views inherit off the following class:

namespace Hub.Samples.Mvc


{


    public class BaseViewPage<T> : ViewPage<T> where T : class

    {

        this.Page.Theme = Request.QueryString["Theme"];

    }

}

That also means that each View we create needs to inherit off it. This can obviously be done in the page directive as follows:

<%@ Page Inherits="Hub.Samples.Mvc.BaseViewPage<dynamic>" %>

Linq to Entities Count() using IQueryable<T> in Extension methods

Something I learnt at Resonate Solutions while working particularly on performance tuning of Linq To Entities when I was digging a little deeper into the IQueryable<T>.Count() method.
I realised the when I was using SQL Profiler to see what the generated TSQL code was, it didn't have the equivalent COUNT(*) operator. It was actually evaluating the entire expression first then passed it back to the application so that the count could be made on the instance collection in memory, which would have a serious impact on the performance of the query because of all the data being sent back and forth.
The main reason it was doing this was because the Extension Method I used was for a type IEnumerable<T> as shown below even though the instance passed in was of type IQueryable<T>

public static int PageCount<T>(this IEnumerable<T> list)


{

    int count = list.Count();

    //rest of the code...

}

 
Since IQueryable<T> implements IEnumerable<T> I thought it would be good to use a "baser" Interface, but as it happened, it returned the entire collection then did a count on the inferred IEnumerable<T> instance in memory.

Looking into SQL Profiler, the TSQL that gets generated contains the following:

exec sp_executesql N'SELECT 

[Project2].[C1] AS [C1], 

[Project2].[C2] AS [C2], 

FROM ( SELECT 

      [Distinct1].[C1] AS [C1], 


      [Distinct1].[C2] AS [C2]

However if I were to use:

        public static int PageCount<T>(this IQueryable<T> list)



{


    int count = list.Count();

    //rest of the code...

}

That evaluates the TSQL with COUNT(1) which is exactly what I wanted as shown below:

exec sp_executesql N'SELECT 

[GroupBy1].[A1] AS [C1]

FROM ( SELECT 

      COUNT(1) AS [A1]

      FROM ( SELECT DISTINCT 

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

            [Project1].[C2] AS [C2]

The main reason for this is that the inference on an interface is quite different than doing it on an abstract function or base class when everything is done in memory.
When passing in the instance of an object that implements IQueryable<T> through a parameter that is of type IEnumerable<T> the instance is forced to shape itself as an IEnumerable and therefore Linq to Entities will recognise that it needs to evaluate the whole collection first rather than building the expression prior to execution.