"Method cannot be translated into a store expression" weird behavior

Question

I have these two models with one-many relation:

[Table("User")]
public class User
{
    public User()
    {
        Times = new HashSet<Time>();
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public Guid Guid { get; set; }

    public virtual ICollection<Time> Times { get; set; }
}

[Table("Time")]
public class Time
{
    [Key]
    public long TimeId { get; set; }
    public DateTime WorkDay { get; set; }
    public Guid UserGuid { get; set; }
    public virtual User User { get; set; }
}

And method in context class which returns DataTable. First implementation fails after query goes through .ToDataTable() extension (or .ToList() or whatever) with exception:

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

Second one goes perfectly fine. Question is why?

First implementation. It doesn't work

public DataTable GetDtProjectsForUser(User user)
{
    var query = from time in Time
                select new
                {
                    WorkDay = time.WorkDay.ToShortDateString(),
                };
    return query.ToDataTable();
}

Second one. It DOES work

public DataTable GetDtProjectsForUser(User user)
{
    var localUser = User.Find(user.Guid);
    var query = from time in localUser.Times
                select new
                {
                    WorkDay = time.WorkDay.ToShortDateString(),
                };
    return query.ToDataTable();
}

score 2 · Accepted Answer · answered Feb 11 '15 at 06:48

Rohit's answer is more or less correct, but his explanation in comments is wrong.

localUser.Times is (presumably) an ICollection<Time>. Building an ICollection requires enumerating the result set. The query is executed as soon as the collection is referenced. Your query is equivalent to:

var collection = localUser.Times.Select(t => new { WorkDay = t.WorkDay.ToShortDateString() });

As soon as localUser.Times is executed, a query is made to the database and the Times collection is loaded. The subsequent .Select() is a LINQ to Objects query.

score 0 · Answer 2 · answered Feb 11 '15 at 06:36

0

Linq is a deferred execution. In you first code, Time variable is not stored in the memory, while in second code, you have stored the collection in localUser variable.
You can read more about the linq and deferred execution at Charlie Calvert's Community Blog.

answered Feb 11 '15 at 06:36

Rohit Prakash

1,975
1
14
24

Yeah, I know about deferred execution. But in both cases "query" executes when I call ToDataTable extension (at least I think so). – Szer Feb 11 '15 at 06:40
@Szer, yeah. that is what deferred means to. both queries executes at the time when you call ToDataTable method. But linq execution is not like a normal execution. To better understand about the linq, also prefer [How does LINQ works internally](http://stackoverflow.com/questions/671235/how-linq-works-internally) – Rohit Prakash Feb 11 '15 at 06:43

"Method cannot be translated into a store expression" weird behavior

2 Answers2