Lazy Load An Entity Framework EntityCollection with criteria

Question

In Entity Framework (specifically EF 3.5, but if it exists in EF 4 it gives me a reason to upgrade) is it possible to lazy load only part of a collection? I may be approaching this wrong too, so I'm open to suggestions. My tables/entities look similar to this:

Person            PersonMeal           Meal
------    1---*   ----------   *---1   -----
ID                ID                   ID
...               PersonID             ...
                  MealID
                  Value
                  ...

I have a list of Person objects that have been retrieved through Entity Framework via a stored procedure. I have a view that only shows one Meal at a time, so I only want the information related to that meal. Currently I have code that looks like this:

Function GetPersons() As List(Of Person)
    Dim personList = context.StoredProcedureCall(param1, param2, param3).ToList()
    personList.ForEach(Function(x) LazyLoadProperties(x))
    Return personList
End Function

' Work around function because VB lambdas don't take Sub's
Function LazyLoadProperties(ByVal person As Person) As Object
    If (Not person.PersonMeal.IsLoaded) Then
        person.PersonMeal.Load()
    End If
    Return Nothing
End Function

The issue is this is loading up the entire collection. Granted it's a small collection so worst case scenario I can load it all up and then remove all but the one I need, but that is far from ideal. Plus I'm not sure if it would be possible without triggering any of the events of modifying the collection since they shouldn't have been in there in the first place.

Answer 1

In this case, instead of using the Load method, you can just query the database for your data:

Function GetPersons() As List(Of Person)
    Dim personList = context.StoredProcedureCall(param1, param2, param3).ToList()

    Dim person As Person
    For Each person in personList
        person.PersonMeals = From pm in context.PersonMeals.Include("Meal")
                             Where pm.Person.Id == person.Id And pm.Meal.Id == Meal_ID
                             Take 1
    Next person

    Return personList
End Function

I assume that person.PersonMeals is a collection, otherwise you can use FirstOrDefault instead of Take.

In this query we basically select all the PersonMeals entities (together with the Meal) that have the person ID as the current person in the loop and the meal ID you want. If your DB does not have corrupt data (multiple rows with the same PersonID-MealID combinations), there will be 0 or 1 results, which will be written into your PersonMeals property.

Answer 2

Yakimych's answer should work, but the code had a few mistakes.

Correct Syntax should be:

Private Function GetPersons() As List(Of Person)
    Dim personList As List(Of Person) = Context.StoredProcedureCall(param1, param2, param3).ToList()
    For Each p In personList
        Dim pId As Integer = p.Id
        p.PersonMeals = (From pm As PersonMeal In context.PersonMeals.Include("Meal")
                         Where (pm.Person.Id = pId And pm.Meal.Id = Meal_ID) Take 1).ToList
    Next
    Return personList
End Function

I hope it helps.

Answer 3

Your question was pretty clear : is it possible to lazily load only part of a collection and the answer is no ! Not in EF1 nor in EF4. Btw, If the last meal is the subject, start querying by this ! Instead of retrieving the person and there meal, retrieve the last meals and the person attached to it.

Answer 4

Maybe there is a different way to think about this.

What it appears that you are trying to do is to reduce the load on that database. You are doing this by only getting the data that you are showing the user at any one time.

Take for example a person that has 10 meals, the user views the first 4. What causes the most load on the database:

• Get all 10 in one request
• Get 4 in 4 seperate requests

It may be that the second one causes the most load.

It may therefore be better to get all the data with a simple load, then cache the data in memory until you need it. For caching data we use Enterprise Library.