EntityCollection navigation properties - searching with a LINQ to entities

entity-framework linq linq-to-entities

Question

There are courses.

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

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

When provided,IQueryable<Invoice> we don't getIQueryable<InvoicePosition> . How can I locate invoices with positions where the GroupName is "Fuel"?

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

It should be possible for EntityFramework to convert it into a suitable SQL query.

EDIT

I can utilize what Mark Seemann wrote:

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 an issue. When I use this filtering, "OtherInclude" is lost. This, in my opinion, is not the appropriate approach to filter when using EF. I'll have to modify it to read:

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

What, though, should I put in Where?

EDIT

Include("Position") was changed to Include ("Positions").

EDIT

Alex James provided a link to the tip, which reads:

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

It seems to function and has no impact on EF inclusions.

1
5
11/8/2009 7:00:19 PM

Accepted Answer

extending Mark's response. Upon doing 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 the advice) because the EF drops all includes when a query's structure changes, such as when you do implicit joins as in a SelectMany query, also known as a query from from.

Writing your query first, then adding the Include at the end, is a workaround.

Possibly like this:

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

When you do this, the Entity Framework incorporates itself.

Hope this is useful.

Alex

5
11/8/2009 4:57:22 PM

Popular Answer

Something like this should be effective:

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

But that use the navigation property.Positions which is not by default loaded (the Entity Framework uses explicit loading). However, it will function if theinvoices the following variables were made:

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow