LINQ Projection in Entity Framework

I posted a couple of questions about filtering in an eager loading query, and I guess the EF does not support filtering inside of the Include statement, so I came up with this.

I want to perform a simple query where get a ChildProdcut by sku number and it PriceTiers that are filtered for IsActive.

Dim ChildProduct = ChildProductRepository.Query.
            Where(Function(x) x.Sku = Sku).
            Select(Function(x) New With {
                       .ChildProduct = x,
                       .PriceTiers = x.PriceTiers.
                       Where(Function(y) y.IsActive).
                       OrderBy(Function(y) y.QuantityStart)
                   }).Select(Function(x) x.ChildProduct).Single

Is there a more efficient way of doing this? I am on the right track at all? It does work.

Another thing I really don't understand is why does this work? Do you just have to load an object graph and the EF will pick up on that and see that these collections belong to the ChildProduct even though they are inside of an anonymous type?

Also, what are the s开发者_如何学Ctandards for formatting a long LINQ expression?

Is there a more efficient way of doing this? I am on the right track at all?

Nope, that's about the way you do this in EF and yes, you're on the right track.

Another thing I really don't understand is why does this work?

This is considered to be a bit of a hack, but it works because EF analyzes the whole expression and generates one query (it would look about the same as if you just used Include, but with the PriceTiers collection filtered). As a result, you get your ChildProducts with the PriceTiers populated (and correctly filtered). Obviously, you don't need the PriceTiers property of your anonymous class (you discard it by just selecting x.ChildProduct), but adding it to the LINQ query tells EF to add the join and the extra where to the generated SQL. As a result, the ChildProduct contains all you need.

If this functionality is critcal, create a stored procedure and link entity framework to it.