locked
Show queries generated by DbContext RRS feed

  • Question

  • Is it posssible to show generated sql query in Output window, for example? Now with eager/lazy loading, and many ways to query data, it is very important to know when the database is hit, and what query  was issued. I cannot find any event that can provide this information, and this should exist in entity framework, and not to rely on profilers.
    Saturday, October 15, 2011 12:01 PM

All replies

  • Hi Goran,

    For the DbContext, it's not as easy to get at the sql code as it is using the full ObjectContext.  But you can still do it okay for select queries.  Just do this:

     var results = from a in db.Clients
                              select new { a.Amount, a.Name };
    
      string SqlCode = results.ToString();
    



    Tom Overton
    Saturday, October 15, 2011 12:30 PM
  • Hi, Tom, I am aware of this approach, but the problem is that with this way you end up with query.ToString() scattered all over the code. With lazy/eager loading its important to know when you are hitting the database, and a general place to handle all the context queries would be the best solution. If this can be handled on the global level, you need to remove only few lines of code, which is the general idea.
    Saturday, October 15, 2011 7:25 PM
  • Goran,

    You could use SQL Server profiler, and filter on the traffic from your application.  That's really the best way to examine the SQL going to server and you don't have to change your .net code at all.

     

     

     

     


    Tom Overton
    Saturday, October 15, 2011 7:43 PM
  • As I have said in my first post, we should not be needing a profiler for such a simple feature. The idea of using ORM is building a database agnostic application to a great extent, so I would need to buy a profiler for every database that I will use with my applications? And what if I want to go with SQL compact database? There are some partial solutions even for compact version, but why do we need a 3rd party tools for such a simple feature? Its like buying a 3rd party tool to be able to read what generated an exception. :-/
    Saturday, October 15, 2011 8:17 PM
  • Hi,

    The T-SQL for ObjectContext:

    using (var context = new TestDBEntities())
    {
        var query = from p in context.Parents
                    where p.Name == "Alan"
                    select p;
    
        ObjectQuery<Parent> parents = query as ObjectQuery<Parent>;
        if (parents != null)
        {
            string sql = parents.ToTraceString();
        }
    }
    

    >> If this can be handled on the global level

    I think there is not a good way to handle on global level, thanks for understanding.

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, October 19, 2011 8:59 AM
  • hi Alan, since I am using DbContext, ToString would be equivalent to prior ToTraceString(). However, my concern is that in almost every larger WinForms application you have many forms that interact with each other. In this scenario it is a necessity to have an overview of when the query is executed. This means that I would need to have hindreds of calls to ToString / ToTraceString throughout application. This polutes the code, and can result in forgotten leftovers in the code. This is the reason why it is normal to request one place / event where we can subscribe to get information about executed queries.

    Is this feature in plan at all and if yes, for what release it is planned?

    Sunday, October 23, 2011 9:15 AM