How to create LINQ expression from Entity Framework navigation property?

Question

I have the following bit of code:

Expression<Func<Subscription, Service>> service2= subscription => (from relationship in subscription.ChildRelationships select relationship.SecondService).FirstOrDefault(); 

It creates an expression that I can use later as part of a query with an entity infrastructure. The actual code I use has a where clause, but I omitted it for readability. This works fine, and I can run it in LINQPad, which I use for testing.

If I changed the code to:

 Expression<Func<Subscription, IQueryable<Service>>> service2= subscription => (from relationship in subscription.ChildRelationships select relationship.SecondService); 

It no longer compiles and has the following error:

It is not possible to convert a lambda expression to the delegate type 'System.Func <Farmworks.Data.Subscription, System.Linq.IQueryable <Farmworks.Data.Service →' because some types of return to block are not implied convertible to return of delegate Type

It seems that ChildRelationships, which is a navigation property, does not implement IQueryable. This is actually an EntityCollection type that implements IEnumerable but is not suitable for creating expressions that work with EF.

I think I understand why the second block of code does not work and would like to know how to rewrite it so that it does. What puzzles me is why the first block of code works. It also uses the ChildRelationships navigation property, but does not have a problem, becoming an expression that works with EF.

Can anyone shed some light?

Answer 1

The problem is not that FirstOrDefault somehow makes Expression work; it is, as you say, that ChildRelationships alone does not implement IQueryable. You can get around this in two ways, depending on your needs.

You can use the IEnumerable.AsQueryable method. This is probably best if the services are already loaded out of context.

If services are not loaded, you can use the Load or Include methods, if necessary, to load them at the appropriate time, and then use the solution above.

If you have a reference to your ObjectContext, you can use ObjectQuery, which implements IQueryable:

 Expression<Func<Subscription, MyEntities, IQueryable<Service>>> service2 = (subscription, context) => ( from s in context.Subscriptions // here is the IQueryable where s.Id == subscription.Id from relationship in s.ChildRelationships select relationship.SecondService); 

Answer 2

I think the CompiledQuery class can help you in this case:

 Expression<Func<Subscription, IQueryable<Service>>> service2 = CompiledQuery.Compile( subscription => ( from relationship in subscription.ChildRelationships select relationship.SecondService ) ); 

Answer 3

Do you need this to be an IQueryable <Service>? I think select returns an IEnumerable <Service>. LINQ runs directly on IEnumerables, so you can simply declare service2 as Expression <Func <Subscription, IEnumerable <Service →>.