Thursday, December 20, 2012

Where I can find SQL Generated by Entity framework?

Few days back I was optimizing the performance with Entity framework and Linq queries and I was using LinqPad and looking SQL generated by the Linq or entity framework queries. After some point of time I got the same question in mind that how I can find the SQL Statement generated by Entity framework?

After some struggling I have managed to found the way of finding SQL Statement so I thought it would be a great idea to write a post about  same and share my knowledge about that. So in this post I will explain how to find SQL statements generated Entity framework queries.

To demonstrate the idea Let’s a very simple console application with C# and then  create a table called ‘Customer’ with CustomerId and CustomerName field in sql server.


SQL Generated by Entity framework

Now once our table is ready it’s time to create a Entity framework model.

How to Find SQL Generated by Entity Framework - Entity Framework Model

Now once Model is ready It’s time to write a simple query in console application where we want to get all customer name like following.

using System;
using System.Runtime.CompilerServices;
using System.Linq;
using System.Data;

namespace EntityframeworkSQL
{
    class Program
    {
        static void Main(string[] args)
        {

            using (CustomerEntities customerEntities = new CustomerEntities())
            {
                var customerNames = from c in customerEntities.Customers
                             select c.CustomerName;
                string sql = ((System.Data.Objects.ObjectQuery)customerNames).ToTraceString();

                Console.WriteLine(sql);

            }
            
        }
    }
}

So here in the above code for that. here I have created a object of my customer entity with help of using and then written a simple query to get all the customer name. Now with the help of System.Data.Objects.ObjectQuery class ToTraceString method I am able to find the SQL Generated by the statement. Here I have direct cast customerNames to ObjectQuery class and find sql generated by statement via ToTraceString method.

Now let’s run this console application via pressing F5 and following is a output as expected.

EntityFrameworkSQLQuery

As you can see the SQL statement generated by entity framework. Hope you like it. Stat tuned for more.

Shout it

kick it on DotNetKicks.com
Share:

12 comments:

  1. Only issue I see is that the sql in the command window doesn't match what was asked of the linq query. You asked for CustomerName and the sql is returning the CustomerID and the CustomerName. Why not just use the SQL Server Profiler ?

    ReplyDelete
  2. The only issue I see is the sql in the command window doesn't match what was asked of the LINQ query. The LINQ query asked for the CustomerName whereas the sql in the command window is asking for the CustomerID and the CustomerName. Why not just use the SQL Server Profiler ?

    ReplyDelete
  3. Nice! Thanks for this. This will help with a project of mine.

    ReplyDelete
  4. @LN- If you see the query carefully its fetching the customer name only!! where you see the customerid?

    ReplyDelete
  5. @google-48856e094345038f4eea4ff63eb755f8:disqus - SQL profiler is good way but sometime we need allow to use sql profiler with limtied access

    ReplyDelete
  6. @disqus_p4nxFVmxZ5:disqus - thanks for compliements!!

    ReplyDelete
  7. @a51a37bdcf25ac8ab00ba6a0204b042a:disqus - thanks for compliments

    ReplyDelete
  8. How I can find SQL staement for Linq-To-SQL Queries

    ReplyDelete
  9. @disqus_6YlGyHjTo0:disqus - I will post that in next blog post!!

    ReplyDelete
  10. I think this for Entity Framework using Model-first approach but is it same for Code-first approach or else ?

    ReplyDelete
  11. @facebook-762153671:disqus - I think it should work for both but still have not tried it on code first approach but I will try and let you know.

    ReplyDelete

Your feedback is very important to me. Please provide your feedback via putting comments.

Support this blog-Buy me a coffee

Buy me a coffeeBuy me a coffee
Search This Blog
Subscribe to my blog

  

My Mvp Profile
Follow us on facebook
Blog Archive
Total Pageviews