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

Adding data attributes to each option rendered by MVC DropDownListFor HTML Helper

Quite often you have a scenario whereby a parent dropdown causes another element to be updated based on what is selected, but not on the value selected directly but rather another piece of information related to the option selected.

For example, you want to show a country name in a textbox based on the city a user has selected in a dropdown, but the option value of each city is its primary key, not its associated parent country name so you have no direct link between option selected and a country name.

There are a number of ways to proceed including hitting the server via Ajax on city dropdown change to get the associated country and outputting a client side lookup table of sorts. A simple approach however is just to output the secondary information (in the form of a data-* attribute) to each dropdown option along with its primary value which will give you a select box whose HTML is similar to below:


After this, extracting country name from the selected option is easy.

In MVC how we’d commonly output a select box would be using a DropDownListFor HTML helper control, however if you want to add data attributes to each option when using a DropDownListFor control…well, you can’t but you can ditch the DropDownListFor helper and just output your select box manually using a foreach in razor code. Sample Razor code which will allow you to do this is below, followed by JQuery which helps you to extract the data attribute from the selected dropdown item (source code is available below).



Overview of steps to add data-* annotations

  • Manually output the select tag, with an id/class/name which you can use to hook a change event to using JQuery
  • Output an option for each of your collection items, to include one or more data attributes (in this case data-country) and a test to check if something should be selected.
  • In jQuery, add a change event to your dropdown and extract the value by using the :selected pseudo class  and the data function for getting data-* attribute values.
  • Note that even though you are manually outputting your select, you can still use strongly typed LabelFor, ValidationMessageFor (assuming the names match) etc. controls around your manually outputted dropdown.
  • Note the use of @Html.NameFor when generating the name of the dropdown. You’ll want to use the name which matches the viewmodel property so binding occurs correctly. This helper avoids the need for hard coding.
  • Watch out for the razor syntax to output text from within an if block. You will likely need the text tag or it’s alternative :@

Source Code

WordPress messes up code so I just put the images in above, but you can see the source for a sample manually outputted dropdown on .Net Fiddle

Posting disabled checkboxes from MVC Razor views

In ASP.Net MVC a common technique when dropdowns and textboxes are required to be disabled but their values still need posting back is to include a hidden backing field corresponding to the same model property. In the case of CheckBoxFor the idea is the same, except there is one extra peculiarity with CheckBoxFor due to the fact that for this HMTL  helper MVC renders a hidden text field of its own (along with the checkbox) which does not have the disabled attribute even if you have added it to the main CheckBoxFor HTML Helper. For example here is what is rendered for a disabled checkbox whose underlying bool property is set to true.


As nothing gets sent to the server if the checkbox is not checked, the binder has nothing to bind to so MVC always renders this hidden field and always sets its value to false. Due to the fact it appears after the checkbox, the true from the checkbox will bind first if the user has checked it, as MVC always binds to the first matched property. Therefore when we are dealing with disabled but checked checkboxes and we want to post the value, we need to ensure our hidden field appears before the @Html.CheckBoxFor hidden field. The first image below for example always bind as false (even though the checkbox is checked), while the second one binds are true.



I think this is a common enough problem, so hopefully the above helps.

Related Links

Credit to Darin Dimitrov for his answer on stackoverflow which outlines the approach above, however crucially he does not mention the importance of having the backing field before the @Html.CheckBoxFor statement which is why I imagine some people mentioned in the comments that a hidden backing field did not work for them.

MVC HTML helpers read from modelState before looking in the model

If you have posted back a form to an MVC action method and want to update some of the bound viewModel properties in that action method before redisplaying the same view, you might run into a problem whereby your HTML helpers are displaying the values as posted at binding time, but not as they were after manipulation.

Just before calling the view statement or indeed in the razor markup itself you can set a breakpoint and verify your viewModel/model have the updated values but still the old values render. This is because MVC assumes that if you’re rendering a view in response to an HTTP POST then you’re likely to be redisplaying a form that has failed validation. Since some data entered by the user and posted back may not be compatible with the model (i.e. posting a decimal number back when the corresponding model property is int) and thus MVC can’t store them in the model it stores all binded values into ModelState and looks in that before the model when rendering a view using HTML helpers such as TextBoxFor.

Example of HTML helper using ModelState not the model to render its value

For example below we can see what was posted (1) and the value of the viewModel/Model when sending it to the view (2). As we are using Html.TextBoxFor however, the rendered data is still showing ‘John’ and ‘Smith’.



Clearing ModelState so the model values will be used

The fix for this is to clear either ModelState completely as in 4a or just clear the ModelState properties which are relevant as in 4b images below. After clearing ModelState we can see that the manually updated properties are now displayed.

clearing specific modelstate


I’m not sure how known this behaviour is or how many problems it causes developers but it’s one to watch out for anyhow.

Related Links

Rick Strahl’s ‘ASP.NET MVC Postbacks and HtmlHelper Controls ignoring Model Changes‘ blog post describes this behaviour and also has a section on the rationale of having MVC work this way. 

Simon Ince’s article ‘ASP.NET MVC’s Html Helpers Render the Wrong Value!’ offers a number of alternative approaches that you could use rather than clearing ModelState such as not using html helpers and implementing the Post-Redirect-Get pattern.

MVC Dropdownlistfor defaulting to previously selected value when using a single SelectListItem List

Watch out for this one. If you have created a single list of SelectListItems and use this viewModel property to populate multiple DropDownListFor HTML helper controls, you will likely notice some strange behaviour.

What happens is that if one DropDownListFor has a selected value (ie. its bound property isn’t null) all subsequent DropDownListFor which are populated using the same SelectListItem List and don’t have a selected value have their default selection changed to whatever the previously selected value was. This happens even though expected behaviour would be to just leave the subsequent DropDownListFor selected to their default values.

As an example, this problem has the potential to occur below. If BirthCountry was selected to say Canada, and ResidenceCountry had no value, the 2nd dropdown would be defaulted to Canada and not “” as explicitly specified.

public IEnumerable<SelectListItem> Countries {get;set;}
@Html.DropDownListFor(x=>x.BirthCountry,Model.Countries,"") @Html.DropDownListFor(x=>x.ResidenceCountry,Model.Countries,"")

This is because SelectListItem is a reference type so when you change the Selected property to true, all dropdowns which use that SelectListItem will reflect this.

Always use distinct SelectListItem lists for each DropDownListFor

You can do this by creating them in the viewModel like:

public IEnumerable<SelectListItem> BirthCountries {get;set;}
public IEnumerable<SelectListItem> ResidenceCountries {get;set;}
@Html.DropDownListFor(x=>x.BirthCountry,Model.BirthCountries,"") @Html.DropDownListFor(x=>x.ResidenceCountry,Model.ResidenceCountries,"")

or just have a single viewModel property which is a collection of countries and then create multiple SelectListItems in your razor view:

public IEnumerable<Country> Countries { get;set}
    new SelectList(Model.Countries,"countryCode","countryName", Model.BirthCountry))
    new SelectList(Model.Countries,"countryCode","countryName", Model.ResidenceCountry))

Related links

Stackoverflow question with a good answer detailing when the behaviour described above occurs

Thread on discussing whether this behaviour is a bug or not