How to tell if a LINQ to SQL query is translated to a SQL statement or not?

Question

We use LINQ to SQL extensively, and one of the biggest performance pitfalls we've run into is situations where a query can't be converted to SQL, and so an entire database table gets loaded into memory and the query performed by .NET. For example, this query

Dim Foo = (From c in Db.Contacts Select c Where c.ContactID=MyContactID)

translates to a simple SQL query, whereas

Dim Foo = (From c in Db.Contacts Select c Where c.ContactID=SafeInt(MyContactID))

doesn't because SafeInt is our own function and doesn't have a SQL equivalent; so the whole contacts table gets loaded into memory and searched by LINQ to objects, which is vastly slower.

With a little experience, we've gotten better at avoiding this kind of situation. But it's not always obvious, and this is the sort of problem that's often not revealed until you're working with a lot of data and things start to slow down.

Is there a straightforward way to see - whether in Visual Studio or some other way - how LINQ queries will be handled, other than trial and error?

Answer 1

Have you tried LinqPad? That is a fantastic Linq prototyping tool. I often use it to prototype my Linq queries, before pasting them into my code. The tool is free.

Are you also aware that you can convert an IQueryable (which gets sent to SQL Server) to an IEnumerable, in order to have the query processed by the .NET Framework, instead of SQL Server? I'll sometimes to do this if I need to call one of my own methods in a Linq query. I'll break the query up into two parts. The first part I'll query as an IEnumerable, and the other part as an IQueryable, and then join the two together.

Answer 2

L2SProf (LINQ to SQL Profiler) is another option that could help you track down the inefficient queries. It has a series of alerts such as Excessive Number of Rows Returned and Unbounded Result Set, and so on. I've found it to be helpful for troubleshooting problems like you're seeing.

Answer 3

Have you tried something like this:

Sub Main
    Dim c = From a In Context.Addresses 
            Where a.City = "London" 
            Select a

    Console.Write(Context.GetCommand(c).CommandText)
End Sub

This should reveal the SQL query that will get used.