CODEWITHSUNDEEP
entity-frameworkef-code-firstlinq-to-entitiespoco
bkwdesign
bkwdesign

Reputation: 2097

EF 6.x Code First: Different SQL Generated for Navigation Property

I'm a veteran web developer with plenty of T-SQL and schema designing experience, but, fairly new to EF (skipping right to EF 6!). I'm wondering what's wrong with my POCOs that I'm getting different SQL generated for the following two queries.

Basically, I have two objects, "Parent" and "ChildThing" with a 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 As String

    Public Property Description As String

    ' '''navigation property
    Public Overridable Property ChildThings As ICollection(Of ChildThing) = New HashSet(Of ChildThing)

End Class

Here's my "ChildThing"

Public Class ChildThing
    <Key, ForeignKey("Parent"), Column(Order:=10), DatabaseGenerated(DatabaseGeneratedOption.None), MaxLength(10), MinLength(4)>
    Public Property ParentParentCode As String

    <Key, Column(Order:=20), DatabaseGenerated(DatabaseGeneratedOption.None)>
    Public Property DateEffective As Date

    Public Property Price As Decimal
 
    ' '''Navigation Property: The 'parent' record associated with child 
    Public Overridable Property Parent As Parent
End Class

So, if I write a query using the Parent's navigation property + WHERE, like this:

    Dim effectiveDate As Date = DateTime.Parse("1/1/2015").Date

    Dim parentObj = db.Parents().Find("7001")

    Dim filteredPrices = From x In parentObj.ChildThings
                         Where x.DateEffective = effectiveDate

I get sql for filteredPrices that seems to ignore the WHERE.. I mean, it has a WHERE, but it only takes into account the FK defined in the POCO models:

SELECT 
    [Extent1].[ParentParentCode] AS [ParentParentCode], 
    [Extent1].[DateEffective] AS [DateEffective], 
    [Extent1].[Price] AS [Price]
    FROM [dbo].[ChildThings] AS [Extent1]
    WHERE [Extent1].[ParentParentCode] = @EntityKeyValue1

If I build a query directly against the ChildThings,

    Dim filteredPrices = From x In db.ChildThings
              Where x.ParentParentCode = "7001" AndAlso x.DateEffective = effectiveDate

then, the WHERE has both parameters in it..

SELECT 
    [Extent1].[ParentParentCode] AS [ParentParentCode], 
    [Extent1].[DateEffective] AS [DateEffective], 
    [Extent1].[Price] AS [Price]
    FROM [dbo].[ChildThings] AS [Extent1]
    WHERE (N'7001' = [Extent1].[ParentParentCode]) AND ([Extent1].[DateEffective] = @p__linq__0)

Solution

Thanks to @MattBrooks for ending my exhausting google search. Using the MSDN link he provided I was able to arrive at the following solution (and I had to turn off lazy loading for this 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. from System.Data.Entity

I've been using LINQ forever, just with in-memory objects.. never before w/EF. I knew from debugging that 'expanding this node will process results' or that calling .ToList would cause a collection to be 'processed' and I was projecting those concepts onto my mental model of how EF would execute a query. I'm still not precisely clear on all the magic that is EF, but, getting there.

Upvotes: 1

Views: 85

Answers (1)

Matt Brooks
Matt Brooks

Reputation: 1604

This is standard EF behaviour. When accessing collection properties ALL related entities are lazy-loaded from the database into the collection before the results are filtered for the query. In this case actual filtering is performed by LINQ-to-Objects and not LINQ-to-Entities as you are expecting.

I've found this to be a useful resource in the past — https://msdn.microsoft.com/en-gb/data/jj574232.aspx. It shows some options for efficiently querying the related entities via the collection property.

Upvotes: 3

Related Questions