Where to find translated Linq to Entity query to Sql

Question

I want to get the translated Linq query programmatically and do some stuff with that Sql syntax.

Suppose this is my code:

public class MyApiController:ApiController
{
   public IQueryable<object> Get()
   {
      var objs=Context.Objexts.Where(m=>m.Id>10);
      return objs;
   }
}

I want to find and get the Sql syntax like:

SELECT * FROM dbo.Objexts where Id > 10

Don't get what you want to do. Why get the SQL query generated from a Linq? — jpgrassi, Dec 18 '15 at 11:10
Yeah also don't get why you want to do that, you can set up the query with code, why do you want to alter the actual query? — Theunis, Dec 18 '15 at 11:11

score 9 · Accepted Answer · edited Feb 29 '16 at 00:13

9

You can call the ToString() method on objs. This will result in a call to the ToTraceString method that returns the executed SQL:

string sql = objs.ToString();

edited Feb 29 '16 at 00:13

Sevle

3,109
2
19
31

answered Dec 18 '15 at 11:11

Alex

21,273
10
61
73

Will the sql returned from ToString always be a complete sql statement without parameters? If I use .Where(c => c == "abc'; delete from othertable;"), will the resulting sql be guaranteed to be sql injection safe? – Mathias Rönnlund May 27 '16 at 11:49
Such a trivial but important concept. Half of the times we will start sql query profile to get hold of the query text getting fired on the sql server instance. Sweet! – RBT Jun 13 '16 at 03:34
If objs=Context.Objexts.Where(m=>m.Id>10).Sum(m=>m.num), than how to know translated sql @Jaco? – DVL Oct 12 '18 at 10:23

score 7 · Answer 2 · answered Dec 18 '15 at 13:23

another option if you are using Entity Framework 6 is use the new feature to logging whats is happen, you can get the t-sql and the query time:

Logging and Intercepting Database Operations

using (var context = new BlogContext()) 
{ 
    context.Database.Log = Console.Write; //here, you can write this info to a text file for example.

    // Your code here... 
}

score 4 · Answer 3 · edited May 23 '17 at 12:13

4

I think these posts may help you:

1) How to view LINQ Generated SQL statements?

2) How do I view the SQL generated by the Entity Framework?

Couple of examples (from the above):

var sql = ((System.Data.Objects.ObjectQuery)objs).ToTraceString();
var sql = objs.ToString();

Documentation on .ToTraceString() can be found here:

MSDN trace string documentation

edited May 23 '17 at 12:13

Community

1
1

answered Dec 18 '15 at 11:18

Oblongata

86
5

Where to find translated Linq to Entity query to Sql

3 Answers3