How To Determine The Generated SQL Queries In Entity Framework 4

With the help of Entity Framework, you may never write a single SQL Query code in your programming projects. Some of the benefits of Entity framework include:

1. Visual Studio intellisense
2. Compile checking
3. Faster development
4. Auto generated domain objects
5. Lazy loading, etc.

However, sometimes you need to know how Entity Framework translates your LINQ Query into a SQL Query. You can't just trust the framework to correctly formulate the Query. If you are concerned about scalability and performance, you need to check and analyze the underlying query and if possible modify or refine it to increase querying speed and performance.

The easiest way to view the generated SQL is to use a profiler tool that's included in database management tools (for instance, SQL Server Manangement Studio). Using this tool, you can monitor all SQL statements executed against the database. But using this tool requires that you have access to the database. Fortunately, with Entity Framework 4, you can use ObjectSet class's ToTraceString method. 

Here's the example code on how to do it in C# and VB.NET: 

C#:

var result = ctx.Orders.Where(o => o.Date.Year == DateTime.Now.Year);
var SQL = (result as ObjectQuery).ToTraceString();

VB:

Dim result = ctx.Orders.Where(Function(o) o.Date.Year = DateTime.Now.Year)
Dim SQL = TryCast(result, ObjectQuery).ToTraceString()