Sohan's Blog

Living the Developer's Life

Logging the LINQ to SQL Generated SQL Queries/Commands

I was looking for logging solutions for the generated SQL in one of my projects where I am using LINQ to SQL. I found that sometimes the the Exceptions and StackTrace information does not say much about what’s really causing the problem with LINQ to SQL queries/commands.

To see the actual SQL that’s generated from LINQ you may write a code like the following-

  1. SampleDataContext context = new SampleDataContext();
  2. //You may wish to use any subclass of System.IO.TextWriter in place of Console.out
  3. context.Log = Console.Out;
  4. Session session = new Session
  5. {
  6. SessionID = Guid.NewGuid().ToString(),
  7. Site = “Google”,
  8. StartTime = DateTime.Now
  9. };
  10. context.Sessions.InsertOnSubmit(session);
  11. context.SubmitChanges();

And when run, a log message like the following will be shown in your console.

INSERT INTO [dbo].[Sessions]([SessionID], [StartTime], [Site], [UserHostAddress]
, [Url], [UserAgent], [UrlReferrer])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)
– @p0: Input VarChar (Size = 36; Prec = 0; Scale = 0) [0522d634-d9e6-43c6-985e-
69c61090b5c4]
– @p1: Input SmallDateTime (Size = 0; Prec = 0; Scale = 0) [4/7/2008 3:28:02 PM
]
– @p2: Input VarChar (Size = 6; Prec = 0; Scale = 0) [Google]
– @p3: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
– @p4: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
– @p5: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
– @p6: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
– Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Well, I know you guys are not just writing Console Applications like me (!) and need something better to be worthy. You may visit the following links for more-

  1. To use Log4Net for capturing the log data, this is a simple yet useful implementation.
  2. As usual, don’t forget to take a look at this MSDN page for more on this.