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.