Seeing the SQL Generated by LINQ to Entity Queries

Musthaan

Sometimes you really, really, really want to see the SQL that LINQ generates when working with the Entity Framework. For instance, in a comment to a recent tip, a reader mentioned that a LINQ to Entities query generated a SQL statement that joined more than 40 tables. The application’s performance was, to say the least, disappointing. If you’re not getting what you expect from your LINQ queries, being able to see the SQL statement can give you real clues in tracking down the problem, as it did with that column’s reader.

There are a couple of free Visual Studio Add-ins that, among their other features, allow you to see the SQL statement for a LINQ query (The LINQ to Entity Query Visualizer, Entity Visualizer). However, developers have had trouble getting those add-ins up and running in some versions of Visual Studio (especially Visual Studio 2010). Fortunately, if all you want to do is see your SQL statement, you can do it in Visual Studio’s debug mode using a little code in the Immediate Window.

The first step, of course, is have a program with a LINQ query as in this example:

Dim en As New northwndEntities

Dim res = From cust In en.Customers

Select cust
If you cast the variable holding the output from the query (res, in this case) to Objects.ObjectQuery then you get access to the ObjectQuery’s ToTraceString property. That property returns the SQL statement that will be issued by your Entity Framework Model. In Visual Basic, you’d type this in the Immediate window:
CType(res, Objects.ObjectQuery).ToTraceString()
In C#, you’d type this:
((System.Data.Objects.ObjectQuery) res).ToTraceString()
For my sample query, the result looked like this in Visual Basic (the C# version includes escape characters for newline and carriage returns):
SELECT [Extent1].[CustomerID] AS [CustomerID],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[ContactName] AS [ContactName],
[Extent1].[ContactTitle] AS [ContactTitle],
[Extent1].[Address] AS [Address],
[Extent1].[City] AS [City],
[Extent1].[Region] AS [Region],
[Extent1].[PostalCode] AS [PostalCode],
[Extent1].[Country] AS [Country],
[Extent1].[Phone] AS [Phone],
[Extent1].[Fax] AS [Fax],
[Extent1].[Timestamp] AS [Timestamp]
FROM [dbo].[Customers] AS [Extent1]

Advertisements

Sent e-mail from ASP.NET site using GMail’s SMTP server

Musthaan

Here’s a class I’ve used in the past:

namespace MyApp
{
    public class GMailer
    {
        public static string GmailUsername { get; set; }
        public static string GmailPassword { get; set; }
        public static string GmailHost { get; set; }
        public static int GmailPort { get; set; }
        public static bool GmailSSL { get; set; }

        public string ToEmail { get; set; }
        public string Subject { get; set; }
        public string Body { get; set; }
        public bool IsHtml { get; set; }

        static GMailer()
        {
            GmailHost = "smtp.gmail.com";
            GmailPort = 25; // Gmail can use ports 25, 465 & 587; but must be 25 for medium trust environment.
            GmailSSL = true;
        }

        public void Send()
        {
            SmtpClient smtp = new SmtpClient();
            smtp.Host = GmailHost;
            smtp.Port = GmailPort;
            smtp.EnableSsl = GmailSSL;
            smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
            smtp.UseDefaultCredentials = false;
            smtp.Credentials = new NetworkCredential(GmailUsername, GmailPassword);

            using (var message = new MailMessage(GmailUsername, ToEmail))
            {
                message.Subject = Subject;
                message.Body = Body;
                message.IsBodyHtml = IsHtml;
                smtp.Send(message);
            }
        }
    }
}



The class needs to be configured in your Application_Start event:

GMailer.GmailUsername = "you@gmail.com";
GMailer.GmailPassword = "password";

Usage:

GMailer mailer = new GMailer();
mailer.ToEmail = "someone@somewhere.com";
mailer.Subject = "Email Subject Line";
mailer.Body = "This is a test message";
mailer.IsHtml = false;
mailer.Send();