Exposing SQL fired when LINQ executes

Question

Morning all.

Just a quick one for you - where can I find the SQL that is executed when a LINQ statement fires?

I have the following code that works a treat,

 var r = (from p in getproductweightsbuyer.tblWeights
                     where p.MemberId == memberid &&
                              p.LocationId == locationid
                     select p);

            if (buyer != "Not Specified")
                r = r.Where(p => p.UnitUserField1 == buyer);

            if (subcategory != "Not Specified")
                r = r.Where(p => p.UnitUserField2 == subcategory);

I'm just not sure of the SQL that is firing in the conditional where clause.

Answer 1

if you debug your code you can put a breakpoint and analyze the value of r, which will have the actual SQL code in it.

Answer 2

If you have the database context at hand you could try:

context.GetCommand(query).CommandText

Answer 3

As well as the above options, you can also run up SQL profiler and see the actual SQL sent down the wire to the DB.

Answer 4

If you use LINQ to SQL you can set the DataContext.Log property. This will log the SQL when you execute the query:

getproductweightsbuyer.Log = Console.Out;

var r = (from p in getproductweightsbuyer.tblWeights
                    where p.MemberId == memberid &&
                             p.LocationId == locationid
                    select p);

           if (buyer != "Not Specified")
               r = r.Where(p => p.UnitUserField1 == buyer);

           if (subcategory != "Not Specified")
               r = r.Where(p => p.UnitUserField2 == subcategory);

foreach (var row in r)
  ...

Answer 5

When you're using LINQ to SQL, you can hook a TextWriter to the DataContext.Log property:

context.Log = Console.Out;

However, writing to the console isn't that useful when running inside of Visual Studio, so you might try this solution.