Database Lookup on Route in .NET MVC4

I started messing around in .NET MVC4's Routing and was wondering if you had a custom route where you need to look up something in the database, how would that work?

I found out this was possible using the constraints attribute when you setup your route. I created a new constraint by extending IRouteConstraint and forwarded the look up on to a model where I did the database look up.

App_Start >> RouteConfig

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;
using myApp.App_Start;

namespace myApp
{
    public class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                name: "LocationSite",
                url: "{id}/{action}",
                defaults: new { controller = "Snowboard", action = "Index", id = UrlParameter.Optional },
                constraints: new { id = new SnowboardSiteRouteConstraint()  }/*---database lookup for snowboards---*/
            );
         }
    }
}

App_Start >> SnowboardSiteRouteConstraint

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Routing;
using myApp.Models;

namespace myApp.App_Start
{
    public class SnowboardSiteRouteConstraint: IRouteConstraint
    {
        public bool Match(HttpContextBase httpContext, Route route, string parameterName,RouteValueDictionary values, RouteDirection routeDirection)
        {
           /*---database lookup for snowboards---*/

            return SnowboardSite.IsSnowboardSiteName((string)values["id"]);

        }
    }
}

Model >> SnowboardSite

using System;
using System.Collections.Generic;
using System.Web;
using myApp.Persistence;
using myApp.Entities;
using NHibernate;
using NHibernate.Linq;

namespace myApp.Models{
    public class SnowboardSite{
        public static bool IsSnowboardSiteName(string boardName){

            /*---database lookup for snowboards, return the result as boolean---*/

            return [[result of database lookup goes here as a boolean]];
        }
    }
}

SQL Multiple table constraint

I've always wanted to know how to do a constraint across tables and someone on StackOverflow did too apparently. Here's the link:

How do I create a multiple-table check constraint?t

CF to .NET: request and session scopes

Coldfusion has a request and session scope and I was determined to find the same in .NET. After some googling I stumbled across this:

HttpContext.Current.Session["Username"] = username;

Using "HttpContext.Current.Session" and "HttpContext.Current.Request" I was able to start a session for use once they logged in. Also I could now store things the request scope which I don't really need because .NET's Viewbag handle most of what I needed for that. In the example above I am storing the username after a login into the session. I am sure there is a more proper way of doing it, but for basic concepts this worked.

Deleting Duplicate Bridge Records

When I was importing some data I mistakenly dup'd records into a bridge (many to many) table. I wanted to delete all but the first occurrence of the relationships for the comibination of ids (snowBoarderID,snowBoardID) and made use of the ROW_NUMBER() over in SQL again.

with data as(
 select
  snowBoarderID,
  snowBoardID,
  ROW_NUMBER() over (partition by snowBoarderID,snowBoardID by SnowboarderToSnowboardID asc ) as rownumber
 from SnowboarderToSnowboard
 where isActive = 1
)
select * 
--delete
from data
where rownumber > 1

SQL MAX() RecordID ordered by date

I had a task where I needed to get the most recent timesheet record for task. A task can have many instances on the timesheet. I made use of the ROW_NUMBER() over SQL statement to first order the records by date. Then I got the record where the recordNumber was equal to 1.

select 
 tt.taskTimesheetID
from(
 select 
  ROW_NUMBER() over (partition by tt.taskID order by tt.startTime asc, tt.CreateDate desc ) as recordNumber
  ,tt.taskTimesheetID
  ,tt.taskID
 from tasktimesheet as tt
 where tt.isactive = 1
  and tt.taskID = 5254
   
 ) as data 
inner join taskTimesheet as tt on tt.taskTimesheetID = data.taskTimesheetID
 and data.recordNumber = 1