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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

CamilliaAuthor Commented:
Thanks, Fernando. I have a second interview with them tomorrow. I'll mention this to them.
0
Fernando SotoRetiredCommented:
See this link for more info. DbContext Logging.
1
CamilliaAuthor Commented:
Thanks, Fernando
0
PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
Wooo hoooo!! Congratulations.
0
CamilliaAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.