Here's the original code:
var contentItems = BmsData.Contents.ToList();
and the resulting SQL command:
SELECT [t0].[ContentNavID], [t0].[ParentTocID],
[t0].[PrevContentNavID], [t0].[NextContentNavID],
[t0].[Title], [t0].[ViewerContent], [t0].[CreateDate]
FROM [content].[Content] AS [t0]
Pretty simple, just the whole table as a list of 'Content'. To trim this down, I tried this:
var contentItems = BmsData.Contents
.Select(x => new Content() {
Title = x.Title, ContentNavID = x.ContentNavID
}).ToList();
This compiled. However, when I ran it I got this exception:
Explicit construction of entity type 'CL.eCourse.BMS.Data.DataLayer.Content' in query is not allowed.
Apparently, it's a no-no to new-up a type defined by the generated data context. The reasoning has to do with "polluting the context cache" as well as things like object tracking and identify field updates. While the justification makes sense, it would still be rather helpful to be able to get a subset of the fields based on what I know I'm going to need.
Some searching led to a solution involving a function delegate:
Func
x => new Content() {
ContentNavID = x.ContentNavID, Title = x.Title
};
var contentItems = BmsData.Contents.Select(x => makeContent(x)).ToList();
Again, this compiles and runs, but it doesn't do what we want. Since the expression delegates to a method, the expression can't be parsed to translate it into the query components, finding only the desired fields in the selector. It seems that the compiler just runs the query for the full select of the entity, then calls the delegate for each one. Indeed, a look at the SQL executed by the data context proves this:
SELECT [t0].[ContentNavID], [t0].[ParentTocID],
[t0].[PrevContentNavID], [t0].[NextContentNavID],
[t0].[Title], [t0].[ViewerContent], [t0].[CreateDate]
FROM [content].[Content] AS [t0]
var contentItems = BmsData.Contents
.Select(x => new { x.Title, x.ContentNavID })
.AsEnumerable()
.Select(x => new Content() {
Title = x.Title, ContentNavID = x.ContentNav
});
This yields the following changed SQL query:
SELECT [t0].[Title], [t0].[ContentNavID]
FROM [content].[Content] AS [t0]
And we have the optimization we were looking for!
Thanks to Tommy Etkin for his help with this.