c# 4.0 - How "where" operates in Linq-to-Entities -

i have products , images table in sql server, along many-to-many productimages link table , have entity framework (vs2012, .net4.5) model generated against structure.

i have wcf service method, getimagelist, lists images associated product. speed, want return couple of columns images table, notably excluding imagebinary , imagethumbnailbinary columns can quite large store high-resolution images in there.

to prove point of query, decided try , images filename of fred.jpg.

to begin with, started off getting product , using navigation properties:

product p = ctx.products.singleordefault(x => x.code == productcode);  if (p != null) {     var images = p.images.where(x => (x.filename == "fred.jpg") && (!imagetypeid.hasvalue || x.imagetypeid == imagetypeid))         .select(x =>             new             {                 x.id,                 x.filename,                 x.maxavailableheight,                 x.maxavailablewidth,                 imagetypename = x.imagetype.name,                 x.filedescription,                 hascmyk = (x.cmykimage != null)             }         ).tolist(); } 

i surprised discover query still slow, despite adding clause , selecting columns wanted. when ran sql profiler, discovered query translated getting every column every image product, , performed filter , select in-memory. here associated sql server trace, took 3541ms execute (notice lack of filtering fred.jpg , bringing every column):

exec sp_executesql n'select  [extent2].[id] [id],  [extent2].[mimetype] [mimetype],  [extent2].[imagetypeid] [imagetypeid],  [extent2].[imagebinary] [imagebinary],  [extent2].[imagethumbnailbinary] [imagethumbnailbinary],  [extent2].[filesizekb] [filesizekb],  [extent2].[filename] [filename],  [extent2].[filedescription] [filedescription],  [extent2].[maxavailableheight] [maxavailableheight],  [extent2].[maxavailablewidth] [maxavailablewidth],  [extent2].[cmykimage] [cmykimage],  [extent2].[sagestockitemid] [sagestockitemid]  [product].[productimages] [extent1] inner join [product].[images] [extent2] on [extent1].[imageid] = [extent2].[id] [extent1].[productid] = @entitykeyvalue1',n'@entitykeyvalue1 int',@entitykeyvalue1=125 

i decided instead of starting @ product , using .images.where, start directly @ context, , add clause product id interested in, follows:

var images = ctx.images.where(x => (x.products.any(y => y.id == 125)) && (x.filename == "fred.jpg") && (!imagetypeid.hasvalue || x.imagetypeid == imagetypeid))     .select(x =>         new         {             x.id,             x.filename,             x.maxavailableheight,             x.maxavailablewidth,             imagetypename = x.imagetype.name,             x.filedescription,             hascmyk = (x.cmykimage != null)         }     ).tolist(); 

to surprise, worked want. sql translated below, includes columns want , filters want:

exec sp_executesql n'select  [extent1].[id] [id],  [extent1].[filename] [filename],  [extent1].[maxavailableheight] [maxavailableheight],  [extent1].[maxavailablewidth] [maxavailablewidth],  [extent2].[name] [name],  [extent1].[filedescription] [filedescription],  case when ([extent1].[cmykimage] not null) cast(1 bit) when ([extent1].[cmykimage] null) cast(0 bit) end [c1]  [product].[images] [extent1] inner join [dbo].[imagetypes] [extent2] on [extent1].[imagetypeid] = [extent2].[id] ( exists (select      1 [c1]     [product].[productimages] [extent3]     ([extent1].[id] = [extent3].[imageid]) , (125 = [extent3].[productid]) )) , (''fred.jpg'' = [extent1].[filename]) , (@p__linq__0 null or [extent1].[imagetypeid] = @p__linq__1)',n'@p__linq__0 int,@p__linq__1 int',@p__linq__0=1,@p__linq__1=1 

this query took 0ms execute according sql server profiler - instant!

so, what's going on here, why - when start @ product , go .images load everything, if start @ entity data context , go .images additional filter product id, works perfectly?

thanks!

the difference in first example first load product. then, far entity framework concerned, do

p.images 

everything happens around call of no interest ef. because ef loads complete entity collections and materializes entities in complete state. won't load images collection filtered where phrase. , won't load images have properties in select phrase.

in second example not single entity loaded ef, because fetch projection , no complete entities (no product , no image). if you're in dbcontext api, can verify checking context.products.local.count.

there way projection entity's collection. following example like:

context.entry(p).collection(x => x.images).query()        .select(x => new                     {                         x.id,                         x.filename,                         ....