Logging in LINQ to SQL

It is very common that you might want to know how to see the SQL query generated at the back end for a LINQ query.

Assume you have a LINQ as below:



If you want to view the SQL query generated for the above LINQ on a console screen you need to add just one line of code as below:


If you closely examine the log, it is a property in the DataContext class and it is of type TextWriter.


Since it is of type TextWriter you can save it to a text file as well. If you want to save the log then you need to follow the following steps.

Step 1:

Create a class derived from the System.IO.TextWriter class.



Step 2:

Configure the log file in the configuration file:



Step 3:

Log the query as below:



Full source code of the above explanation is given below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO; 
 
namespace Relatedtable
{
    class Program
    {
 
        static DataClasses1DataContext context;
        static void Main(string[] args)
        {      
            context = new DataClasses1DataContext();
            context.Log = Console.Out;
            context.Persons.InsertOnSubmit(
                            new Person
                            {
                                FirstName = "The ",
                                LastName = "Poet",
                                HireDate = DateTime.Now,
                                OfficeAssignment = new OfficeAssignment
                                {
                                    Location = "Jamshedpur"
                                }
                             }
                            );
            context.Log = new LogLINQSQL();
            context.SubmitChanges();            
            context.Log = Console.Out;
            Console.ReadKey(true);
 

var result = from r in context.Persons 
                         where r.FirstName=="Dhananjay"
                         select r ;
            context.Log = Console.Out;          
            foreach (var r in result)
            {
                Console.WriteLine(r.FirstName);
            }           
            Console.ReadKey(true); 

 }

  }
 
    class LogLINQSQL : System.IO.TextWriter
    {
        public override void Write(char[] buffer, int index, int count)
        {
            System.Diagnostics.Debug.Write(new String(buffer, index, count));
        }

        public override void Write(string value)
        {
            System.Diagnostics.Debug.Write(value);
        }
        public override Encoding Encoding
        {
            get { return System.Text.Encoding.UTF8; }
        }
    }
}

I hope this article was useful. Thanks for reading.