Um blog sobre nada

Um conjunto de inutilidades que podem vir a ser úteis

Logging Queries on LINQ and EF

Posted by Diego em Outubro 14, 2012



Instrumentation

 

Determine the running state of a system after it’s been deployed. Techniques:

·         Event logging

·         Debug tracing

·         Performance counters

 

 

 

1) Logging Queries

 

1.1) Accessing SQL Generated by LINQ to SQL

 

When working with LINQ to SQL classes, you can easily assign an object that inherits from TextWriter to the Log property of the DataContext object so that you can create a StreamWriter object that fills up a log file, assigns it to the Log property, and starts running queries.

The Log property is used on the DataContext object to capture all SQL statements when they are executed.

 

 

1)  Create the LinqToSqlTraceWriter.cs class:

 

using System;

using System.Diagnostics;

using System.Text;

using System.IO;

 

namespace AxiDataMerge.BLL

{

    public class LinqToSqlTraceWriter : TextWriter

    {

        private BooleanSwitch logSwitch;

        public LinqToSqlTraceWriter(string switchName)

        {

            logSwitch = new BooleanSwitch(switchName, switchName);

        }

        public override Encoding Encoding

        {

            get { throw new NotImplementedException(); }

        }

        public override void Write(char value)

        {

            if (logSwitch.Enabled)

            {

                Trace.Write(value);

            }

        }

        public override void Flush()

        {

            Trace.Flush();

        }

        public override void Close()

        {

            Trace.Close();

        }

    }

}

 

 

2)      Create the dbContext Object:

       MyDBDataContext context = new MyDBDataContext ();      

context.Log = new LinqToSqlTraceWriter("LinqToSqlSwitch");

 

 

3)  Enable on the web.config

 

<configuration>

       <system.diagnostics>

              <switches>

                     <add name="LinqToSqlSwitch" value="1"/>

              </switches>

       </system.diagnostics>

</configuration>

 

 

When you run the code to execute a LINQ to SQL query, you see the SQL statement in your Visual Studio .NET Output window because a default listener is configured that sets the trace output to the Output window

 

 

clip_image001

 

It can also be enhanced to write to text field:

<system.diagnostics>

       <switches>

              <add name="LinqToSqlSwitch" value="1"/>

       </switches>

       <trace autoflush="true">

              <listeners>

<add name="LinqToSql" initializeData="C:\Logs\CsLinqToSql.log" type="System.Diagnostics.TextWriterTraceListener"/>

              </listeners>

       </trace>            

</system.diagnostics>

 

 

1.2) Accessing SQL Generated by The Entity Framework

 

Do this by using the ToTraceString method on the generic ObjectQuery class. Most LINQ to Entities queries return a generic IQueryable object, which can be converted to a generic ObjectQuery class. To implement this, you can create an extension method for IQueryable that performs the conversion and executes the ToTraceString method.

The ToTraceString method retrieves the SQL for LINQ to Entities, and calling this method does not execute the query.

 

1)  Create the ExtensionMethods.cs class:

 

    public static class ExtensionMethods

    {

        public static string ToTraceString<T>(this IQueryable<T> query)

        {

            var objQuery = query as ObjectQuery<T>;

            if (objQuery != null)

                return string.Format("{0}{2}{1}{2}{2}", DateTime.Now, objQuery.ToTraceString(), Environment.NewLine);

            return string.Empty;

        }

    }

 

2)       Use it:

 

            var x = from acc in YourDBContext.YouEntity

                    select acc;

 

            string s = x.ToTraceString();

 

 

This will not execute the code, just get the query.

 

           

 

 

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

 
%d bloggers like this: