How do I improve the performance of this simple LINQ?

Question

I have two tables, one parent "Point" and one child "PointValue", connected by a single foreign key "PointID", making a one-to-many relation in SQL Server 2005.

I have a LINQ query:

        var points = from p in ContextDB.Points
                     //join v in ContextDB.PointValues on p.PointID equals v.PointID
                     where p.InstanceID == instanceId
                     orderby p.PointInTime descending
                     select new
                     {
                         Point = p,
                         Values = p.PointValues.Take(16).ToList()
                     };

As you can see from the commented out join and the "Values" assignment, the "Point" table has a relation to "PointValue" (called "Points" and "PointValues" by LINQ).

When iterating through the "var points" IQueryable (say, when binding it to a GridView, etc.) the initial query is very fast, however iterating through the "Values" property is very slow. SQL Profiler shows me that for each value in the "points" IQueryable another query is executed.

How do I get this to be one query?

Interestingly, the initial query becomes very slow when the join is uncommented.

Answer 1

I think you want to use the DataLoadOptions.LoadWith method, described here:
http://msdn.microsoft.com/en-us/library/system.data.linq.dataloadoptions.loadwith.aspx

In your case you would do something like the following, when creating your DataContext:

DataLoadOptions options = new DataLoadOptions();
ContextDB.LoadOptions = options;
options.LoadWith((Point p) => p.PointValues);

Answer 2

You should make sure that the PointValues table has an index on the PointID column.

See also this SO question: Does Foreign Key improve query performance?