EntitySet vs Table query performance in LINQ2SQL

Question

In a LINQ to SQL class, why are the properties that are created from the foreign keys EntitySet objects, which implement IEnumerable, where as the objects on the DataContext are Table objects which implement IQueryable?

EDIT: To clarify, here is an example that illustrates what I'm trying to understand. This example:

ctx.Matches.Where(x => x.MatchID == 1).Single()
           .MatchPlayers.Max(x => x.Score);

hits the database twice where as:

ctx.MatchPlayers.Where(x => x.MatchID == 1)
                .Max(x => x.Score);

only runs 1 query. Here are the traces:

exec sp_executesql N'SELECT [t0].[MatchID], [t0].[Date]
FROM [dbo].[Matches] AS [t0]
WHERE [t0].[MatchID] = @p0',N'@p0 int',@p0=1
go
exec sp_executesql N'SELECT [t0].[MatchID], [t0].[PlayerID], [t0].[Score]
FROM [dbo].[MatchPlayers] AS [t0]
WHERE [t0].[MatchID] = @p0',N'@p0 int',@p0=1
go

and

exec sp_executesql N'SELECT MAX([t0].[Score]) AS [value]
FROM [dbo].[MatchPlayers] AS [t0]
WHERE [t0].[MatchID] = @p0',N'@p0 int',@p0=1
go

which also shows that, even worse, the max is done at the C# level rather than in the database.

I know that the reason this happens is the difference between IQueryables and IEnumerables, so why doesn't the MatchPlayers object in the first example implement the IQueryable interface to get the same benefits as the latter example.

Answer 1

ctx.Matches.Where(x => x.MatchID == 1).Single()

Single() returns a Match, not an IQueryable(Match).

Just push Single() off to the last step:

ctx.Matches
  .Where(m => m.MatchID == 1)
  .Select(m => m.MatchPlayers.Max(mp => mp.Score))
  .Single();

What this query shows is, that it's ok to use the MatchPlayers property in a query. Which addresses my interpretation of the asker's question - "Why can't I use an EntitySet in a query?", You can.

Answer 2

Tables are effectively a conceptual matter - they really exist on the server, so you need to query to get entries. The foreign key entries are the ones actually fetched by another query, so at that point they're locally available. That's a fairly woolly description, but hopefully it gets over the general concept.