LINQ to SQL mystery: Why does query include all fields in some cases but not others?

Question

This LINQ to SQL query

From g In Db.Context.Current.Groups
Select g.GroupID

generates this SQL:

SELECT [t0].[GroupID]
FROM [dbo].[Groups] AS [t0]

But this query

From g In Db.Context.Current.Groups
Select g.GroupID, g.MemberCount

generates this SQL:

SELECT 
  [t0].[GroupID], [t0].[Title], [t0].[Description], ...
   -- 24 more fields - omitted for brevity 
FROM [dbo].[Groups] AS [t0]

g.MemberCount is a property of the Group class that returns an integer.

 Public ReadOnly Property MemberCount() As Integer
     Get
        Return (
            From cgx In KN.Db.Context.Current.ContactsGroupsXtabs
            Where cgx.GroupID = Me.GroupID
            Select cgx.ContactID
        ).Count()
     End Get
 End Property

I very much want to select only the fields I need. How can I persuade LINQ to SQL not to select all columns?

Answer 1

In your first query, LINQ to SQL knows it only needs to return a scalar value GroupID (or a GroupID array). So that's what it does: LINQ to SQL queries only that column in the database and returns only those value(s).

From g In Db.Context.Current.Groups
Select g.GroupID

In your second query, LINQ to SQL knows it needs to return a scalar (same as the first query), plus the value returned by a property/method call on an actual entity instance, a Group object. So, to be able to call MemberCount() there must first be a Group object to call it on, right? So LINQ to SQL must do a full entity fetch, and that's why it's querying all the columns.

From g In Db.Context.Current.Groups
Select g.GroupID, g.MemberCount 'property/method call on an entity

[Just theorizing here...] You might say, But MemberCount() doesn't need a full entity fetch, it doesn't need all that data - all it needs is GroupID. And I would reply, How does LINQ to SQL know that? How can LINQ to SQL know that your method MemberCount() doesn't rely on the data of other db-column-based properties? It can't know that, so it must return the full object. [End theorizing.]

Suggestion: Join ContactsGroupsXtabs in your second query, group on GroupID and do a Count() on the group to get your value. (I can see by your other SO questions and answers that you know how to do all that so I'll skip the example.)