Entity Framework 小技巧五 —— 如何得到EF查询生成的SQL？ 不及格的程序员

Entity Framework 小技巧五 —— 如何得到EF查询生成的SQL？

 

最近挺忙的，只能为大家带来一些使用EF的小技巧。有关EF4.1的使用与探索将在之后一段时间为大家奉上。今天为大家带来如何得到EF查询所生成的SQL。

在EF 4和EF 3.5 SP1中，我们可以使用ToTraceString()方法得到EF查询所生成的SQL。 

using (var context = new TestDBEntities())
{
    var query = from p in context.Parents
                where p.Name == "Lingzhi"
                select p;

    ObjectQuery<Parent> parents = query as ObjectQuery<Parent>;
    if (parents != null)
    {
        string sql = parents.ToTraceString();
    }
}

这里所生成的SQL为：

SELECT 
[Extent1].[ParentID] AS [ParentID], 
[Extent1].[Name] AS [Name]
FROM [dbo].[Parent] AS [Extent1]
WHERE N'Lingzhi'=[Extent1].[Name]

在EF 4.1中，我们可以直接调用DbQuery<>的ToString()方法得到所生成的SQL。

using (var context = new MyDbContext())
{
    var people = from p in context.People
                 where p.PersonID > 100
                 select p;

    string sql = people.ToString();
}

所生成的SQL是：

SELECT 
[Extent1].[PersonID] AS [PersonID], 
[Extent1].[Name] AS [Name]
FROM [dbo].[People] AS [Extent1]
WHERE [Extent1].[PersonID] > 100

大家应该已经猜到，这里的ToString()方法其实也就是调用了ObjectQuery<>的ToTraceString()方法。DbQuery<>.ToString() ==> System.Data.Entity.Internal.Linq.InternalQuery<>.ToString()方法，此方法在.NET Reflector得到的实现是这样的：

public override string ToString()
{
    return this._objectQuery.ToTraceString();
}

以上讨论的方法都只能得到普通Select查询所生成的SQL，像其他Loading啊，更新啊，删除啊，添加等所生成的SQL并不能得到。EF并不像LINQ to SQL有DataContext.Log可以得到所有被执行的SQL命令。要实现这样的功能，需要自己拓展一下EF的data provider，比如这个例子：http://code.msdn.microsoft.com/EFProviderWrappers-c0b88f32。（可能的话，以后会为大家详细介绍下这个很酷的provider）

当然我们也可以使用Visual Studio 2010 Ultimate所带的IntelliTrace功能或者SQL Server Profiler来得到EF所生成的SQL。 不过这两种方法只针对SQL Server和对应的T-SQL。

PS1：这里为大家带来一个好消息：微软一站式实例代码库(Microsoft All-In-One Code Framework)即日起正式迁移至MSDN代码库了，新的平台会帮您更轻松地解决开发难题、节省更多时间、获得更友好的用户体验。本人作为这个项目的元老，见到我们已拥有600多个经典的代码实例，甚感欣慰啊！  更详细信息，请看http://msdn.microsoft.com/zh-cn/hh124104.aspx?ocid=ban-f-cn-loc-OC201104-MSDN

最新的代码浏览器也发布啦！为大家带来了更多很cool的功能，比如1）代码按需下载 2）实例集中化管理  3）自动更新。

http://blog.csdn.net/MSCodeSample/archive/2011/04/18/6331382.aspx 

之后我将尽力为大家带来更多有关EF的代码实例以及相关的介绍！

PS2：同事开发了一个很cool的MSDN论坛桌面小工具，绝对给力！欢迎使用！（我也出了不少力啊）

也欢迎到MSDN中文论坛ADO.NET与LINQ论坛来提问EF的问题啊，可以试试直接报我的名字Michael Sun，哈哈！ 

如需转发请注明原文出处，谢谢： http://www.cnblogs.com/LingzhiSun/archive/2011/05/05/EF_Trick5.html

---

Logging all SQL statements done by Entity Framework

One of the disadvantages of using an ORM is that your application doesn’t know which SQL commands are being executed. In this post I’m going to show you how to log all the SQL statements that are executed by Entity Framework. This is very useful if you want to look at the query executed and the time take by each to catch some possible pitfalls, or to keep a log of all the commands executed if you have your own custom replication/synchronization strategy.

The need

I was exactly in that latter scenario: the backend acts on one DB, while the frontend reads data from another DB. And for many reasons we are not using standard SQL Server replication. Instead we have a custom replication in place: we log all the SQL UPDATE, INSERT, DELETE to a journal table and we have a scheduled script that gets all the statements from the source DB and executes them in the destination DB. Using standard data access technologies this is simple: when we create the SQL statement we also store it in the journal table: but with ORM we are not in control of that process anymore.

The solution

Unfortunately Entity Framework misses the Log property that Linq2Sql had so I needed to go a bit more inside the core. I knew that EF had some kind of interceptor because otherwise the EFProfiler would have not been possible, so a bit of googling brought me to the Tracing and Caching Provider Wrappers for Entity Framework, developed by Jaroslaw Kowalski. These providers, together with a small sample file you have to copy your application, gives you the possibility to get the Log property back, to inject a Cache or to do more advanced operations using the CommandExecuting, CommandFinished, CommandFailed events. You can read more about the API in the blog post where they announce it.

For my specific scenario I had to write an handler for the CommandFinished event that gets the DbCommand object and transforms it to the SQL statement.

How to do it

Let’s see how this is done.

1 - Get the providers

First thing you have to download the provider wrappers with the sample apps from the MSDN Gallery site. The latest version (v1.02) is for EF v4, but if you are like me and are still using EF v1 you have to get version 1.

The file you get is just a Visual Studio Solution, so you have to open it, change the configuration to Release and build it. You then open theEFProviderWrapperDemo project, go the Bin\Release folder and get the 3 DLLs:FCachingProvider.dll, EFTracingProvider.dll andEFProviderWrapperToolkit.dll.

2 – Setting up your application

You take the 3 DLLs mentioned above and you add them as references to you application: the EFProviderWrapperToolkit is always needed, and then the one that you need for your application: in my scenario I didn’t need caching, so I just added the EFTracingProvider one.

Then the tricky part comes: from the EFProviderWrapperDemo project you have to take the ExtendedNorthwindEntities.cs file, copy in the folder where you have your Entity Framework edmx file, and modify it to match your scenario (change the name of the class and the connection string). For example, my EDMX file was called PhotoLibEntities, so I renamed the file (and class) toExtendedPhotoLibEntities, and modified the code as follows:

public partial class ExtendedPhotoLibEntities : PhotoLibEntities
{
    private TextWriter logOutput;

    public ExtendedPhotoLibEntities()
        : this("name=PhotoLibEntities")
    {
    }

    public ExtendedPhotoLibEntities(string connectionString)
        : base(EntityConnectionWrapperUtils.CreateEntityConnectionWithWrappers(
                connectionString,
                "EFTracingProvider"
        ))
    {
    }
...
}

Notice that if you need just one provider, you can remove the registration of the one you don’t need (as I did).

You also need to register the provider in you application putting some entries in the app.config (or web.config) file:

<system.data>
  <DbProviderFactories>
    <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>

Here I added only the tracing provider, but if you needed the caching provider, you have to add it in the configuration file as well.

If you don’t want, or cannot change your code, you can also inject the provider by manually modifying the SSDL files. You can read more about this in the original blog post by Jaroslaw Kowalski.

3 – Using the Extended ObjectContext

Now that everything is correctly setup you can use the extended ObjectContext to have access to the logging and caching features provided by the wrapper.

using (ExtendedPhotoLibModel context = CreateExtendedPhotoLibModel())
{
    //Do stuff
    context.SaveChanges();
}

I use an helper method instead of instantiating the extended context directly because this way I can setup the event handler I need to store the statements to the journal table.

private ExtendedPhotoLibModel CreateExtendedPhotoLibModel()
{
    ExtendedPhotoLibModel context = new ExtendedPhotoLibModel();

    photoLibModel.CommandFinished += (sender, e) =>
    {
        if (e.Method.Equals("ExecuteNonQuery"))
        {
            string sqlStr = e.Command.CommandText;
            foreach (DbParameter parameter in e.Command.Parameters)
            {
                sqlStr = sqlStr.Replace(parameter.ParameterName,
                            ToDbFormattedString(parameter.Value));
            }
            sqlStr = sqlStr.Replace("\r\n", " ");
            PhotoJournalLogger.AddEntry(sqlStr);
        }
    };
    return context;
}

private static string ToDbFormattedString(object p)
{
    if (p is DateTime)
        return "CONVERT(datetime,'" + ((DateTime) p).ToShortDateStr() + "',103)";
    if (p is String)
        return "'" + (p as string).Replace("'", "''") + "'";
    return p.ToString();
}

My event handler gets the command text, cycles through the parameters and modifies the command text replacing the parameter’s names with their values.

Here is an example of how the CommandText looks like:

update [dbo].[NEWS_PHOTOGALLERY]
set [bookID] = @0,
    [date] = @1,
    [numPhoto] = @2,
    [picture] = null,
    [status] = @3,
    [titleID] = null
where ([id] = @4)

If you just need to log the statements you could also call thee.ToTraceString() method and you would have got the following text (with also all the values for the parameters):

update [dbo].[NEWS_PHOTOGALLERY]
    [date] = @1,
    [numPhoto] = @2,
    [picture] = null,
    [status] = @3,
    [titleID] = null
where ([id] = @4)

-- @0 (dbtype=Int32, size=0, direction=Input) = 34
-- @1 (dbtype=DateTime, size=0, direction=Input) = 08/12/2010 12.21.38
-- @2 (dbtype=Int32, size=0, direction=Input) = 0
-- @3 (dbtype=AnsiStringFixedLength, size=1, direction=Input) = "0"
-- @4 (dbtype=Int32, size=0, direction=Input) = 234

Further usages

This is just the basic usage, but if you want you can extend the logging to also include the duration of the command (very useful for performance tuning) or even the whole command tree.

I struggled a bit to find the solution, and I hope this helps.

Tags: Entity Framework,tracing,caching

comments powered by Disqus