Why Linq can't translate expression/treat as local expression and throws an exception?

Question

I asked a question here about an Linq error that results from mixing Linq-To-SQL with C# code. In brief, the compiler gets confused and doesn't realize that you are intending to call a local function on the resultset after it's come back from the database.

The answer I accepted was to use AsEnumerable() on the result set, thus forcing it to return all columns before doing any further calculations.

My problem now is that in a very similar way, I am now trying to invoke a method of an object that is related to my resultset via a foreign key, e.g.:

var q =
  from c in MyCities.AsEnumerable() // note the AsEnumerable, forcing the query to execute before accessing properties of c
  let cy = c.County
  select new { CityName = c.Name, CountyName = cy.Name, CountyFoo = cy.Foo() };

And guess what - this throws that ol' exception again. Why? Clearly because we need to go back to the database again in order to load the related County of c. Only this time, I can't call AsEnumerable(), because c has only one County, not a collection!

Dwat that wascawwy wabbit!

How do I get around this one?

Answer 1

One more level of indirection should fix it, though I don't know how readable this is. Remove CountyFoo = cy.Foo() and CountyName = cy.Name from your result set and replace it with County = cy to give you..

var q =
  from c in MyCities.AsEnumerable() 
  let cy = c.County
  select new { CityName = c.Name, County = cy };

then add

var p = q.AsEnumerable().Select(x => 
    new 
    { 
        CityName = x.CityName, 
        CountyName = x.County.Name, 
        CountyFoo = x.County.Foo() 
    });

You can then enumerate over p. This is quite obfuscated, though. Why do you need all of this in line LINQ query?