none
Sql statement of Entity framework

    Question

  • Is there a way to get sql query that is generated by entity framework as programmatically. For instance, is it possible to get sql query of DeleteObject method ?
    Monday, December 26, 2011 12:42 PM

Answers

  • Hi sk0601,

    Welcome to MSDN Forum.

    You can use ObjectQuery's ToTraceString. Here's the code

    static string ToTraceString<T>(this IQueryable<T> t)
    
    {
    
       // try to cast to ObjectQuery<T>
    
       ObjectQuery<T> oqt = t as ObjectQuery<T>;
    
       if (oqt != null)
    
          return oqt.ToTraceString();
    
       return "";
    
    }
    
    using (NorthwindEFEntities context = new NorthwindEFEntities())
    
    {
    
       // connection must be open in order for ToTraceString() to work
    
       context.Connection.Open();
    
       var p = from c in context.Customers
    
               where c.City == "London"
    
               select new { Name = c.CompanyName, City = c.City };
    
       Console.WriteLine(p.ToTraceString());
    
    }
    

    Note that it is not recommended to use ToTraceString for anything other than debugging purposes. If you are using SqlClient to connect to the database then T-SQL is also displayed in the Visual Studio Intelli-Trace window.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 28, 2011 2:31 AM
    Moderator
  • Hi sk0601,

    It seems there is not a method to generate the Add or Delete commandtext. But for Add/Update/Delete command, it is easy to understand their T-SQLs based on entity. For example:

    Addobject(Person)//

    public class Person
    {
    public int Id{get;set;}
    public string Name{get;set}
    }
    

    Insert into Person(Id,Name) values(@Id,@Name) @Id=1,@name="alan";

    I'm not very sure about your requirements, you can override the SaveChanges() method to go through the ObjectStateEntries to find the ^Unchanged entitis, if there is any exception, you should catch it.

    But for complex query, you can use ToTraceString method in Allen's post.

    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.


    Tuesday, January 03, 2012 8:43 AM
    Moderator

All replies

  • Hi sk0601,

    Welcome to MSDN Forum.

    You can use ObjectQuery's ToTraceString. Here's the code

    static string ToTraceString<T>(this IQueryable<T> t)
    
    {
    
       // try to cast to ObjectQuery<T>
    
       ObjectQuery<T> oqt = t as ObjectQuery<T>;
    
       if (oqt != null)
    
          return oqt.ToTraceString();
    
       return "";
    
    }
    
    using (NorthwindEFEntities context = new NorthwindEFEntities())
    
    {
    
       // connection must be open in order for ToTraceString() to work
    
       context.Connection.Open();
    
       var p = from c in context.Customers
    
               where c.City == "London"
    
               select new { Name = c.CompanyName, City = c.City };
    
       Console.WriteLine(p.ToTraceString());
    
    }
    

    Note that it is not recommended to use ToTraceString for anything other than debugging purposes. If you are using SqlClient to connect to the database then T-SQL is also displayed in the Visual Studio Intelli-Trace window.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 28, 2011 2:31 AM
    Moderator
  • I know about the ToTraceString method... I need to log sql queries that are generated by ef, in my application. how can i use ToTraceString method for AddObject or DeleteObject functions ?
    Monday, January 02, 2012 8:08 AM
  • Hi sk0601,

    It seems there is not a method to generate the Add or Delete commandtext. But for Add/Update/Delete command, it is easy to understand their T-SQLs based on entity. For example:

    Addobject(Person)//

    public class Person
    {
    public int Id{get;set;}
    public string Name{get;set}
    }
    

    Insert into Person(Id,Name) values(@Id,@Name) @Id=1,@name="alan";

    I'm not very sure about your requirements, you can override the SaveChanges() method to go through the ObjectStateEntries to find the ^Unchanged entitis, if there is any exception, you should catch it.

    But for complex query, you can use ToTraceString method in Allen's post.

    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.


    Tuesday, January 03, 2012 8:43 AM
    Moderator