Database Command Logging & Profiling in Entity Framework 6.0
In this post I am going to provide you with a hands-on example on how to log commands & queries sent to the database by Entity Framework 6.0.
You can use the fantastic Entity Framework third party profiler by Hibernating Rhinos if you want. You can find a similar post here.
Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.
Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time. You can find many posts regarding Entity Framework in this blog.
1) Create an empty ASP.Net Application (Web Forms Application) and give it the name EFLogging. I am using Visual Studio 2013 Ultimate edition.
2) Add a new web forms page in the application. Leave the default name.
3) I will use the AdventureWorks2014 database (You can download it here) for this application and more specifically the Person.Person table. I have installed SQL Server 2014 Enterprise edition in my machine. SQL Express edition will work fine.
4) I will add an ADO.Net Entity data model using Database First. Follow the wizzard steps, create the connection string and then import into the conceptual model the Person.Person table which will become an entity in the domain model.
5) I will write a sql statement to acquire rows from the Person.Person table. Connect to your SQL Server instance. Type the following query.
This query after execution will return 5 rows.
SELECT [FirstName]
FROM [AdventureWorks2014].[Person].[Person]
WHERE FirstName = 'Rob'
6) In our webform in the Page_Load event we write the following code. We change the name Rob to Robby.
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new AdventureWorks2014Entities())
{
ctx.Database.Log = T => Debug.Write(T);
var query = from p in ctx.People
where p.FirstName=="Rob"
select p;
foreach (var item in query)
{
item.FirstName = "Robby";
}
ctx.SaveChanges();
}
}
7) You can see in the output that it logs all the activities performed by EF e.g. opening & closing connection, execution & completion time and database queries & commands.
Have a look at the picture below
Hope it helps!!!