Linq "Could not translate expression... into SQL and could not treat it as a local expression."

Question

I started out with this question, which I sort of answered there, and now I'm asking the more fundamental question here. I've simplified the query down to this:

var q = from ent in LinqUtils.GetTable<Entity>()
        from tel in ent.Telephones.DefaultIfEmpty()
        select new {
          Name = ent.FormattedName,
          Tel = tel != null ? tel.FormattedNumber : "" // this is what causes the error
        };

tel.FormattedNumber is a property that combines the Number and Extension fields into a neatly formatted string. And here's the error that results:

System.InvalidOperationException: Could not translate expression 'Table(Entity).SelectMany(ent => ent.Telephones.DefaultIfEmpty(), (ent, tel) => new <>f__AnonymousType0`2(Name = ent.FormattedName, Tel = IIF((tel != null), tel.FormattedNumber, "")))' into SQL and could not treat it as a local expression.

If I change the reference above from FormattedNumber to just plain Number, everything works fine.

But I do want the formatted number to display nicely in my list. What do you recommend as the neatest, cleanest way of doing so?

Answer 1

You could use AsEnumerable on the entity, but that would force it to bring back all the columns (even if not used); perhaps instead something like:

var q1 = from ent in LinqUtils.GetTable<Entity>()
         from tel in ent.Telephones.DefaultIfEmpty()
         select new {
           Name = ent.FormattedName,
           Number = (tel == null ? null : ent.Number),
           Extension = (tel == null ? null : ent.Extension)
         };

var q2 = from row in q1.AsEnumerable()
         select new {
             row.Name,
             FormattedNumber = FormatNumber(row.Number, row.Extension)
         };

where FormatNumber is some method that takes the two and merges them, presumably re-used from your other (property) code.

With LINQ-to-SQL, another option is to expose a UDF on the data-context that does the formatting inside the database; a slightly different example:

var qry = from cust in ctx.Customers // and tel
          select new {
              cust.Name,
              FormattedNumber = ctx.FormatNumber(tel.Number, tel.Extension)
          };

(which will do the work at the database; whether or not that is a good idea ;-p)

Answer 2

The clean way is to state the fields you actually want in expression, put them in your mid-tier objects and then use any auxiliary functions to modify them latter.

I'm not sure if you realize that a class representing SQL table for LINQ is a DTO class - it defines the grammar used by LINQ-SQL translator. Injecting a property into a DTO which is not mapped to the SQL table is not even supported - meaning that translator can fire at will. Attributes define the grammar and anything not defined by them doesn't exist for expression translator.

Entities named in the from clause are not objects - they are just symbols used to help spelling out actual table fields that will be fetched. A field not named explicitly in select is a field not fetched - at least that's the goal of the translator, it may have to let a few slip through. For example if that ent.FormattedName is not declared, that's a slip and can explode latter.

So, that FormattedNumber property injected into DTO class doesn't even exist in the grammar. It's not a "calculated field" - that term is strictly for SQL table definitions and if you had one it would be in DTO's grammar. Notice that error said very precisely "local expression" - very limited scope.

You could try to cheat it by a nested lambda expression invoking a static function on the whole "tel" which might triger fetching of the whole record - or throwing another exception.

Other LINQ-s, which are not translators, can have relaxed rules. LINQ-SQL has to be either very strict or very slow and it's already slow enough :-)

Answer 3

@Marc Gravell beat me to the answer, credit also to the various answerers to this question who put me on the right track.

I did it much like Marc's first suggestion, like so:

var q1 = from ent in LinqUtils.GetTable<Entity>()
         from tel in ent.Telephones.DefaultIfEmpty()
         select new { ent, tel };
var q2 = from q in q1.AsEnumerable()
         select new {
           Name = q.ent.FormattedName,
           Tel = q.tel != null ? q.tel.FormattedNumber : ""
         };

And that did it! Thanks, all!