I ran across an interesting Linq to SQL, uh, feature, the other day. Perhaps someone can give me a logical explanation for the reasoning behind the results. Take the code below as my example which utilizes the AdventureWorks database setup in a Linq to SQL DataContext. This is a clip from my unit test. The resulting customer returned from a call to both CustomerQuery_Test_01() and CustomerQuery_Test_02() is the same. However, the query executed on the SQLServer are different is a major way. The method CustomerQuery_Test_01 us causing the entire Customer table to be materialized, which the call to CustomerQuery_Test_02 is only causing the single customer to be materialized. The resulting SQL Queries are at the bottom of this post. Anyone have a good reason for this? To me, it was highly non-intuitive.

protected virtual Customer GetByPrimaryKey(Func<Customer, bool> keySelection)
{
    AdventureWorksDataContext context = new AdventureWorksDataContext();
    return (from r in context.Customers select r).SingleOrDefault(keySelection);
}

[TestMethod]
public void CustomerQuery_Test_01()
{
    Customer customer = GetByPrimaryKey(c => c.CustomerID == 2);
}

[TestMethod]
public void CustomerQuery_Test_02()
{
    AdventureWorksDataContext context = new AdventureWorksDataContext();
    Customer customer = (from r in context.Customers select r).SingleOrDefault(c => c.CustomerID == 2);
}

Query for CustomerQuery_Test_01 (notice the lack of a where clause)

SELECT [t0].[CustomerID], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[CompanyName], [t0].[SalesPerson], [t0].[EmailAddress], [t0].[Phone], [t0].[PasswordHash], [t0].[PasswordSalt], [t0].[rowguid], [t0].[ModifiedDate] FROM [SalesLT].[Customer] AS [t0]

Query for CustomerQuery_Test_02 (notice the where clause)

SELECT [t0].[CustomerID], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[CompanyName], [t0].[SalesPerson], [t0].[EmailAddress], [t0].[Phone], [t0].[PasswordHash], [t0].[PasswordSalt], [t0].[rowguid], [t0].[ModifiedDate] FROM [SalesLT].[Customer] AS [t0] WHERE [t0].[CustomerID] = @p0