Linq to entities - searching in EntityCollection navigation properties.

Question

We have classes

public Invoice: EntityObject
{
    public EntityCollection<InvoicePosition> Positions { get {...}; set{...}; }
    ...
}

public InvoicePosition: EntityObject
{
    public string GroupName { get {...}; set{...}; }
}

We are given IQueryable<Invoice>, we are not given IQueryable<InvoicePosition>. How should I find invoices that have positions, where GroupName is 'Fuel'?

IQueryable<Invoice> invoices = InvoiceRepository.List();
IQueryable<Invoice> invoicesThatHaveFuelPositions = 
    from i in invoices
    where ?
    select i

EntityFramework should be able to translate it to proper sql query.

EDIT

As Mark Seemann wrote, I can use:

IQueryable<Invoice> invoices = InvoiceRepository.List().Include("Positions").Include("OtherInclude");
IQueryable<Invoice> invoicesThatHaveFuelPositions = 
    from i in invoices
    from p in i.Positions
    where p.GroupName = 'Fuel'
    select i;

There is a problem. When I use this filtering, I lose "OtherInclude". I think that this is not proper way of filtering when using EF. I'll have to change it to:

IQueryable<Invoice> invoices = InvoiceRepository.List().Include("Positions").Include("OtherInclude");
IQueryable<Invoice> invoicesThatHaveFuelPositions = invoices.Where(???);

But what should I write in Where?

EDIT

Changed Include("Position") to Include("Positions").

EDIT

Alex James gave link to the tip (http://blogs.msdn.com/alexj/archive/2009/06/02/tip-22-how-to-make-include-really-include.aspx), which suggests:

IQueryable<Invoice> invoicesThatHaveFuelPositions = 
    from i in invoices
    where i.Positions.Any(p => p.GroupName == 'Fuel')
    select i;

It seems to work and doesn't influence EF includes.

Answer 1

Something like this ought to work:

var q = from i in invoices
        from p in i.Positions
        where p.GroupName == "Fuel"
        select i;

However, that uses the navigation property Positions, which by default isn't loaded (the Entity Framework uses explicit loading). It will, however, work, if the invoices variable was created like this:

var invoices = from i in myObjectContext.Invoices.Include("Positions")
               select i;

Answer 2

Building on Marks answer. If you do this:

var q = from i in invoices.Include("something")
        from p in i.Positions
        where p.GroupName == "Fuel"
        select i;

The include is lost (see this tip) because the EF loses all includes if the shape of the query changes, for example if you do implicit joins like in a SelectMany query, aka from from.

The workaround is to write your query, and then at the end apply the Include.

Something like this:

var q = ((from i in invoices
        from p in i.Positions
        where p.GroupName == "Fuel"
        select i) as ObjectQuery<Invoice>).Include("something");

If you do this the Entity Framework actually does the include.

Hope this helps

Alex