I recently started to dive into LINQ to SQL (and am actually planning to publish the first 24100.net screencast about my journey in the very near future - so stay tuned!).

In one of the projects I'm currently working on I ran into the issue of nested SQL statements and wanted to see how they translate to LINQ. We happen to have three tables

Posts - Containing blog posts with a foreign key into the Authors table
Authors - Containing author names

and

BannedAuthors, which simply contains a list of all Authors that have been banned from posting for some reason.

The query I wanted to design should retrieve all titles from all posts where the respective author is not in the BannedAuthors table. This would have been done by a nested SQL statement in traditional SQL. I ended up writing the following LINQ statement:

var query = from p in db.Posts

            where (

                from a in db.BannedAuthors

                select a.AuthorId

                ).Contains<int>((int)p.AuthorId) == false

            select p;

I was quite surprised about the straight-forwardness of this LINQ query. However, there is one immediate question which you - supposing you've done SQL stuff before - might have, too: Does this nested LINQ query mean, that we're now going to the database twice? First we get the list of banned authors from the inner statement, than we iterate through it in a foreach like approach?

Well, the answer is: No! LINQ to SQL is clever enough to figure out to translate the above statement into the following SQL query:

SELECT [t0].[Id], [t0].[Title], [t0].[AuthorId], [t0].[ViewCount]
FROM [dbo].[Posts] AS [t0]
WHERE NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[BannedAuthors] AS [t1]
    WHERE [t1].[AuthorId] = [t0].[AuthorId]
    ))

And SQL Server Profiler proves there really is just a single DB dip involved:

Quite cool, isn't it?