Duplicate rows returned when querying views with entity framework

If your querying a SQL Server view in entity framework it will attempt to use the first non-nullable column as the primary key. If this column is unique, all good, however if it is not unique, entity framework will return duplicate rows whereby all rows with the same ‘key’ will have the data from the first occurance (the first row) of that ‘key’. This is most likely not the behaviour you want, but its easily fixed:

Preventing entity framework returning duplicate rows from SQL Server views

  1. If your underlying data has a primary key be sure its selected first in the view.
  2. Add an artificial key just for this scenario using ROW_NUMBER() and return it first in the view.
  3. Use AsNoTracking() in your LINQ statement to advise EF to ignore key definitions and just return the data as is. If your data doesn’t have a primary key, AsNoTracking() is the simplest way to resolve this and you don’t lose anything as since you don’t update view records you don’t need EF to ever track this data. Usage is simple:

dbcontext.view.AsNoTracking().Where(x => …)

What to look for when reviewing code for SOLID principle ‘violations’

If your tasked with doing a code review of a fellow developers code or indeed are looking to improve an existing code base as a whole, one good set of principles to review the code by is the SOLID principles by Uncle Bob.

There are five SOLID principles in total and these courtesy of Wikipedia are:


Here’s what you should keep an eye out for if looking for violations of these principles:

Single responsibility principle – look for large classes which contain lots of functionality, chances are those classes could be broken up into smaller more focused ones. When your dealing with already small size classes the line is a little less clear as really what is the definition of ‘single’.

Open/closed principle – look for long blocks of if/else statements which check an object’s type and then does some similar action X (but differently) based on the result. When you add a new relevant type the class with this if/else type check code has to be modified to accommodate the new type, whereas the better way would be to use polymorphism either through interfaces or abstract classes.

Liskov substitution principle – this one may be a little tougher to spot and indeed is it often the toughest principle for developers to grasp. Look for subclass methods which change the behaviour (even if only sometimes) of a base class method in such a way that consumers can’t call the derived method and have it ALWAYS behave as if it was the base class method. To find LSP violations look for things like:

Subclasses with overridden methods which throw NotImplementedException().

Subclass methods which enforce stricter rules on parameters than their base.  For example… a base method accepts an integer as parameter but overridden method throws exception if this integer is not positive.

Methods which appear from a signature point of view to operate on a base class (or interface) but then within the method some type checking occurs. 

Interface segregation principle – look for large (fat) interfaces with a lot of unfocused methods, also look at interfaces whose clients throw NotImplementedException() a lot.

Dependency inversion principle – look for a lot of new statements.  New statements means concrete instantiations, which means coupling. Better to use a dependency injection framework or even (if you must) poor man’s dependency injection and push the creation of your objects up higher in the stack while simply passing down interfaces which provide a functionality contract but which makes no assertions about how that functionality is implemented.

Related Links

Overview of the five different principles. I always seem to be reading this article come interview time.

Passing userId to SQL Server delete trigger from entity framework

If you’re in an enterprise environment some form of auditing is likely to be required so you can track who inserted, updated or deleted what in your database. Using triggers to insert into audit tables is usually a good fit for this, particularly if your database can be updated from multiple sources.

Within both insert and update triggers you have access to the original record inserted or updated from your web app so you can access the updated_by column from the original record and pop this into your audit_* tables. In the case of deletes however, there never is any user information passed to the delete statement so delete triggers don’t know who specifically (the DB connection itself is usually shared) issued the delete. Using a session like feature in SQL Server 2000+ called CONTEXT_INFO however we can ‘pass’ the specific id of the user issuing the delete from our web app to the trigger.

Passing user info to delete trigger using CONTEXT_INFO

CONTEXT_INFO allows us to associate a limited (very limited but enough space for user info) amount of information with the current SQL Server connection. This info can then be read back at any stage from within the same connection. Using this to pass user info to a trigger is quite easy then:

  1. Set CONTEXT_INFO to userID logged into your web app
  2. Issue delete statement from your web app
  3. Read CONTEXT_INFO back in the delete trigger

As long as all three above happen in the same connection this should work fine. Setting the context is easy, but is best done from within a store procedure to encapsulate the code to deal with CONTEXT_INFO (cumbersome binary). SetUserContext below takes the user id as a param and stores it into CONTEXT_INFO.

CREATE PROCEDURE [dbo].[SetUserContext]
 @userId NVARCHAR (30)

 DECLARE @context VARBINARY(128)
 SET @context = CONVERT(VARBINARY(128), @userId)


Reading CONTEXT_INFO is best done through a function so you can just directly call it from your delete trigger statement that inserts into your audit_* tables. The GetUserContext function below returns CONTEXT_INFO if it is set or the standard suser_name() if it is not set.

Checking for null and falling back on the connected DB user is important if a DB can be updated directly by an admin or support person outside the context of an app which would set CONTEXT_INFO.

CREATE FUNCTION [dbo].[GetUserContext] ()

  SELECT @IdToReturn = suser_name()
 RETURN @IdToReturn

And finally a sample delete trigger which calls the function is below:

     FROM deleted AS d

After you have created the sproc for setting the context, the function for reading the context and have a couple of delete audit triggers created that’s things from the database point of view set up.

Setting CONTEXT_INFO using entity framework

In your web app you only care about setting the context. This can be done by simply calling the procedure. In entity framework this is easy:

public virtual void SetUserContext(string userId)
 var idParam = new SqlParameter("@userId", userId);
 this.Database.ExecuteSqlCommand("SetUserContext @userId", idParam);

Then in an overload of the SaveChanges method you simple call the SetUserContext sproc before you call the base SaveChanges method:

public void SaveChanges(string userId)
   using (var scope = Database.BeginTransaction())

Both the above functions would go in your database context class (the one which derives from DbContext).

Due to the fact that when executing stored procedures entity framework opens a connection, executes the proc and then immediately closes the connection, the context_info will be lost by the time we call SaveChanges which is when the delete trigger will run. To ensure both the setting and the reading of the context takes places in the same connection, we need to wrap them both in the same transaction as can be seen above.

What about connection pooling?

When a connection is being reused .Net SQL Server client executes the sp_reset_connection stored procedure which resets most connection specific state such as temp tables, cursors, transactions and crucially from the point of view of this article, CONTEXT_INFO. This is the case since SQL Server 2005 and above.

This means if you’re using connection pooling, which you really should be, you will not run into state sharing issues and thus connection pooling does not preclude you from using CONTEXT_INFO as part of your auditing solution. SQL Server 2000 users… get with the times or just reset CONTEXT_INFO after you issue your delete.

I hope you find the above useful. Please let me know your thoughts or questions below.

Related Links

List of what gets reset when a connection is reused from the connection pool

Stack Overflow question from a user who is concerned about creating race conditions if using CONTEXT_INFO

Another article about using CONTEXT_INFO from within Entity framework

Article about SESSION_CONTEXT which is a new and improved session mechanism in SQL Server 2016 which overcomes a lot of the limitations associated with CONTEXT_INFO

Prevent account enumeration on login, reset password and registration pages

Account enumeration is a potential security risk whereby a web site gives out information about what accounts are already in the system. This may a) leave them susceptible to a brute force-esque attack and b) may violate their users privacy which may be very important for certain types of sites. There are a couple of prominent Irish websites covered below which I’m familiar with and I just happened to notice they allow account enumeration but it is important to note that the vast majority of ecommerce sites online do this too. Certainly there are greater security risks out there such as SQL Injection, badly stored passwords, not using https when you should etc. but when this exploit is found alongside other exploits… well let’s just say that’s when bad things can happen.

keep login failed messages generic

I think most developers have the possibility of account enumeration in their minds when considering the messages returned from a login page for failed logins. When logging into most websites with a non existing username you will commonly see ‘Invalid username/password combination‘ type messages rather than ‘That username does not exist‘ type messages. An attacker now cannot just keep trying usernames on the login page until they find one that does exist, at which point they may (if possible) launch a dictionary password attack against that specific account. A few examples of Irish sites doing a good job here include easons.com and water.ie:

Generic Login messages


Another prominent Irish site; ticketmaster.ie does present different messages to the user if the account exists or not on their login page. The following image shows the login for non existing accounts:


It all looks good right? No mention that the specific email address does not exist, just that the address and password as a whole did not match any accounts. If however, you know of one account that does exist (the one the hacker just created for himself) and try to login with that you’ll notice that the message is different:


Ticketmaster do appear however to have an account lock out facility which locks accounts after 5 failed accounts. What this means is that although an attacker can find out if an account exists due to the slightly different error messages returned (which would be really bad for a very private site.. think ashleymadison.com) they will only get five password tries thus a full on brute force attack against a specific account with a password dictionary is not feasible.

Although locking accounts help mitigate against brute force attacks there are a number of potential problems with this approach. It can for example leave ticketmaster.ie open to an account lockout attack, whereby the attacker can deliberately lock out a large number of accounts in a denial of service-esque attack (this is possible as ticketmaster sends a reset password rather than URL to reset a password). It also means an attacker could build a large list of valid accounts and try the same password on all of them (ticketmaster1234 perhaps) which could return a few matches. Ideally ticketmaster would not reveal if an account existed or not. 

preventing account enumeration on the forgot password page?

The same idea should of course apply to the forgot/reset password pages. Unfortunately what is very common is that although a website’s login fail message is generic, the websites goes and undoes all this good work on their forgot/reset password page. Remember easons.com who displayed ‘If you have an account but can’t remember your password, click on “Password Problems?” below‘ regardless if the account existed or not? Well here’s what happens on their reset password page:

easons-forgotpassword1 easons-forgotpassword2

Remember water.ie who upon failed login just presented ‘Please enter a valid username and password’ to the user? The first image below is what the forgot your password screen shows when the account doesn’t exist, while on the right, we see what the screen shows for an account that does exist.








So previously undiscoverable accounts are now discoverable on both easons.com and water.ie via their reset password pages. This is probably not a huge deal from a privacy perspective (does your neighbour like books? / has your neighbour paid his water charges?) for these particular sites. If however these sites don’t implement some kind of brute force mitigation individual accounts could be vulnerable. Ticketmaster.ie, as noted does appear to have brute force protection in place, this is just as well because their password reset page gives up existing accounts too:

What should happen in reset password scenarios is that the website should display the same generic message regardless if the username/email address exists. A message such as ‘Further instructions have been sent to your email address‘ or similar is often used in both cases. If the email address does exist the email can contain a password reset link (not a reset password in email – search for ‘Sending a reset password versus sending a reset URL’) which the email account holder can click on if it was them that asked for the reset or ignore if it was not. If the email account doesn’t exist in the user DB, just don’t send any email.

…but what about the registration page? I have to tell a potential new user that their chosen username/email is already taken on screen!

Not necessarily. Preventing account enumeration is most problematic on the registration page that’s for sure, at least from a usability perspective. The goal here is the same as with the login and password reset pages and that is to keep the outward (what’s on the screen) process the same for usernames/emails that already exist and those that don’t.

If the site uses the email as the username, it should be possible to do this pretty nicely and in a manner similar to before. It might require some re-jigging of your registration pages, but the idea would be to start the process by requiring the user to enter their email address. When they submit this notify them that they have been sent an email with further instructions regardless if the email is in the DB or not. If the email address is already in the system the email will advise them that they have already registered and perhaps provide a link to the password reset page. If the email address is unused the email will contain a time limited URL link to verify email and continue the registration process. There is a very small usability hit on this approach as the notification about email addresses already existing now happens via email rather than on screen, but this will affect only a tiny percentage of registrations.

Both easons.com and ticketmaster.ie use the users email as their username so could use the above approach. Their current registration processes let us all know if accounts already exist or not:

easons-createaccount ticketmaster-createaccont

Note – water.ie is not really applicable in this case as their registration process begins with the entering of a application ID and associated pin which has been sent out in the post.

What if a website uses arbitrarily chosen usernames rather than email addresses to login? Well, for sure preventing account enumeration in this case is a bigger problem than above. In this instance, the first step of the process could capture both of these and in the follow up email notify if the email entered is already taken and advise of the username already associated with the email. Not a major problem so far… but what if the email is available but the username is already being used by a different user.

On popular sites, millions of usernames could already be taken, so it would be impractical to send a ‘username already taken’ email everytime someone tries to use an existing username as it could take them god knows how tries to find a free one. In this case, the best balance between security and usability may be to notify the user trying to register that their desired username is already taken on the registration page itself rather than via email but also to reCAPTCHA enable the page so scripts won’t work. This means the task of trying to find if a username already exists requires manual intervention by the attacker which significantly reduces their ability to build lists of existing accounts. Of course reCAPTCHA from Google and similar plugins by others are not unbreakable so there is no 100% perfect solution here. Your specific implementation is likely to be yet another trade off between security and usability.

suggestions to prevent account enumeration:

  1. Use email addresses as usernames as this makes accounts easier to protect on password reset and registration pages.
  2. Show generic ‘Invalid username/password combination’ message on failed login.
  3. Show generic ‘Further instructions have been sent to your email address’ message on password reset. If email is already in DB, send a time limited reset password URL
  4. First registration step is user enters email address and submits, then the site shows generic ‘Further instructions have been sent to your email address’ message. If email is already in DB, email says already registered etc. If email not in DB, email should contain a continue registration URL.

related Links

Troy Hunt’s (my favourite microsoft MVP by a mile) amazing article ‘Everything you ever wanted to know about building a secure password reset feature‘ from 2012 which was how I was originally introduced to many of the ideas above.

Troy Hunt’s Secure Account Management Fundamentals PluralSight course. Another amazing resource from Troy.

OWASP page on preventing brute force attacks. Note in particular the section on account lockouts and the potential exploits that remain even when doing this after X bad attempts. The surface space of many of these exploits can be reduced by preventing account enumeration on login, reset and registration pages as per above.

please can I have your comments and/or questions?

I’m very interested to know your thoughts on the whole topic of account enumeration so please feel free to leave a comment. If you’re a developer or IT professional and would like to connect with me on LinkedIn please check out my profile and send me a connection request.

Organise ASP.MVC projects by feature by changing view location in MVC

I think it would be a safe bet to say most people stick with the default ASP.Net MVC folder structure and set up whereby the project structure is organised by architectural role (e.g. models, views and controllers) and not by business feature (e.g. registration, shopping cart, order history etc. ). For small projects to me this is fine. For bigger projects however it can be cumbersome to navigate as code related to particular features are scattered over multiple folders.

This ‘ by architectural role’ organisation goes against how in my experience software is written. Usually we build systems out feature by feature rather than writing all HTML markup, all controllers, all model classes etc. During development, this is particularly true in teams that embrace agile with its focus on delivering something that works and adds value at the end of each sprint. Imagine spending a three week sprint just writing viewModels… business value derived at end of sprint = 0. Check out this post on stackoverflow for some interesting comments on the merits of organising MVC by feature.

I’m sold… how do I organise my ASP.Net MVC project folders by feature? Well, given that MVC searches for controllers by type and name (not folder location) you can simply just start creating folders for ‘Registration’, ‘CheckOut’ etc. in the root and put your controllers, viewmodels and models in them and MVC will work fine. Views however are required to be in one of a number of predefined locations so simply creating folders won’t help you here. MVC is very extensible so we can of course change view locations and I will outline all the steps required to do this later. First however let’s look at MVC built in ‘Areas’ to see if they can meet our requirements ‘out of the box’.

Organise MVC projects using areas

Areas are around since MVC 2 and are logical groupings of controllers, models and views and other related folders (such as images, JavaScript etc.) for a particular module or feature in an MVC application.

mvcAREASA downside of areas unfortunately is that within them things are still organised into three folders; models, views and controllers. Yes everything within an area is related to a particular feature (a big step in the right direction) but code within that feature is still scattered as can be seen from the image to the left.

If this project layout gives you what you need, excellent, areas is what you should use. The extra advantage of using areas is of course they are available out of the box so if you have problems with them there is likely to be a lot of help available online. Snesh Prajapati’s article on areas in MVC 4 (no major changes in MVC 5 (not sure about MVC 6) except for how they are added I believe) on codeproject.com is one of the better articles on areas. In her article she notes the downsides of just creating moduleA, moduleB etc. folders under each of the root controllers, models and views folder. She also outlines the routing related code needed to wire up areas.


For my recent projects areas didn’t meet my needs as I was aiming for a structure similar to the right whereby everything could be in one folder. I know I could get close to this by just putting controllers, models and viewmodels in an area root and deleting the controllers and models folders. This however means my URLs get longer (a consequence of using areas), linking between areas becomes a little more ‘complicated’ and of course I’m still forced to put my views in the views folder (albeit a feature specific one).

Steps required to change view location in MVC

Areas don’t check all my checkboxes so I needed to find out how to change the location of where MVC looks for views. If you’re interested in doing this yourself, note there are a number of steps involved (overriding the default viewEngine is not enough!!!) but all are pretty easy to follow. The steps to change view location in MVC are:

Clear existing viewEngines, add back RazorViewModel, set new locations

The first step to change view location in MVC is to remove existing view engines and add RazorViewModel back in and define where the views are. Below I’m instructing MVC to look in ~/Shared/Views/VIEWNAME.cshtml (which will be the new location for shared views) and ~/CONTROLLERNAME/VIEWNAME.cshtml for my normal views. After this MVC does not care about the default ~/Views folder anymore. Note to change view location in MVC you don’t have to create a new ViewEngine like many places recommend.

Set the new view paths in Application_Start to change view location in MVC


Copy old ~/Views/web.config into ~/web.config so views will work correctly

Merge the ~/Views/web.config file with into the root web.config file. Views derive from System.Web.Mvc.WebViewPage and the root web.config doesn’t have this assembly referenced by default. Referencing it from root web.config means you don’t have to add a web.config for each new feature folder you add. Not everything needs to be brought across as some of it is there already, just focus on sections highlighted in yellow below.



Edit handlers in ~/web.config to explicitly allow JS, CSS etc. to be served

Set BlockViewHandler path to ‘*.cshtml‘ in the now updated root web.config. The web.config in the views folder had this set to ‘*’, meaning IIS would, for security reasons refuse to serve any files from the views folder. Setting it to .cshtml means all the CSS and JS will render correctly…. but wait…..

cshtml blocked

… note this is the blacklist approach where we are explicitly blocking .cshtml files from being served. We may add a new type of file in the future to a location in the project that doesn’t have it’s own web.config (and hence uses root web.config) which we do not want served but if we forget to change the above it will be silently served.

A more secure approach is to whitelist and explicitly allow whatever extension we know to be safe to be served. I’m obviously recommending the whitelist approach but I’ve included the blacklist approach just to illustrate the differences between the two as not many articles discussing how to change view location in MVC mention this when they advise the whitelist approach.



Move views to new shared views folder, update _ViewStart.cshtml and tidy up

Copy all files ~/Views/Shared to ~/Shared/Views. The original views/shared folder is now a dead duck, bring whatever views (most likely _Layout.cshtml at least) you need from there to the Shared/Views folder which hangs off the root.

Copy _ViewStart.cshtml from ~/Views to ~ and update the layout path inside it to point to new location of the _Layout.cshtml file.

new viewstart

Delete existing ~/Views folder as we have everything we need from it now.

Delete controllers and models folder in root when you moved all your classes to their own feature folders.

Related Links

The above relates to MVC 4 and MVC 5. MVC 6 has not been released yet, it’s still in RC1 and RC2 is not yet scheduled so it could even be 2017 before we see a final RTM version. If you’re playing with RC1 and looking to know how to do change view locations with it, the following two links may help:

Customize ASP.NET MVC 6 View Location Easily
What’s new in MVC 6: View Location Expanders


matthewFor an absolutely great overview of ‘Clean Architecture‘ which is the underlying pattern behind the idea of organising software around business features please check out Matthew Renze’s excellent video on Youtube.