[LINQ] EntitySet vs Table

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

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

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.

Answer 2

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 3

This was addressed on the MSDN forums. The gist of the reasoning is that it's very difficult to track added and removed objects while making queries against the database. Instead, the EntitySet is something of a local copy of the related objects that you can manipulate. Unfortunately, as you noticed, this has the side effect of devolving expressions into LINQ to Objects calls instead of the better performing LINQ to SQL.