Interview question - can you find out what SQL statement EF is running?

I had an interview this morning and Entity Framework came up. The guy asked how we can identify why an EF call is slow. I can you can look at the SQL statement it generates. You can step thru the code, grab the SQL it generates. He said EF doesn't do SQL (or something like that).

I also said EF generates complicated SQL statements and brings back columns of data that might not be needed.

I think you can grab the SQL statement EF generates. A couple of years ago, I worked on a project that we would grab the EF's generated SQL.

He also asked how we can improve EF's performance. I said maybe break up the call they're making to get the data.

I think he wanted an answer to take back to his developer. He said he keeps asking his developer why the EF call is slow and the guy says he doesn't know :)
LVL 8
CamilliaAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
SQL Profiler is one way to see absolutely all the requests that are sent to the SQL Server. You cannot hide anything to it!

There are also tools like https://www.hibernatingrhinos.com/products/efprof that will help you: https://www.hibernatingrhinos.com/products/efprof
0
 
CamilliaAuthor Commented:
Thanks. I think I mentioned to him Sql profiler. I'll take a look at the links you sent.
0
 
Fernando SotoRetiredCommented:
Hi Camillia;

The DbContext.Database.Log property can be set to a delegate for any method that takes a string such as Console.Write as shown below or any method that takes a string. All SQL generated by the current context will be logged to that method.
using (var context = new DbContextName())
{
    context.Database.Log = Console.Write;
 
    // Your code here...
}

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
CamilliaAuthor Commented:
Thanks, Fernando. I have a second interview with them tomorrow. I'll mention this to them.
0
 
Fernando SotoConnect With a Mentor RetiredCommented:
See this link for more info. DbContext Logging.
1
 
CamilliaAuthor Commented:
Thanks, Fernando
0
 
PortletPaulConnect With a Mentor freelancerCommented:
Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write.
https://msdn.microsoft.com/en-us/library/aa937723(v=vs.113).aspx
i.e. It definitely does generate SQL

Any command sent from the EF to the database can now be logged. To view the generated queries from EF 6.x, use the DBContext.Database.Log property
(ref) as already mentioned by Fernando

See: Entity Framework Logging and Intercepting Database Operations (EF6 Onwards)
1
 
CamilliaAuthor Commented:
Thanks paul
0
 
Fernando SotoRetiredCommented:
Hi Camillia;

Here is a list of Performance Considerations for EF 4, 5, and 6 which was also part of your question.
0
 
CamilliaAuthor Commented:
Thanks, Fernando. I'm going over to have a second interview today and I'll share these with them. It's ok if I don't the job.. I'll share anyway and help them out :)
1
 
CamilliaAuthor Commented:
Thanks. I went for my second interview and gave the manager these solutions. I learned something new about EF as well :)
0
 
CamilliaAuthor Commented:
I got the job! :)
1
 
PortletPaulfreelancerCommented:
Wooo hoooo!! Congratulations.
0
 
CamilliaAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.