Monday, 7 April 2014

Optimizing ASP.Net Identity Database Access

I have to say I’m a bit obsessive about issuing SQL requests as they take time, and so the fewer requests I can make the better.

Recently I’ve installed the latest version of ASP.NET Identity (2.0.0.0) and whilst running SQL Profiler I noticed something odd being executed for every instance of my DbContext class. What I was expecting was one SQL statement (that being the one I was explicitly issuing), however what I actually saw in the profiler was three…


(1) SELECT Count(*) FROM sys.databases WHERE [name]=N'VPC'


(2) exec sp_executesql N'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@Table',N'@Table nvarchar(11)',@Table=N'AspNetUsers'


(3) SELECT
[Extent1].[PublisherId] AS [PublisherId],
[Extent1].[ApiKey] AS [ApiKey],
[Extent1].[Name] AS [Name],
[Extent1].[Description] AS [Description]
FROM [VPC].[Publisher] AS [Extent1]
ORDER BY [Extent1].[Name] ASC

The one I was expecting was #3, the other two were unexpected and also unwanted. So I had to find out where they were coming from.


When I start hunting I always think “it’s my fault, I’ve caused this somehow” – and to be honest it almost always is caused by my code. But I was pretty sure that this wasn’t my code – or at least, it wasn’t cause by my code directly. What followed was an hour or so of fruitless searching in my project – I began with an EF profiler, no change, then I added some extra debug, no change, wired up some logging for Owin to see if it was one of the bits of middleware added into the pipeline, still no change, none was telling me what I needed to know.


I then started hunting on the web, and happened to find just what I needed on www.symbolsource.org, specifically the code for the IdentityDbContext class (which was where I’d hazarded a guess the problem was). I was (as most people will do) deriving from IdentityDbContext<TUser> which I’d assumed was benign but it wasn’t. It contains the following code in the default constructor…

  public IdentityDbContext(string nameOrConnectionString)
: this(nameOrConnectionString, true)
{
}

And looking at the next constuctor...

  public IdentityDbContext(string nameOrConnectionString, bool throwIfV1Schema)
: base(nameOrConnectionString)
{
if (throwIfV1Schema && IsIdentityV1Schema(this))
{
throw new InvalidOperationException(IdentityResources.IdentityV1SchemaError);
}
}

Not much going on there - or is there?. If you look at the code for the IsIdentityV1Schema function it reveals all - it's checking if the database exists, and it's also doing the column name lookup.


So, to remove the two additional SQL statements from your code, you need to pass false as the second parameter to the IdentityDbContext constructor…

  public class DbApplicationRepository 
: IdentityDbContext, IDbApplicationRepository
{
public DbApplicationRepository(string connectionString)
: base(connectionString, false)
{
}

...
}

Hopefully this will help someone!

13 comments:

Unknown said...

Awesome, thanks, fixed the issue fore me!

Unknown said...

thanks for awesom post
asp.net training in jaipur

Nick said...

Thanks for posting this!

Mary Petter said...

Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from Dot Net Training in Chennai. or learn thru ASP.NET Essential Training Online . Nowadays Dot Net has tons of job opportunities on various vertical industry.

Richard Majece said...

I don't like to waste my time and I prefer optimising. For example, when I need to write argumentative essay, this writing info helps me a lot.

Jacob Foshee said...

You are not alone. These DB calls can be expensive, especially if they need to travel around the world. Thanks so much for sharing.

Puremelda said...

Our declaration to providing the best customer experience when it come to those seeking custom writing services from research paper companies are guided by these principles we entrench daily in our delivery for the delivery of business essay writing service.

Kelly0989 said...

Don't stress out. Stop being so obsessive about your data dictionary sample. If you need some help just let me know, I have a few tips to share.

norhan said...


تنظيف منازل بالدمام شركة تنظيف
تنظيف منازل بالاحساء شركة تنظيف منازل بالاحساء
تنظيف منازل بمكة شركة تنظيف منازل بمكة
تنظيف منازل بجدة شركة تنظيف منازل بجدة
تنظيف منازل بالمدينة المنورة شركة تنظيف المنازل بالمدينة المنورة

ahmed5987 said...

تتضاعف جميع قطع الأثاث في الصناديق عند الحاجة ، أو يمكن الاستمتاع بها فقط كأثاث جديد أو فريد. يمكنك أيضًا شراء خطط ومجموعات أثاث / صُنع لتصنعها بنفسك. أثاث النعش يأخذ أيضا أوامر مخصصة.شركة نقل عفش
شركة نقل اثاث من الرياض الى جدة

شركة نقل عفش من الرياض الى جدة
شركة نقل عفش بالجبيل

Sankar said...

I have read your blog its very attractive and impressive. I like it your blog.
Final Year Projects for CSE in Dot Net

.Net Training in Chennai

Final Year Project Centers in Chennai

Dot Net Training in Chennai

noor said...


تسليك مجارى بالاحساء تسليك مجارى بالاحساء
تسليك مجارى بالدمام تسليك مجارى بالدمام
تنظيف بيارات بالرياض تنظيف بيارات بالرياض

كشف تسربات المياه بالاحساء كشف تسربات المياه بالاحساء

Nora Pittman said...

Crypto-currency as a modern form of the digital asset has received a worldwide acclaim for easy and faster financial transactions and its awareness among people have allowed them to take more interest in the field thus opening up new and advanced ways of making payments. Crypto.com Referral Code with the growing demand of this global phenomenon more,new traders and business owners are now willing to invest in this currency platform despite its fluctuating prices however it is quite difficult to choose the best one when the market is full. In the list of crypto-currencies bit-coins is one of the oldest and more popular Crypto.com Referral Code for the last few years. It is basically used for trading goods and services and has become the part of the so-called computerized block-chain system allowing anyone to use it thus increasing the craze among the public, Crypto.com Referral Code.

Common people who are willing to purchase BTC can use an online wallet system for buying them safely in exchange of cash or credit cards and in a comfortable way from the thousands of BTC foundations around the world and keep them as assets for the future. Due to its popularity, many corporate investors are now accepting them as cross-border payments and the rise is unstoppable. With the advent of the internet and mobile devices,information gathering has become quite easy as a result the BTC financial transactions are accessible and its price is set in accordance with people’s choice and preferences thus leading to a profitable investment with Crypto.com Referral Code. Recent surveys have also proved that instability is good for BTC exchange as if there is instability and political unrest in the country due to which banks suffer then investing in BTC can surely be a better option. Again bit-coin transaction fees are pretty cheaper and a more convenient technology for making contracts thus attracting the crowd. The BTC can also be converted into different fiat currencies and is used for trading of securities, for land titles, document stamping, public rewards and vice versa.

Another advanced block-chain project is Ethereumor the ETH which has served much more than just a digital form of crypto-currency Crypto.com Referral Code and its popularity in the last few decades have allowed billions of people to hold wallets for them. With the ease of the online world,the ETH have allowed the retailers and business organizations to accept them for trading purposes, therefore, can serve as the future of the financial system.