Fetch Entity Framework objects with partially loaded collections

Question

I have an entity Framework model with the following:

class Farm{
    string owner;
    List<Animal> animals;
    DateTime StartDate;
}

class Animal{
    string Name;
    DateTime DOB;
}

Problem:

I would like to select a collection of farms whose start date is >= 2013/01/01 along with it's animals, but also filtered by DOB >= 2013/06/01.

I've tried the following:

Try1:

//This still shows all animals from each farm, if there is at least one
//animal with the required DOB

var x = context.Farm.Where(y => y.StartDate >= myDate 
                           && y.Animal.Any(z => z.DOB >= otherDate)
                          ).Include("Animal");

Try2:

//I subclassed the Farm class because i cant instantiate the class 
//from Entity Framework directly, and that should be my return type.
class Temp:Farm{}

var x = context.Farm.Where(y => y.StartDate >= myDate).Include("Animal")
        .Select(z => new Temp(){ 
                    owner = z.owner, 
                    animals = new TrackableCollection<Animal>(){ z.animals.Where(y => y.DOB >= newDate).SingleOrDefault() });

//Couple of things here:
//1: I instantiated a new TrackableCollection because thats what the collection
//type of Animal is inside Entity Framework.
//2: This still doesnt work for some reason, if i use this approach, the list 
//of animals in the farm comes with 0 elements.

Try3:

After reading this: Ef-query-with-conditional-include

var x = (from farm in ctx.Farm
        from animal in farm.Animal
        where animal.DOB => newDate
        select new{farm, animal}).AsEnumerable().Select(x=> x.farm).Distinct().ToList();
//I have no idea how this works, but it does...

Anyone care to explain how the above works?

Basically the query is selecting the parent entity and the child entity filtered by the required parameters, and entity framework through "Relationship Fixup" knows that the selected children are associated with the selected parents, so they get added to the parent collection as well. I see it kind of a hacky solution, but it works indeed.

--Andrei D.

There needs to be a relationship between animal and Farm. Do you have one that is not shown in your example? — Botonomous, Sep 25 '13 at 18:23
Yeah, i just used those classes for simplicity, but in my actual model I have a relationship between those classes. The entity model was imported from a SQL Server database. — Andrei Dvoynos, Sep 25 '13 at 19:01

Khan · Accepted Answer · 2013-09-25T19:45:22.500

2

Anyone care to explain how the above works?

Look at the following as two separate queries:

var x = (from farm in ctx.Farm
        from animal in farm.Animal
        where animal.DOB => newDate
        select new{farm, animal}).AsEnumerable().Select(x=> x.farm).Distinct().ToList();

Broken out:

//Give me all farms
from farm in ctx.Farm

//Give me farms with animals with a DOB greater or equal to newDate
from animal in farm.Animal
where animal.DOB => newDate

//Select both so that neither are discluded from the query during execution
select new{farm, animal})

At the point of execution, the query will only contain data from whats above, so the result will contain two of every Farm, including the filtered Animals.

The Distinct filters the duplicates.

edited Sep 25 '13 at 19:45

answered Sep 25 '13 at 18:31

Khan

17,904
5
47
59

Are you sure about the LazyLoadingEnabled part? Because I have it enabled in my model, and yet it returns only a subset of the collection, not the whole collection as you state if it is on. – Andrei Dvoynos Sep 25 '13 at 19:41
I am not certain, but in EF 4, I had to shut it off. I'll go ahead and remove that part of the answer in case it varies. – Khan Sep 25 '13 at 19:44
Im still not clear how does the farm get the filtered animals after I do the Select, in the link I mentioned on the last part of my question they speak of Relationship Fixup, but I cannot seem to grasp the concept – Andrei Dvoynos Sep 26 '13 at 03:43
I created a test project to check the LazyLoadingEnabled part and you were right, if it is enabled, then all items are selected on the child collection. But for some reason I still can't figure out, on my real project LazyLoading is Enabled and yet, it doesnt return all elements, just the filtered ones. – Andrei Dvoynos Oct 02 '13 at 21:31
They are both db first, but the real project uses a custom code generation template, I havent tested the LazyLoading using that template, I'll let you know when I do :P – Andrei Dvoynos Oct 03 '13 at 15:19

score 0 · Answer 2 · answered Sep 25 '13 at 18:39

0

You can try:

var result = context.Farms.Where(y => y.StartDate >= myDate)
                          .Select(z => new Farm { 
                            owner = z.owner, 
                            StartDate = z.StartDate,
                            animals = z.animals.Where(x => x.DOB >= newDate).ToList() 
                }).ToList();

answered Sep 25 '13 at 18:39

Esteban Elverdin

3,552
1
17
21

Problem is that I'm using Entity Framework generated classes and I cant instantiate directly a Farm, thats why in my second try I subclassed the Farm entity and instantiated that, but it still didnt do the trick, I'm guessing because of something related with the TrackableCollection class... – Andrei Dvoynos Sep 25 '13 at 19:03
Ah ok, get it, I hadn't understood that part in your question. Does it work if you return an anonymous type? I guess you can put your solution as the right answer. – Esteban Elverdin Sep 25 '13 at 19:08
It works in my real world case, but it doesnt work on this made up case X-( I'm trying to figure out why.. – Andrei Dvoynos Sep 25 '13 at 22:26
Also, I don't return an anonymous type, I return the Entity Framework type, the anonymous type is only used as a bridge. – Andrei Dvoynos Oct 02 '13 at 21:32

score 0 · Answer 3 · answered Sep 25 '13 at 18:52

I usually flip over to the server at this point and create a function that I can call from EF. Just makes it easier alot of the time for instances like these.

(Sorry, I'm typing this without testing the sql. Take it for Psudocode)

Create Procedure GetFarmAnimals @StartDate as [Datetime], @DOB as [Date] As Select * Farm left join Animals on (Whatever you actual foreign key combo is) where Farm.Startdate = @StartDate and Animal.DOB = @DOB end

Then just import the function into EF.

Yeah, thats my last choice, but I would prefer doing this in .Net instead of T-SQL, also this would mean doing a lot more changes to my code. — Andrei Dvoynos, Sep 25 '13 at 19:04

score 0 · Answer 4 · answered Sep 26 '13 at 09:41

0

You can try this:

var filteredObjects = db.Farm.Where(x=>x.StartDate >= <startDateVariable> && x.Animal.Where(y=>y.DOB >= <DOBVariable>));

answered Sep 26 '13 at 09:41

John Gyber Tubig Santos

11
2

Fetch Entity Framework objects with partially loaded collections

Problem:

I've tried the following:

Anyone care to explain how the above works?

4 Answers4

Linked