Tracing and Caching for Entity Framework available on MSDN Code Gallery

  • Article

We have just released a sample that shows how to extend Entity Framework in interesting ways by plugging into ADO.NET provider interface. The sample provides two extensions:

  • EFTracingProvider – which adds the ability to log all SQL commands that are executed (similar to LINQ to SQL’s DataContext.Log
  • EFCachingProvider – which adds transparent query results cache to EF

The sample comes with implementation of distributed cache which uses Velocity CTP 3 as well as an adapter for ASP.NET and simple in-memory cache implementation.

Because the sample is quite large and uses many advanced techniques, it’s impossible to fully explain it all in one blog post. In this first post I’ll briefly explain the idea of wrapper providers and describe the new the APIs exposed by EFTracingProvider and EFCachingProvider. In future posts I’ll try to explain more technical detail details and provide advanced logging/caching tips.

Provider Wrapers

Entity Framework has a public provider model which makes it possible for provider writers to support 3rd-party databases, such as Oracle, MySQL, PostreSQL, Firebird. The provider model provides uniform way for EF to query the capabilities of the database and execute queries and updates using canonical query tree representation (as opposed to textual queries).

Whenever you issue a LINQ or Entity SQL query through an ObjectContext instance, the query passes through a series of layers (see the picture below). At high level we can say that all queries and updates from ObjectContext are translated and executed through EntityConnection, which in turns talks to server-specific data provider such as SqlClient or Sql Server CE client.

Provider interface used by Entity Framework is stackable, which means it’s possible to write a provider which will wrap another provider and intercept communication between Entity Framework and the original provider.

The wrapper provider gets a chance do interesting things, such as:

  • Examining query trees and commands before they are executed
  • Controlling connections, commands, transactions, data readers, etc.

image

EFTracingProvider intercepts DbCommand.ExecuteReader(), ExecuteScalar() and ExecuteNonQuery() and sends details about the command (including command text and parameters) to configured outputs.

EFCachingProvider is a bit more complex. It uses external caching implementation and caches results of all queries queries that are executed in DbCommand.ExecuteReader(). Whenever update is detected (either UPDATE, INSERT or DELETE) the provider invalidates affected cache entries by evicting all cached queries which were dependent on any of the updated tables.

Using the sample code

Here’s a step-by-step guide to downloading and using the sample code in your project:

  1. Download the sample project from MSDN Code Gallery and built it.
  2. Take EFCachingProvider.dll, EFTracingProvider.dll and EFProviderWrapperToolkit.dll and place them in a common directory for easy referencing.
  3. Add reference to those three assemblies in your application.
  4. Register the providers – either:
    1. Locally for your application (recommended) – put registration entries in App.config (see Provider Registration section below) and make sure that provider DLLs are in your application directory (adding reference and building should take care of that).
    2. Globally: GAC the providers and add required registration entries (see Provider Registration section below) to machine.config
  5. Copy EFProviderWrapperDemo\ExtendedNorthwindEntities.cs from the sample and put it in your project – rename the class as appropriate – you will need to use this class instead of a regular strongly typed object context class.
  6. Modify the base class by replacing NorthwindEntities with the name of your strongly typed object context class.
  7. Modify the default constructor by providing your own connection string.
  8. (optional) You can also modify the second constructor by specifying which wrapper providers to use.

That’s it.

Caching and Tracing APIs

By using ExtendedNorthwindEntities which was created in previous step, instead of NorthwindEntities you get access to new APIs which control caching and tracing:

 public TextWriter Log { get; set; }

Specifies the text writer where log output should be written - same as in LINQ to SQL

 public ICache Cache { get; set; }

Specifies which cache should be used for the context (typically a global one). The sample comes with 3 implementations of ICache interface which you can be used in your applications:

  • AspNetCache – cache which uses ASP.NET caching mechanism
  • InMemoryCache – simple, in-memory cache with basic LRU expiration policy
  • VelocityCache – implementation of caching which uses Microsoft Distributed Cache codename "Velocity" CTP3.
 public CachingPolicy CachingPolicy { get; set; }

Specifies caching policy. There are 3 policies included in the package:

  • CachingPolicy.CacheAll – caches all queries regardless of their results size or affected tables
  • CachingPolicy.NoCaching – disables caching
  • CustomCachingPolicy – includes user-configurable list of tables that should and should not be cached, as well as expiration times and result size limits.

It is also possible to write your own caching policy by creating a class which derives from CachingPolicy and overriding a bunch of methods.

For more advanced logging scenarios there are also 3 events, which provide access to raw DbCommand objects and some additional information:

 public event EventHandler<CommandExecutionEventArgs> CommandExecuting
public event EventHandler<CommandExecutionEventArgs> CommandFinished
public event EventHandler<CommandExecutionEventArgs> CommandFailed

The events are raised before and after each command is executed.

Global configuration

You can also configure logging defaults through static properties of EFTracingProviderConfiguration class and they will apply to all new contexts:

 public static bool LogToConsole { get; set; }

Specifies whether every SQL command should be logged to the console.

 public static string LogToFile { get; set; }

Specifies global log file.

 public static Action<CommandExecutionEventArgs> LogAction { get; set; }

Specifies global custom logging action – a delegate that will be invoked before and after each command is executed.

Tracing Example

In order to write all SQL commands to a file, you must create a text writer object to write to and assign it to context.Log:

 using (TextWriter logFile = File.CreateText("sqllogfile.txt"))
{
  using (var context = new ExtendedNorthwindEntities())
  {
    context.Log = logFile;     // ... 
  }
}

Logging to the console is even easier:

 using (var context = new ExtendedNorthwindEntities())
{
  context.Log = Console.Out;   // ... 
}

More advanced logging can be achieved by hooking up Command*events:

 using (var context = new ExtendedNorthwindEntities())
{
  context.CommandExecuting += (sender, e) =>
  {
   Console.WriteLine("Command is executing: {0}", e.ToTraceString());
  };
  context.CommandFinished += (sender, e) =>
  {
    Console.WriteLine("Command has finished: {0}", e.ToTraceString());
  };   // ... 
}

To enable tracing globally for all connections (to both console and a log file):

 EFTracingProviderConfiguration.LogToConsole = true;
EFTracingProviderConfiguration.LogToFile = "MyLogFile.txt"

Caching Example

In order to use caching using InMemoryCache implementation, you must create global instances of your cache and caching policy objects:

 ICache cache = new InMemoryCache(); 
CachingPolicy cachingPolicy = CachingPolicy.CacheAll;

In order to use caching with Velocity CTP3, you must create DataCache object and pass it to VelocityCache constructor.

 private static ICache CreateVelocityCache(bool useLocalCache)
{
  DataCacheServerEndpoint endpoint = new DataCacheServerEndpoint("localhost", 22233, "DistributedCacheService");
  DataCacheFactory fac = new DataCacheFactory(new DataCacheServerEndpoint[] { endpoint }, useLocalCache, useLocalCache);

  return new VelocityCache(fac.GetCache("Velocity"));
}

Now in order to use either of the caches we need to set up Cache and CachingPolicy properties on the context:

 using (var context = new ExtendedNorthwindEntities())
{
  // set up caching
  context.Cache = cache;
  context.CachingPolicy = cachingPolicy;   // ... 
}

Configuring Providers

Provider Registration

Before a provider can work with Entity Framework it must be registered, either in machine.config file or in application configuration file. Configuration for each provider specifies the factory class and gives it three names, two of which are human-readable name and one - provider invariant name is used to refer to the provider in the connection string and SSDL.

Configuration for the providers included in the sample looks like this:

 <system.data>
  <DbProviderFactories>
    <add name="EF Caching Data Provider"
         invariant="EFCachingProvider"
         description="Caching Provider Wrapper"
         type="EFCachingProvider.EFCachingProviderFactory, EFCachingProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=def642f226e0e59b" />
    <add name="EF Tracing Data Provider"
         invariant="EFTracingProvider"
         description="Tracing Provider Wrapper"
         type="EFTracingProvider.EFTracingProviderFactory, EFTracingProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=def642f226e0e59b" />
    <add name="EF Generic Provider Wrapper"
         invariant="EFProviderWrapper"
         description="Generic Provider Wrapper"
         type="EFProviderWrapperToolkit.EFProviderWrapperFactory, EFProviderWrapperToolkit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=def642f226e0e59b" />
  </DbProviderFactories>
</system.data>

 

This XML fragment can be copied/pasted into any project which needs to use EF provider wrappers or put in the machine.config to be shared by all applications.

Injecting into provider chain

In order to inject the provider into the provider chain, you have to modify SSDL files for your project as well as the connection string. There are two ways to do so: automated (which requires application code changes) or manual which can be done externally just by changing configuration file and SSDL file.

In order to create an EntityConnection with injected wrapped providers, you can use the provided helper method:

 connection = EntityConnectionWrapperUtils.CreateEntityConnectionWithWrappers(
  connectionString, "EFTracingProvider", "EFCachingProvider")

You can then pass connection to ObjectContext constructor or use the connection to run ESQL queries as usual.

Alternative injection method

If you cannot change the application code, you have to make the modifications manually, which involves changing SSDL file and the connection string. Let’s take a quick look to see what SSDL and connection string look like today: The provider name is specified in the Provider attribute of the <Schema/> element:

 <Schema Namespace="NorthwindEFModel.Store"
  Alias="Self"
  Provider="System.Data.SqlClient"
  ProviderManifestToken="2005"
  xmlns="https://schemas.microsoft.com/ado/2006/04/edm/ssdl">

Provider invariant name is also be specified in the connection string:

 <connectionStrings>
  <add name="NorthwindEntities"
       connectionString="metadata=NorthwindEFModel.csdl | NorthwindEFModel.msl | NorthwindEFModel.ssdl;
                         provider=System.Data.SqlClient; 
                         provider connection string=&quot;Data Source=.\sqlexpress;
Initial Catalog=NorthwindEF;Integrated Security=True;MultipleActiveResultSets=True&quot;"
       providerName="System.Data.EntityClient" />
</connectionStrings>

In order to inject our own provider we need to override those to point to our provider. In SSDL, we put the name of the new provider in the Provider attribute and concatenate the previous provider with its provider manifest token in the ProviderManifestToken field, like this:

 <?xml version="1.0" encoding="utf-8"?>
<Schema Namespace="NorthwindEFModel.Store"
  Alias="Self"
  Provider="EFCachingProvider"
  ProviderManifestToken="System.Data.SqlClient;2005"
  xmlns="https://schemas.microsoft.com/ado/2006/04/edm/ssdl">

Modifying connection string is a bit different – we need to put the pointer to the new *.ssdl, change provider name and add new keyword to provider connection string:

 <connectionStrings>
  <add name="NorthwindEntities"
       connectionString="metadata=NorthwindEFModel.csdl | NorthwindEFModel.msl | NorthwindEFModel.Modified.ssdl;
                         provider=EFCachingProvider; 
                         provider connection string=&quot;wrappedProvider=System.Data.SqlClient; Data Source=.\sqlexpress;
Initial Catalog=NorthwindEF;Integrated Security=True;MultipleActiveResultSets=True&quot;"
       providerName="System.Data.EntityClient" />
</connectionStrings>
Specifying tracing configuration in the configuration file:

It is also possible to specify tracing configuration in App.config file. The following parameters are available:

 <appSettings>
  <!-- write log messages to the console. -->
  <add key="EFTracingProvider.logToConsole" value="true" />
    
  <!-- append log messages to the specified file -->
  <add key="EFTracingProvider.logToFile" value="sqllog.txt" />
</appSettings>

Limitations and Disclaimers

The providers have not been extensively tested beyond what’s included in the sample code, so you should use tem at your own risk.

As with any other sample, Microsoft is not offering any kind of support for it, but if you find bugs or have feature suggestions, please use this blog’s contact form and let me know about them.