LINQ-to-SQL "Member access not legal on type" exception with unioned and compiled query

Question

I have multiple queries that I'd like to union together, then compile the entire thing. The uncompiled query runs fine, but an "InvalidOperationException: Member access 'Int32 Id' of 'UserQuery+Foo' not legal on type 'System.Linq.IQueryable`1[UserQuery+Foo]." exception is thrown when the same query is compiled and run.

How do I fix this?

void Main()
{
    var db = new MyDataContext( "..." );

    Expression < Func < DataContext, int, IQueryable < Foo > > > queryExpression = (DataContext dc, int unused) =>
        from ab in GetA(dc).Union( GetB(dc) )
        group ab by new { ab.Id, ab.Name } into grp
        select new Foo
        {
            Id = grp.Key.Id,
            Name = grp.Key.Name,
            Total = grp.Count()
        };

    var final = CompiledQuery.Compile ( queryExpression );

    var result1 = queryExpression.Compile () (db, 0);  // calling the original query works fine
    var result2 = final (db, 0);            // calling the compiled query throws an exception
}

public class Foo
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Total { get; set; }
}

IQueryable<Foo> GetA( DataContext db )
{
    return from b in db.GetTable<Bar>()
    where b.IsActive
    select new Foo { Id = b.Id, Name = b.Name };
}

IQueryable<Foo> GetB( DataContext db )
{
    return from b in db.GetTable<Bar>()
    where !b.IsActive
    select new Foo { Id = b.Id, Name = b.Name };
}

EDIT

It looks like the union and grouping are irrelevant. Removing those elements from the query still causes an exception when compiled:

    Expression < Func < DataContext, int, IQueryable < Foo > > > queryExpression = (DataContext dc, int unused) =>
        from a in GetA(dc)
        select new Foo
        {
            Id = a.Id,
            Name = a.Name,
            Total = 42
        };

Replacing the call to GetA(dc) with dc.GetTable<Bar>() and adding the where clause fixes the issue.

So, is connecting separate queries together like this simply not possible for compiled queries?

EDIT #2

James' answer hit the nail on the head. Simplifying the query even further reveals the root problem:

    Expression < Func < DataContext, int, IQueryable < Foo > > > queryExpression = (DataContext dc, int unused) =>
        from a in GetA(dc)
        select a;

This query throws NotSupportedException: Method 'System.Linq.IQueryable``1[UserQuery+Foo] GetA(System.Data.Linq.DataContext)' has no supported translation to SQL.

Pulling the call to GetA out into a separate variable assignment, then using that variable in the query throws a InvalidOperationException: Sequence contains more than one element exception.

Answer 1

My guess is that the linq compiler doesn't understand the methods returning IQueryable.

To compile it, those methods would probably have to return some form of Expression<>.

Answer 2

I had the same issue and what seemed to do the trick for me was separating out an inline static method call that returned IQueryable<> so that I stored this deferred query into a variable and referenced that.

I think this is a bug in Linq to SQL but at least there is a reasonable workaround.