Skip to main content

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

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

Post a Comment

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

Popular posts from this blog

How to create Rest API(Web API) with ASP.NET Core 1.0

Recently Microsoft has released ASP.NET Core 1.0 RC2, I am getting lots of request from readers that about creating Web API. So I thought it will be a good idea to write a blog post about how to create Rest API(Web API) with ASP.NET Core 1.0.

So let’s get started via creating an ASP.NET Core 1.0 Web Application like following.



Once you click on ASP.NET Web Application, It will ask whether you need to create Web Application or Web API application. We are going to Web API so I am going to select Web API Application like following. Please note that in ASP.NET Core 1.0 there is no separate libraries or DLLs required for creating web APIs. This is just a project template.



Now once you click on OK It will create a Web API application with default values controller and program.cs.  As you know Program.cs is now starting point for the ASP.NET Core 1.0 application so It contains all the required configuration and startup items. Following is a code for that.
using System.IO; using Microsoft.Asp…

How to use migration with Entity Framework Core

Entity framework core is  the lightweight, extensible and cross-platform version of Entity Framework. Before some time, Microsoft has Released a new version of Entity Framework RC2. I have written a couple of blog post about Entity framework code first migration earlier for Entity framework 6.0. So there was a couple of request coming for writing a blog post about Entity Framework Core RC2 migration. So I thought it will be a good idea to give an overview how database migration works in Entity Framework Core RC2. This post will cover a basic scenario where we are going to create the database with existing ASP.NET Identity migration and then we are going to create a new model and have that migration applied in the database.

How to use Entity Framework Migrations: Let’s get started, To demonstrate entity framework core migrations, I am going to create a sample asp.net core web application like following.



Once we select asp.net core application it will appear the following dialog.



Now w…

Solution: gulp is not recognized as internal or external command- Visual Studio

Recently, After Upgrading to the newer version of Gulp, I was getting this error.  After doing lots of  finding online and in my system I was able to figure out that Modules like Gulp does not installed to the path. So when you try to run it from the command line it was showing below error.
gulp is not recognized as internal or external command How to solve : gulp is not recognized as internal or external command To solve this error we just need to create a new environment variable and set the NPM modules path there.

To create an environment variable,  Right click My Computer/ This Pc and click on properties.  It will open following windows.



Go to Advance System Settings and it will open system properties windows like below.



Click on Environment Variables it will open an environment and system variables windows like below.



Click on new System variable and put NODE_PATH in system variable name and %AppData%\npm\node_modules into the variable value like below.



Click on Ok. That’s it. N…