entity framework - EF 6.x Code First: Different SQL Generated for Navigation Property - What am I doing wrong? -

i'm veteran web developer plenty of t-sql , schema designing experience, but, new ef (skipping right ef 6!). i'm wondering what's wrong pocos i'm getting different sql generated following 2 queries.

basically, have 2 objects, "parent" , "childthing" 1-to-many navigation property. here's "parent" (natural keys, not generated):

public class parent     <key>     <databasegenerated(databasegeneratedoption.none)> <maxlength(10), minlength(4)>     public property parentcode string      public property description string      ' '''navigation property     public overridable property childthings icollection(of childthing) = new hashset(of childthing)  end class 

here's "childthing"

public class childthing     <key, foreignkey("parent"), column(order:=10), databasegenerated(databasegeneratedoption.none), maxlength(10), minlength(4)>     public property parentparentcode string      <key, column(order:=20), databasegenerated(databasegeneratedoption.none)>     public property dateeffective date      public property price decimal      ' '''navigation property: 'parent' record associated child      public overridable property parent parent end class 

so, if write query using parent's navigation property + where, this:

    dim effectivedate date = datetime.parse("1/1/2015").date      dim parentobj = db.parents().find("7001")      dim filteredprices = x in parentobj.childthings                          x.dateeffective = effectivedate 

i sql filteredprices seems ignore where.. mean, has where, takes account fk defined in poco models:

select      [extent1].[parentparentcode] [parentparentcode],      [extent1].[dateeffective] [dateeffective],      [extent1].[price] [price]     [dbo].[childthings] [extent1]     [extent1].[parentparentcode] = @entitykeyvalue1 

if build query directly against childthings,

    dim filteredprices = x in db.childthings               x.parentparentcode = "7001" andalso x.dateeffective = effectivedate 

then, has both parameters in it..

select      [extent1].[parentparentcode] [parentparentcode],      [extent1].[dateeffective] [dateeffective],      [extent1].[price] [price]     [dbo].[childthings] [extent1]     (n'7001' = [extent1].[parentparentcode]) , ([extent1].[dateeffective] = @p__linq__0) 

---

solution

---

thanks @mattbrooks ending exhausting google search. using msdn link provided able arrive @ following solution (and had turn off lazy loading navigation collection property)

    dim parentobj = db.parents().find("7001")      db.entry(parentobj) _         .collection(function(x) x.childthings) _         .query() _         .where(function(x) x.dateeffective = effectivedate) _         .load() 'extension meth. system.data.entity 

i've been using linq forever, in-memory objects.. never before w/ef. knew debugging 'expanding node process results' or calling .tolist cause collection 'processed' , projecting concepts onto mental model of how ef execute query. i'm still not precisely clear on magic ef, but, getting there.

this standard ef behaviour. when accessing collection properties related entities lazy-loaded database collection before results filtered query. in case actual filtering performed linq-to-objects , not linq-to-entities expecting.

i've found useful resource in past — https://msdn.microsoft.com/en-gb/data/jj574232.aspx. shows options efficiently querying related entities via collection property.