Does Enumerable.ToDictionary only retrieve what it needs?

Question

I'm using Enumerable.ToDictionary to create a Dictionary off of a linq call:

return (from term in dataContext.Terms
        where term.Name.StartsWith(text)
        select term).ToDictionary(t => t.TermID, t => t.Name);

Will that call fetch the entirety of each term, or will it only retrieve the TermID and the Name fields from my data provider? In other words, would I be saving myself database traffic if I instead wrote it like this:

return (from term in dataContext.Terms
        where term.Name.StartsWith(text)
        select new { term.TermID, term.Name }).ToDictionary(t => t.TermID, t => t.Name);

Answer 1

No. ToDictionary is an extension method for IEnumerable<T> not IQueryable<T>. It doesn't take an Expression<Func<T, TKey>> but simply a Func<T, TKey> that it'll blindly call for each item. It doesn't care (and doesn't know) about LINQ and the underlying expression trees and stuff like that. It just iterates the sequence and builds up a dictionary. As a consequence, in your first query, all columns are fetched.

Answer 2

The generated SQL will return the entire term, so your second statement will bring down just what you need.

You can set dataContext.Log = Console.Out and look at the different results of the query.

Using my sample LINQPad database, here's an example:

var dc = (TypedDataContext)this;

// 1st approach
var query = Orders.Select(o => o);
dc.GetCommand(query).CommandText.Dump();
query.ToDictionary(o => o.OrderID, o => o.OrderDate).Dump();

// 2nd approach
var query2 = Orders.Select(o => new { o.OrderID, o.OrderDate});
dc.GetCommand(query2).CommandText.Dump();
query2.ToDictionary(o => o.OrderID, o => o.OrderDate).Dump();

The generated SQL is (or just peek at LINQPad's SQL tab):

// 1st approach
SELECT [t0].[OrderID], [t0].[OrderDate], [t0].[ShipCountry]
FROM [Orders] AS [t0]

// 2nd approach
SELECT [t0].[OrderID], [t0].[OrderDate]
FROM [Orders] AS [t0]

Answer 3

Enumerable.ToDictionary works on IEnumerable objects. The first part of your statement "(from ... select term") is an IQueryable object. Queryable is going to look at the expression and build the SQL statement. It will then convert that to an IEnumerable to pass to ToDictionary().

In other words, yes, your second version would be more efficient.