DotNetStories
How to profile LINQ to Entities queries in your asp.net applications - part 2
In this post I will continue exploring ways on how to profile database activity when using the Entity Framework as the data access layer in our applications.
I will use a simple asp.net web site and EF to demonstrate this. If you want to read the first post of the series click here . In this post I will use the Tracing Provider Wrappers which extend the Entity framework. You can download the whole solutions/samples project from here .The providers were developed from Jaroslaw Kowalski . 1) Unzip the .zip file and then load the EFProviderWrappers.sln . Build the solution and the projects inside it. 2) Then you can browse inside the various folders (EFProviderWrapperToolkit,EFTracingProvider) and locate inside there in the respective Bin folders the EFProviderWrapperToolkit.dll and the EFTracingProvider.dll. You will need those later on. 3) Inside the EFProviderWrappers.sln, there is another project - EFProviderWrapperDemo.
Locate the ExtendedNorthwindEntities.cs file and have a look in the first line
public partial class ExtendedNorthwindEntities : NorthwindEFEntities
and the tracing extensions (methods,events and properties) inside the file.We will use most of this code in our example. I assume that you have access to a version of SQL Server and Northwind database. If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here 4) Launch Visual Studio 2010/2008 in Administrator mode. (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language. 5) Add a new item in your site. Add a ADO.Net Entity Data Model and name it Northwind.edmx.Place this file in the App_Code special folder. 6) From the wizard choose (create model from database) and select only the Customers, Orders and Categories tables to be included in our model. Inside the Northwind.Designer.cs file we see the code
public partial class NorthwindEntities : ObjectContext
This our main class NorthwindEntities that inherits and extends the ObjectContext class. This is the class that we normally instantiate in our client applications. In this project this is not the case.We need to add the new class that extends our NorthwindEntities class. Add new special folder Bin in your site and drag and drop in there the EFProviderWrapperToolkit.dll , EFTracingProvider.dll. 7) Add another file in your site, ExtNorthwindEntities.cs. Inside this file the code, in my case anyway, looks like this
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using EFProviderWrapperToolkit;
using EFTracingProvider;
using NorthwindModel;
public partial class ExtNorthwindEntities : NorthwindEntities
{
private TextWriter logOutput;
public ExtNorthwindEntities()
: this("name=NorthwindEntities")
{
}
public ExtNorthwindEntities(string connectionString)
: base(EntityConnectionWrapperUtils.CreateEntityConnectionWithWrappers(
connectionString,
"EFTracingProvider"
))
{
}
#region Tracing Extensions
private EFTracingConnection TracingConnection
{
get { return this.UnwrapConnection<EFTracingConnection>(); }
}
public event EventHandler<CommandExecutionEventArgs> CommandExecuting
{
add { this.TracingConnection.CommandExecuting += value; }
remove { this.TracingConnection.CommandExecuting -= value; }
}
public event EventHandler<CommandExecutionEventArgs> CommandFinished
{
add { this.TracingConnection.CommandFinished += value; }
remove { this.TracingConnection.CommandFinished -= value; }
}
public event EventHandler<CommandExecutionEventArgs> CommandFailed
{
add { this.TracingConnection.CommandFailed += value; }
remove { this.TracingConnection.CommandFailed -= value; }
}
private void AppendToLog(object sender, CommandExecutionEventArgs e)
{
if (this.logOutput != null)
{
this.logOutput.WriteLine(e.ToTraceString().TrimEnd());
this.logOutput.WriteLine();
}
}
public TextWriter Log
{
get { return this.logOutput; }
set
{
if ((this.logOutput != null) != (value != null))
{
if (value == null)
{
CommandExecuting -= AppendToLog;
}
else
{
CommandExecuting += AppendToLog;
}
}
this.logOutput = value;
}
}
#endregion
}
Basically I copied out the code from the EFProviderWrapperDemo and the ExtendedNorthwindEntities.cs file into my own class file,leaving out the caching information.
8) Add a new item to your site, a web form. Leave the default name. Drag and drop a Gridview control on the form. In the Page_Load event handling routine of the Default.aspx type
using (TextWriter logFile = File.CreateText("C:\\data\\mylogfile.txt"))
{
using (var ctx = new ExtNorthwindEntities())
{
ctx.Log = logFile;
var query = from c in ctx.Customers
where c.Orders.Any(o => o.Freight > 500)
select c;
GridView1.DataSource = query;
GridView1.DataBind();
}
}
I created a text file in my hard disk and use the extended (with tracing info) class. Then I created an instance of this class and set the Log property of the object to the logfile object. Let's try to add more functionality to our site by inserting a record to our Categories table. 9) Add a new item to your site, a web form. Leave the default name-Default2.aspx. Drag and drop a Gridview control on the form.Add a button on the web form.In the Default.aspx page drop a Hyperlink control. The Hypelink control points to the Default2.aspx page.
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/Default2.aspx">
HyperLink</asp:HyperLink>
10) The code for the partial class follows:
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
InsertIntoCategories();
LoadCategories();
}
void LoadCategories()
{
using (TextWriter logFile = File.AppendText("C:\\data\\mylogfile.txt"))
{
using (var ctx = new ExtNorthwindEntities())
{
ctx.Log = logFile;
var myquery = from cat in ctx.Categories
select cat;
GridView1.DataSource = myquery;
GridView1.DataBind();
}
}
}
void InsertIntoCategories()
{
using (TextWriter logFile = File.AppendText("C:\\data\\mylogfile.txt"))
{
using (var ctx = new ExtNorthwindEntities())
{
ctx.Log = logFile;
Category cat = new Category();
cat.CategoryID = 10;
cat.CategoryName = "Nice food";
ctx.AddToCategories(cat);
ctx.SaveChanges();
}
}
}
}
11) Run your website and see the records being displayed in the Default.aspx page. Click the hyperlink and you will navigate to the Default2.aspx pag and click the button in that page. Observe the new record added to the Categories table. 12) When I navigate to the mylogfile.txt I see all the queries executed against the server logged into this file. In my case the contents of the log file are: SELECT Drop me an email If you want me to send you the source code. Hope it helps!!!
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[ContactName] AS [ContactName],
[Extent1].[ContactTitle] AS [ContactTitle],
[Extent1].[Address] AS [Address],
[Extent1].[City] AS [City],
[Extent1].[Region] AS [Region],
[Extent1].[PostalCode] AS [PostalCode],
[Extent1].[Country] AS [Country],
[Extent1].[Phone] AS [Phone],
[Extent1].[Fax] AS [Fax]
FROM [dbo].[Customers] AS [Extent1]
WHERE EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Orders] AS [Extent2]
WHERE ([Extent1].[CustomerID] = [Extent2].[CustomerID]) AND ([Extent2].[Freight] > cast(500 as decimal(18)))
)
insert [dbo].[Categories]([CategoryName], [Description], [Picture])
values (@0, null, null)
select [CategoryID]
from [dbo].[Categories]
where @@ROWCOUNT > 0 and [CategoryID] = scope_identity()
-- @0 (dbtype=String, size=15, direction=Input) = "Nice food"
SELECT
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName],
[Extent1].[Description] AS [Description],
[Extent1].[Picture] AS [Picture]
FROM [dbo].[Categories] AS [Extent1]