Linq “Could not translate expression… into SQL and could not treat it as a local expression.”

by

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)

More Related Contents:

  1. Returning IEnumerable vs. IQueryable
  2. Difference Between Select and SelectMany
  3. Return anonymous type results?
  4. LINQ – Left Join, Group By, and Count
  5. When should I dispose of a data context
  6. How to do a subquery in LINQ?
  7. LINQ to SQL and a running total on ordered results
  8. Understanding .AsEnumerable() in LINQ to SQL
  9. How to return anonymous type from c# method that uses LINQ to SQL [duplicate]
  10. LINQ: combining join and group by
  11. Convert Linq Query Result to Dictionary
  12. Am I misunderstanding LINQ to SQL .AsEnumerable()?
  13. LINQ Where Ignore Accentuation and Case
  14. Operation could destabilize the runtime?
  15. Get table-data from table-name in LINQ DataContext
  16. DISTINCT() and ORDERBY issue
  17. Linq query with nullable sum
  18. LINQ to SQL using GROUP BY and COUNT(DISTINCT)
  19. LINQ Expression to return Property value?
  20. Max or Default?
  21. How to make LINQ execute a (SQL) LIKE range search
  22. Will using LINQ to SQL help prevent SQL injection
  23. How do I do a left outer join with Dynamic Linq?
  24. Last and LastOrDefault not supported
  25. Accessing System Databases/Tables using LINQ to SQL?
  26. LINQ to SQL Where Clause Optional Criteria
  27. Linq where column == (null reference) not the same as column == null
  28. Is Injection Possible through Dynamic LINQ?
  29. If Else in LINQ
  30. C# Linq to SQL: How to express “CONVERT([…] AS INT)”?

Leave a Comment