A technique for computed columns in LINQ-based EF queries

Have you seen this message before? 

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

It is one of my favorite LINQ to EF messages! LINQ is pretty smart at translating C# expressions into SQL.  But, there are lots of simple things that can blow its mind.  Here is an example:

                var camps = from c in db.Camps
                            select new {
                                CampId = "camps/"+c.CampId                             
                            };

This error will occur if CampId is a Guid.  It can also occur if you are trying to call a function or perform some sort of complex manipulation.

A easy way to solve this problem is to put the results of the query into a class with computed properties.  It might be tempting to put this logic into a constructor. However, LINQ to EF only supports parameterless constructors so this would be a no-go.

Here is an example:

   public class Camp
    {
        public string CampId { get; set; }
        public Guid CampGuid
        {
            set
            {
                CampId = "camps/" + value;
            }
        }
   }

   var camps = from c in db.Camps
                        select new Camp {
                                CampGuid = c.CampId 
                         };

The nice thing about this approach is the CampGuid property won't be serialized to JSON since it only has a setter.

Alternatively, you could have the getter do the calculation: 

   public class Camp
    {
        public string CampId 
        {
            get
            {
                return "camps/" + CampGuid;
            }
        }

        public Guid CampGuid { get; set; }
   }

This even works with more complex examples.  In this case, I have a model that is being serialized to JSON.  The JavaScript is expecting the dates in Unix format, while EF returns the dates in a C# DateTime class.  The same technique can solve this problem as well:

    public class CalendarEventModel
    {
        private long ToUnixTimespan(DateTime date)
        {
            TimeSpan tspan = date.ToUniversalTime().Subtract(new DateTime(1970, 1, 1, 0, 0, 0));
            return (long)Math.Truncate(tspan.TotalSeconds);
        }

        public DateTime startDT { get; set; }
        public DateTime endDT { get; set; }
        public long start { get { return ToUnixTimespan(startDT); } }
        public long end { get { return ToUnixTimespan(endDT); } }
   } 

   var q = from schedule in db.CamperSchedules
                select new CalendarEventModel()
        {
              startDT = schedule.StartDateTime,
              endDT = schedule.EndDateTime
        };