Filtering a graph of entity framework objects

Question

I'm trying to filter down the results returned by EF into only those relevant - in the example below to those in a year (formattedYear) and an ordertype (filtOrder)

I have a simple set of objects

PEOPLE 1-M ORDERS 1-M ORDERLINES

with these relationships already defined in the Model.edmx

in SQL I would do something like...

select * from PEOPLE inner join ORDERS on ORDERS.PEOPLE_RECNO=PEOPLE.RECORD_NUMBER inner join ORDERLINE on ORDERLINE.ORDER_RECNO=ORDERS.RECORD_NUMBER where ORDERLINE.SERVICE_YEAR=@formattedYear and ORDERS.ORDER_KEY=@filtOrder

I've tried a couple of approaches...

        var y = _entities.PEOPLE.Include("ORDERS").Where("it.ORDERS.ORDER_KEY=" + filtOrder.ToString()).Include("ORDERLINEs").Where("it.ORDERS.ORDERLINEs.SERVICE_YEAR='" + formattedYear + "'");

        var x = (from hp in _entities.PEOPLE 
                 join ho in _entities.ORDERS on hp.RECORD_NUMBER equals ho.PEOPLE_RECNO
                 join ol in _entities.ORDERLINEs on ho.RECORD_NUMBER equals ol.ORDERS_RECNO
                 where (formattedYear == ol.SERVICE_YEAR) && (ho.ORDER_KEY==filtOrder)
                 select hp
                );

y fails with ORDER_KEY is not a member of transient.collection... and x returns the right PEOPLE but they have all of their orders attached - not just those I am after.

I guess I'm missing something simple ?

Answer 1

Imagine you have a person with 100 orders. Now you filter those orders down to 10. Finally you select the person who has those orders. Guess what? The person still has 100 orders!

What you're asking for is not the entity, because you don't want the whole entity. What you seem to want is a subset of the data from the entity. So project that:

var x = from hp in _entities.PEOPLE
        let ho = hp.ORDERS.Where(o => o.ORDER_KEY == filtOrder
                                      && o.ORDERLINES.Any(ol => ol.SERVICE_YEAR == formattedYear))
        where ho.Any()
        select new 
        {
            Id = hp.ID,
            Name = hp.Name, // etc.
            Orders = from o in ho
                     select new { // whatever 
        };