Entity Framework SQL First Database Query

Question

I am working on an ASP.NET MVC App using code first. When I run the app from inside visual studio (in debug or release mode) my first query is always slower because of the following SQL that runs automatically:

SELECT Count(*) 
FROM INFORMATION_SCHEMA.TABLES AS t 
WHERE t.TABLE_TYPE = 'BASE TABLE' AND 
(t.TABLE_SCHEMA + '.' + t.TABLE_NAME IN 
('dbo.LoginAttempt','dbo.Product','dbo.Supplier','dbo.AspNetRoles',
'dbo.AspNetUsers','dbo.AspNetUserClaims','dbo.AspNetUserLogins','dbo.AspNetUserRoles') 
OR t.TABLE_NAME = 'EdmMetadata') -- Executing at 2/27/2014 2:43:58 PM -05:00 -- Completed in 20 ms with result: 8 

SELECT [GroupBy1].[A1] AS [C1] FROM ( 
SELECT COUNT(1) AS [A1] 
FROM [dbo].[__MigrationHistory] AS [Extent1] 
) AS [GroupBy1] -- Executing at 2/27/2014 2:43:59 PM -05:00 -- Completed in 3 ms with result: SqlDataReader 

SELECT [GroupBy1].[A1] AS [C1] FROM ( 
SELECT COUNT(1) AS [A1] 
FROM [dbo].[__MigrationHistory] AS [Extent1] 
WHERE ([Extent1].[ContextKey] = @p__linq__0) 
AND (@p__linq__0 IS NOT NULL) ) AS [GroupBy1] -- p__linq__0: 'CompanyName.Migrations.Configuration' (Type = String, Size = 4000) -- Executing at 2/27/2014 2:43:59 PM -05:00 -- Completed in 2 ms with result: SqlDataReader 

SELECT TOP (1) [Project1].[C1] AS [C1], [Project1].[MigrationId] AS [MigrationId], 
[Project1].[Model] AS [Model] FROM ( 
SELECT [Extent1].[MigrationId] AS [MigrationId], 
[Extent1].[Model] AS [Model], 1 AS [C1] 
FROM [dbo].[__MigrationHistory] AS [Extent1] 
WHERE ([Extent1].[ContextKey] = @p__linq__0) 
AND (@p__linq__0 IS NOT NULL) ) AS [Project1] 
ORDER BY [Project1].[MigrationId] DESC -- p__linq__0: 'CompanyName.Migrations.Configuration' (Type = String, Size = 4000) -- Executing at 2/27/2014 2:43:59 PM -05:00 -- Completed in 2 ms with result: SqlDataReader

I have a few questions regarding this SQL:

When I release this app to production will this SQL still run with the first query?
If it does, will this happen for the first query by each user, or just the first query ran on the site?
Are these queries necessary for EF to work or is there a way to disable them?

Yes, but I won't want to once the app is in production. Is there a way I can turn it off for production only? I tried setting AutomaticMigrationsEnabled = false; in my constructor of the Configuration class but it didn't seem to make a difference. — Jon
– Jon, Commented Feb 27, 2014 at 20:54
I just tried adding that to my Configuration constructor but it still executed the SQL I included in my question. Is that the right place to add it? — Jon
– Jon, Commented Feb 27, 2014 at 21:01
It should be part of an application's initialization, so e.g. in global.asax.cs. — Gert Arnold
– Gert Arnold, Commented Feb 27, 2014 at 21:07

Gert Arnold · Accepted Answer · 2014-02-27 21:19:37Z

4

Except for the first query this is EF checking whether the database still matches the conceptual model (the mapped class model). If there is a difference it will try to migrate the database to the latest version. Since you don't want to use migrations in production you can turn this off by

Database.SetInitializer<YourContext>(null);

in the initialization if your application, global.asax.cs in MVC.

To answer your questions

Yes, it will always run with the first query (unless you turn it off as above).
For the first query run by the application, so when the web app starts, also after each reset of the app pool.
Necessary if you want to use migrations.

answered Feb 27, 2014 at 21:19

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vittore Over a year ago

Well even if you want to use migrations you can have the same assembly with EF context referenced from say console application, and only run migration code there.

Hamid Over a year ago

We have different issue but seams this work for us too. thanks for sharing. We using DB first EF and we host our DB in AWS, without any issue.we plan to move to Azure and we see similar mentioned SQL and failure on 'EdmMetadata' table which we don't have. the error comes frequently but not with pattern and it make troubleshooting difficult. We null the initialization according your solution and seams is working for us too. thanks again

Collectives™ on Stack Overflow

Entity Framework SQL First Database Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related