Viewing SQL generated by Entity Framework when querying or calling SaveChanges()

When using Entity Framework, in an effort to track down bugs or performance problems, it’s very handy to see what SQL Entity Framework is generating and sending to the DB when you run a lambda/linq query or indeed make a call to SaveChanges().

This is easy once you know how. Simple add this.Database.Log = x => System.Diagnostics.Debug.WriteLine(x); to the constructor of your database context class (the one that derives from DbContext):

dbcontext

Afterwards you should see all the queries Entity Framework is sending to the DB in your output window.

Validation failed for one or more entities. See ‘EntityValidationErrors’ property for more details

You may see this exception when doing a DbContext.SaveChanges() call. You then may go drilling into the exception details to find what the specific problem was and notice there’s nothing of value there.  A simple way of getting access to the EntityValidationErrors collection and actually seeing what’s in it is to add a watch for it:

((System.Data.Entity.Validation.DbEntityValidationException)$exception).EntityValidationErrors

Just copy and paste the above watch expression into the watch window and afterwards you can drill down to see what the problem(s) is. Thanks to Yoel Halb for this answer.

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)
AS
BEGIN
 SET NOCOUNT ON;

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

 SET CONTEXT_INFO @context
END
GO

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] ()
RETURNS NVARCHAR (30)
AS
BEGIN
 
 DECLARE @idToReturn NVARCHAR(30)

 IF CONTEXT_INFO() IS NOT NULL
  SELECT @IdToReturn = CONVERT(NVARCHAR (30), CONTEXT_INFO())
 ELSE
  SELECT @IdToReturn = suser_name()
 
 RETURN @IdToReturn
 
END
GO

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

CREATE TRIGGER manager_d
    ON DBO.MANAGER
    FOR DELETE
AS
     INSERT dbo.AUDIT_MANAGER
     (
        manager_id,
        updated_by,
        updated_date,
        mode
     )
     SELECT 
     d.manager_id, 
     dbo.GetUserContext(),
     getdate(),
     'Delete'
     FROM deleted AS d
GO

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())
   {
     SetUserContext(userId);
     base.SaveChanges();
     scope.Commit();
   }
}

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

Improving performance of read only selects in entity framework by turning off tracking with AsNoTracking method

We had a new contractor start the other day in work and he brought to my attention the ability to tell entity framework to not track (e.g. bypass the ObjectStateManager) the results of a query using the AsNoTracking method which if you are only loading entities once may result in a performance boost.

The order of the performance boost appears to be less significant in EF 6 than it is in EF 5 in terms of both time to complete and memory usage. The specific performance increase achieved will of course depend on how complex your model is and how many rows your looking to pull back from the database. In this example EF 5 showed a time to complete difference of 9% and EF 6 showed a difference of 3% between queries executed with and without tracking enabled.

I’m not sure how I missed this one before but this method appears very useful when querying read only data which you might just be using to populate a drop down or similar HTML element for example. Examples of how AsNoTracking can be implemented are on the MSDN article ‘Performance Considerations for Entity Framework 4, 5, and 6

As that article points out however be mindful that bypassing the ObjectStateManager disables the cache and thus if you retrieve the same entities multiple times from the same context, using AsNoTracking could actually worsen the performance of your queries. 

Using Optimistic concurrency with Entity Framework over SQL Server

If you want to have a client-wins or last in wins approach to concurrency management you don’t have to do anything, the last user to save their changes will simply cause an overwrite of all previous changes. This is acceptable in many cases. You might however want to notify the user of the most recent save (attempted) that another user has saved changes to a record since they loaded the record edit page and thus their data is out of date and their update hasn’t been saved.  This is refereed to as a store wins approach. Implementing this in Entity Framework is relatively easy, you need to:

  1. Enable Entity Framework to detect optimistic concurrency violations
  2. and when they happen catch DbUpdateConcurrencyException and notify user.

A common way to detect concurrency violations and the one recommended by Tom Dykstra is to use the rowversion data type approach. This data type is essentially a version stamp for a particular DB record. With this information, at the point of update (update or delete) client code (in this case entity framework) restricts the update or delete query with a where clause containing the original value of the rowversion field. If another user has edited the row, the where clause won’t match, 0 records will be update and Entity Framework will throw an exception. As noted in Tom’s article, to indicate to Entity Framework which field to use as the rowversion field you can annotate the relevant field with the [TimeStamp] annotation or use the fluent API.

timestamp

isRowVersion

This column of course must be manually created in the DB or migrated into the DB as with entity frameworks code first approach. Although for the purpose of the demo, Tom’s article has the C# property in the object class itself, it is often better to use an entity base class which can contain implementation fields such as RowVersion, DateCreated and DateModified. While doing this is still technically ‘polluting’ the domain model, it keeps things a little bit cleaner and is a good solution for most. Remember that you will need to post back the value of the RowVersion field using a hidden field for Entity Framework to work with it: @Html.HiddenFor(model => model.RowVersion).

Although the underlying exception thrown is OptimisticConcurrencyException, entity frameworks wrap of that is DbUpdateConcurrencyException, and this is the one you should catch after attempting calling SaveChanges() in your try block.

catchingConcurrencyException

You can see from the above trimmed down excerpt from Tom Dykstra’s official MS article (Handling Concurrency with the Entity Framework 6 in an ASP.NET MVC 5 Applicationthat he sends the current DB values back to the GUI so the user can see what the previous user changed the values to. Since the rowversion which gets sent back to the view is updated with the DB rowversion value the user can now review the current values and then decide to keep his or her changes without causing another concurrency exception. Although this is a nice usability touch, it can be expensive to program all the explicit comparisons between the DB values and values from the GUI, particularly in large applications with a lot of edit pages. An alternative approach might be to simply reload the edit page, discard attempted updates and put a message informing (using TempData) the user that their changes didn’t go through. Of course the user has to redo their changes in this instance, but this approach could be fine if it is only rare that concurrency issues happen.

basicConcurrency

To keep things DRY you could put this code in a base controller method. If a concurrency exception is thrown in a delete controller action after a previous user has edited something, you can use a similar technique and redirect to the edit page and inform them they need to try and delete again. On the other hand if a user attempts to delete an already deleted record, I don’t think you need to display anything concurrency related on the GUI as the user has got what they wanted, just a regular confirmation perhaps.