Why can't I project a `TimeSpan` across RIA using `TimeSpan.FromMinutes`?

Question

I have combined RIA Services with Entity Framework using POCO's. It's all worked wonderfully (way better than LINQ to SQL). The problem that I have is with the following segment of code:

[Query]
public IQueryable<MyEntity> GetMyEntities()
{
    return from myEntity in ObjectContext.MyEntities
           where myEntity.Status != "deleted"
           select new MyEntity
           {
               // Other property assignments...

               SuchAndSuchTime = TimeSpan.FromMinutes(project.SuchAndSuchTime ?? 0.0),

               // Other property assignments...
           };
}

This is an version of my code where the names have been changed to protect the innocent. This compiles find but I get following exception when I run it:

Load operation failed for query 'GetMyEntities'. LINQ to Entities does not recognize the method 'System.TimeSpan FromMinutes(Double)' method, and this method cannot be translated into a store expression.

Why can I not do this, and is there a work around?

Answer 1

The issue here is that the CLR TimeSpan class does not have full canonical mapping in LINQ to Entities. This is what your exception is referring to in the clause '...cannot be translated into a store expression...'

For LINQ scenarios, queries against the Entity Framework involve mapping certain CLR methods to methods on the underlying data source through canonical functions. Any method calls in a LINQ to Entities query that are not explicitly mapped to a canonical function will result in a runtime NotSupportedException exception being thrown.

Source: MSDN

TimeSpan is one of those cases.

There are at least two solutions, and these are...

1. Use a valid LINQ to SQL (i.e., canonical methods only) statement to obtain the full set of results from the database; and then use LINQ to Objects to filter the results you get back. The LINQ to Objects filter should contain all of the non-canonical methods so that your filtering delivers the intended subset of data.

2. Evacuate the non-canonical methods to a stored procedure (i.e., translate to straight SQL) and call the stored procedure.

The third solution would be to try and wrap the non-canonical expressions into a private method in your domain service class. This is what I did before getting to the bottom of this elusive exception.

A fourth, and more obscure approach, is given here