none
Need a workaround for modifying the generated SQL RRS feed

  • Question

  • Our infrastructure relies on a base utility class we use for executing SQL (the traditional ADO.NET way) that prepends a comment to the beginning of every SQL statement that gets executed.  Looks something like this:
    --<trace db="{db name}" user="{user ID}" path="{URL of calling page}" method="{name of method executing the SQL}" seq="" class="{class name}" time="2/2/2010 10:19 AM" />

    We then have tools that parse this info so that when our servers start having performance issues we know exactly where to look.  Were are a SAS/ASP provider so we host thousands of databases that our web apps run on.  Using Linq to SQL I have not found a way of getting this trace information in.  Anyone have any good ideas on how this could be accomplished?  I looked at going with EF instead, but we have a bunch of table valued UDFs...

    Thanks!

    Tuesday, February 2, 2010 4:25 PM

Answers

  • There's no easy way to do that in a nice manner.

    1) One way would be:

    IQueryable<Employee> emps = from emp in dc2.Employees where emp.NationalIDNumber == "abc" select emp;
    
    System.Data.Common.DbCommand command = dc2.GetCommand(emps);
    
    command.CommandText = "--<trace db=\"{db name}\" user=\"{user ID}\" path=\"{URL of calling page}\" method=\"{name of method executing the SQL}\" seq=\"\" class=\"{class name}\" \r\n"
     + command.CommandText;  // <-- you _may_ have to create a new instance of the SqlCommand rather than modify the existing...
    
    IEnumerable<Employee> emps2 = dc2.Translate<Employee>(command.ExecuteReader());
    

    2) ...another would be to override the datacontext constructor and do something session based (e.g. open the connection, create a temp table, insert the trace data there, ...).

    3) A third way (not-out-of-the-box) would be to use a third party profiler designed for Linq-to-SQL. My company makes one such profiler: http://huagati.com/L2SProfiler/ . You can attach custom data to profiling logs using the AddCustomProfilerData method: http://www.huagati.com/l2sprofiler/runtimehelp/html/M_Huagati_LinqToSQL_Profiler_QueryProfiler_AddCustomProfilerData.htm ...although it already collects plenty of data by itself to identify the source of a query. You can also set up runtime filters so it logs _only_ queries that are heavy in one way or another (timings, I/O, scans, etc...).

    4) You can use reflection to replace/wrap internal L2S objects, intercept and edit the sql commands. Error prone, good chance that it can break if some future version of the framework changes name on internal/private members, and in a web scenario that could be an issue if the web app don't have reflection permissions.
    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Wednesday, February 3, 2010 2:13 AM
    Answerer

All replies

  • There's no easy way to do that in a nice manner.

    1) One way would be:

    IQueryable<Employee> emps = from emp in dc2.Employees where emp.NationalIDNumber == "abc" select emp;
    
    System.Data.Common.DbCommand command = dc2.GetCommand(emps);
    
    command.CommandText = "--<trace db=\"{db name}\" user=\"{user ID}\" path=\"{URL of calling page}\" method=\"{name of method executing the SQL}\" seq=\"\" class=\"{class name}\" \r\n"
     + command.CommandText;  // <-- you _may_ have to create a new instance of the SqlCommand rather than modify the existing...
    
    IEnumerable<Employee> emps2 = dc2.Translate<Employee>(command.ExecuteReader());
    

    2) ...another would be to override the datacontext constructor and do something session based (e.g. open the connection, create a temp table, insert the trace data there, ...).

    3) A third way (not-out-of-the-box) would be to use a third party profiler designed for Linq-to-SQL. My company makes one such profiler: http://huagati.com/L2SProfiler/ . You can attach custom data to profiling logs using the AddCustomProfilerData method: http://www.huagati.com/l2sprofiler/runtimehelp/html/M_Huagati_LinqToSQL_Profiler_QueryProfiler_AddCustomProfilerData.htm ...although it already collects plenty of data by itself to identify the source of a query. You can also set up runtime filters so it logs _only_ queries that are heavy in one way or another (timings, I/O, scans, etc...).

    4) You can use reflection to replace/wrap internal L2S objects, intercept and edit the sql commands. Error prone, good chance that it can break if some future version of the framework changes name on internal/private members, and in a web scenario that could be an issue if the web app don't have reflection permissions.
    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Wednesday, February 3, 2010 2:13 AM
    Answerer
  • Thanks Ahmad - those are some interesting ideas.  I already have a base datacontext that handles how we use connection pooling (all connections have to be opened using the same exact connection string, then we switch the DB it executes against based on an enumerated list of possible databases).  The problem is the existing tools that our IT/DBA group uses to monitor the applications' health.  It looks at the text data of a profile and extracts out the <trace> node for each SQL call.  I need that node to be present on every SQL call made by L2S and it needs to be done without any implementation from specific developers.  Since were are an Application Service Provider we have a LOT of developers writing code against our systems and I can't rely on everyone of them to implement something like GetCommand.  It is also my understanding that you lose Object Tracking when you do it that way.

    Ultimately what would be nice is if there were a way to specify a literal string as a property on a table based object that could serve this purpose.  Does anyone know if using the Expression attribute would work?


    [Column(DbType="varchar(200)",Expression="'<trace/>'")]
    public string TraceInfo
    {
        get;set;
    }
    Could I then have something on the constructor of the object that alters it's Column.Expression attribute value to what I need it to be?  I'll have to try this and see what I come up with.


    Well that was a bad idea. Expressions are only used when calling CreateDatabase.
    Thursday, February 4, 2010 3:34 PM