LINQ to SQL Peculiarities

Question

I'm encountering some peculiarities with LINQ to SQL.

With a relatively simple query, I want to select some fields, but have the date fields formatted as strings, which I first achieved like this:

        var list = dataContext.MyLists.Single(x => x.ID == myId);

        var items = from i in list.MyItems
                    select
                        new
                            {
                                i.ID,
                                i.Sector,
                                i.Description,
                                CompleteDate = i.CompleteDate.HasValue ? i.CompleteDate.Value.ToShortDateString() : "",
                                DueDate = i.DueDate.HasValue ? i.DueDate.Value.ToShortDateString() : ""
                            };                               

Later on I tried the following query, which is exactly the same, except I'm querying straight from my dataContext, rather than an element in my first query:

        var items = from i in dataContext.MyLists
                    select
                        new
                            {
                                i.ID,
                                i.Sector,
                                i.Description,
                                CompleteDate = i.CompleteDate.HasValue ? i.CompleteDate.Value.ToShortDateString() : "",
                                DueDate = i.DueDate.HasValue ? i.DueDate.Value.ToShortDateString() : ""
                            };

The first one runs fine, yet the second query yields a:

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

If I remove the lines that Format the date, it works fine. If I remove the .HasValue check it also works fine, until there are null values.

Any ideas?

Anthony

Answer 1

I'd do the SQL part without doing the formatting, then do the formatting on the client side:

var items = list.MyItems.Select(item => new { item.ID, item.Sector, item.Description, 
                                              item.CompleteDate, item.DueDate })
                        .AsEnumerable() // Don't do the next bit in the DB
                        .Select(item => new { item.ID, item.Sector, item.Description,
                                              CompleteDate = FormatDate(CompleteDate),
                                              DueDate = FormatDate(DueDate) });


static string FormatDate(DateTime? date)
{
    return date.HasValue ? date.Value.ToShortDateString() : ""
}

Answer 2

In the first query, you have already got the data back from the database by the time the second line runs (var items = ...). This means that the 2nd line runs at the client, where ToShortDateString can run quite happily.

In the second query, because the select runs directly on an IQueryable collection (dataContext.MyLists), it attempts to translate the select into SQL for processing at the server, where ToShortDateString is not understood - hence the "Could Not Translate.." exception.

To understand this a bit better, you really need to understand the difference between IQueryable and IEnumerable, and at which point a Linq To Sql query stops being IQueryable and becomes IEnumerable. There is plenty of stuff on the web about this.

Hope this helps,

Paul