question

FrayFray-2159 avatar image
0 Votes"
FrayFray-2159 asked BruceZhang-MSFT answered

EF6 app w/ AspNet Identity / OWIN continuously calling SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

Has anyone ever seen this or know what might be causing it?

Diagnosing performance issues for a .NET app built with MVC 5, EF6, repository pattern, using Ninject, Automapper, AspNet Identity. It was built database-first and uses LINQ to Entities to call a SQL Server 2016 database.

The SQL Server logs show that in production, the following query is called an average of 70 times per second:

 (@Table nvarchar(11)) SELECT COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME = @Table

Not sure where the nvarchar(11) would come from, but could it be based off of a specific table with 11 characters, such as AspNetUsers or AspNetRoles?

Could this possibly be due to some hidden setting in IIS? Something dealing with connection pooling / keeping connections open?

Anyone have any ideas on this one? I'm totally at a loss...

UPDATE

Determined that this is being called from Asp.Net Identity or OWIN functionality. It is passing in 'AspNetUsers' as the table name, so for some reason it is constantly querying the database for column names in the AspNetUsers table.

Any ideas on what would cause this behaviour?

windows-server-iisdotnet-aspnet-mvcdotnet-entity-framework
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, nvarchar(11) suggests that the length of the variable is 11 characters.

I don't know EF, ASPNET or IIS (I'm an SQL Server guy), so I cannot say where this madness comes from.

0 Votes 0 ·

Hi @FrayFray-2159 ,

IIS don't have any setting to operate SQL Server directly. Only one module that called Connection Strings(it only can add or remove connectionstring) has relationship with SQL Server. But it won't affect the performance of query or create any hidden table in SQL.

You need to determine what sent this query to the database. Is it your custom query statement or some EF method? In addition, did you choose a template with authentication function when creating an MVC5 application?


0 Votes 0 ·
  • NEW INFO **

We determined that the table name being passed in as a parameter is 'AspNetUsers'. So, these SQL queries are being generated by the AspNet Identity code being used for user login management.

Has anyone seen these kind of issues with respect to AspNet.Identity?

0 Votes 0 ·

Has anyone seen these kind of issues with respect to AspNet.Identity?

No. I have several testing application that use Identity with EF6 and code first. The apps are mostly basic and used for answering general forum question. I can't reproduce a single script that queries INFORMATION_SCHEMA.COLUMNS.



0 Votes 0 ·
FrayFray-2159 avatar image
0 Votes"
FrayFray-2159 answered FrayFray-2159 edited

Here is a partial answer:

The IdentityDbContext object has a VerifyColumns method that creates the SELECT COLUMN_NAMES sql query...

 [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities",
             Justification = "Reviewed")]
         internal static bool VerifyColumns(SqlConnection conn, string table, params string[] columns)
         {
             var tableColumns = new List<string>();
             using (
                 var command =
                     new SqlCommand("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@Table", conn))
             {
                 command.Parameters.Add(new SqlParameter("Table", table));
                 using (var reader = command.ExecuteReader())
                 {
                     while (reader.Read())
                     {
                         // Add all the columns from the table
                         tableColumns.Add(reader.GetString(0));
                     }
                 }
             }
             // Make sure that we find all the expected columns
             return columns.All(tableColumns.Contains);
         }
     }


Now, the question is: Where would this method be called from?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BruceZhang-MSFT avatar image
0 Votes"
BruceZhang-MSFT answered

Hi @FrayFray-2159 ,

If you check the source code of IndentityDbContext, you will find how this method work.

 internal static bool IsIdentityV1Schema(DbContext db)
         {
             var originalConnection = db.Database.Connection as SqlConnection;
             // Give up and assume its ok if its not a sql connection
             if (originalConnection == null)
             {
                 return false;
             }
    
             if (db.Database.Exists())
             {
                 using (var tempConnection = new SqlConnection(originalConnection.ConnectionString))
                 {
                     tempConnection.Open();
                     return
                         VerifyColumns(tempConnection, "AspNetUsers", "Id", "UserName", "PasswordHash", "SecurityStamp",
                             "Discriminator") &&
                         VerifyColumns(tempConnection, "AspNetRoles", "Id", "Name") &&
                         VerifyColumns(tempConnection, "AspNetUserRoles", "UserId", "RoleId") &&
                         VerifyColumns(tempConnection, "AspNetUserClaims", "Id", "ClaimType", "ClaimValue", "User_Id") &&
                         VerifyColumns(tempConnection, "AspNetUserLogins", "UserId", "ProviderKey", "LoginProvider");
                 }
             }
    
             return false;
         }

First of all, VerifyColumns is used as a method to verify columns. It will obtain the columns in the table based on the existing sqlconnection and table name, and ensure that all default columns in the table created by default exist. Then the VerifyColumns is called in the IsIdentityV1Schema method. If the default columns in all tables exist, it means that the verification is passed.

 /// <summary>
         ///     Constructor which takes the connection string to use
         /// </summary>
         /// <param name="nameOrConnectionString"></param>
         /// <param name="throwIfV1Schema">Will throw an exception if the schema matches that of Identity 1.0.0</param>
         public IdentityDbContext(string nameOrConnectionString, bool throwIfV1Schema)
             : base(nameOrConnectionString)
         {
             if (throwIfV1Schema && IsIdentityV1Schema(this))
             {
                 throw new InvalidOperationException(IdentityResources.IdentityV1SchemaError);
             }
         }

The constructor of IdentityDbContext will call throwIfV1Schema to validate the data table based on your connection string. After the verification is passed, the IdentityDbContext is constructed and initialized.

  /// <summary>
     ///     DbContext which uses a custom user entity with a string primary key
     /// </summary>
     /// <typeparam name="TUser"></typeparam>
     public class IdentityDbContext<TUser> :
         IdentityDbContext<TUser, IdentityRole, string, IdentityUserLogin, IdentityUserRole, IdentityUserClaim>
         where TUser : IdentityUser
     { ........
       ........
      public IdentityDbContext(string nameOrConnectionString, bool throwIfV1Schema)
             : base(nameOrConnectionString)
         {
             if (throwIfV1Schema && IsIdentityV1Schema(this))
             {
                 throw new InvalidOperationException(IdentityResources.IdentityV1SchemaError);
             }
         }
      .........
      ........
 }

All the above methods are in the IdentityDbContext class. This shows when you use the properties of a custom model to attach to the default table generated by identityDbContext. Asp.net Identity uses this class as your IdentityDbContext instead of the default IdentityDbContext below.

  /// <summary>
     /// Default IdentityDbContext that uses the default entity types for ASP.NET Identity Users, Roles, Claims, Logins. 
     /// Use this overload to add your own entity types.
     /// </summary>
     public class IdentityDbContext :
         IdentityDbContext<IdentityUser, IdentityRole, string, IdentityUserLogin, IdentityUserRole, IdentityUserClaim>
     {
       ........
     }

So back to your original question. The SQL log shows that a query statement is executed multiple times in a short time is by design. IsIdentityV1Schema will call VerifyColumns multiple times to query all tables generated by Asp.net Identity. You don't have to worry about the performance problems caused by this.



If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our  documentation  to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Bruce Zhang




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.