Monday, August 27, 2012

How to Convert Linq Entity Query into T-SQL ?


Why Should We Convert ?

1. We all know we are better T-Sql professionals than quite new Entity framework.Hence by converting entity query into T-Sql, we can understand what's going on when it runs on SQL Server better.

2. We can use generated T-Sql query to identify the Index Keys for tables which are needed for gain more performance.(as an input for Database Engine Tuning Adviser- my next blog-post will explain how to do)

3. By studying those generated T-Sql we can improve our Entity query for better performances (using SQL Server Execution Plans etc.).

How To Do That?

Method 1 :

You can use LINQPad.It's Free http://www.linqpad.net/
(Note: my next blog-post will explain how to configure LinqPad with Visual Studio )

LINQPad

































Method 2 :

You can use SQL Server Profiler inside the Sql Server (Tools --> SQL Server Profiler)


SQL Server Profiler

Method 3 :

You can use Visual Studio Debugger for Generate T-Sql.(with any visual studio version)

Visual Studio Debugger

Note: One thing I need to mention here is After request the data from Database Server by using FirstOrDefault() , ToList() ,etc then you cannot use above method.So you have to investigate T-Sql Before calling it to the Database server (the way I showed in above image).

Conclusion
You can use one of the above method to investigate the T-Sql generation from EF Query Without using High Paid 3rd party tools or Visual Studio Ultimate edition.

Happy Coding.


May Be Use Full To You :
How to Enable jQuery Intellisense in Visual Studio 2010 ?
How to Disable a Button Using Jquery and CSS ?
How to Improve Performance of Entity Framework Query (over 400 times)?

11 comments:

  1. It's very use full.Keep it up.

    ReplyDelete
  2. Interesting ! Would like to read next article !

    ReplyDelete
    Replies
    1. Hi Hasitha,

      Yep,I will write above mentioned articles very soon.
      Keep in Touch.

      Delete
  3. Sampath thanks for this article is there any way i can change my Sql Query to Linq

    ReplyDelete
    Replies
    1. Hi Santosh,

      Yep,You can.For that you have to use "Linqer"
      Linqer is a SQL to LINQ conversion tool. It helps learning LINQ and convert existing SQL statements.
      Not every SQL statement can be converted to LINQ, but Linqer covers many different types of SQL expressions. Linqer supports both .NET languages - C# and Visual Basic.

      Here is the Link : http://www.sqltolinq.com/

      Delete
  4. Interesting ! How do we convert SQL to Linq ?

    ReplyDelete

Thanks for your Feedback.