How to create a LINQ expression from an 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 the entity framework. The actual code I'm using has a where clause but I've omitted it for readability. This works fine and I'm able to run it in LINQPad which I'm using for testing.

If I change 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:

Cannot convert lambda expression to delegate type 'System.Func<Farmworks.Data.Subscription,System.Linq.IQueryable<Farmworks.Data.Service>>' because some of the return types in the block are not implicitly convertible to the delegate return type

It seems to be because ChildRelationships which is a navigation property doesn't implement IQueryable. It is actually of type EntityCollection which does implement IEnumerable but is no good for creating expressions that work with EF.

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

Can anyone shed any light?

Answer 1

I think CompiledQuery class may help you in this case:

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

Answer 2

The issue is not that FirstOrDefault somehow makes the Expression work; it is, as you say, that ChildRelationships does not, by itself, implement IQueryable. You can work around that in two different ways, depending upon your needs.

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

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

If you have a reference to your ObjectContext, you can use the ObjectQuery, which does implement 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 3

Do you need it to be IQueryable<Service>? I think that select is returning an IEnumerable<Service>. LINQ operates directly on IEnumerables, so you could just declare service2 as Expression<Func<Subscription, IEnumerable<Service>>>.