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

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

Answer 2

Maybe there was a copy and paste error or just a typo in your sample. But if not, this might be the problem...

In the second query you are querying a collection of lists, whereas in the first query you were querying the items within a list. But you haven't adjusted the query to account for this difference.

What you need might be this. Note the commented lines which did not appear in your second sample.

    var items = from aList in dataContext.MyLists
                from i in aList.MyItems  // Access the items in a list
                where aList.ID == myId  // Use only the single desired list
                select
                    new
                        {
                            i.ID,
                            i.Sector,
                            i.Description,
                            CompleteDate = i.CompleteDate.HasValue ? i.CompleteDate.Value.ToShortDateString() : "",
                            DueDate = i.DueDate.HasValue ? i.DueDate.Value.ToShortDateString() : ""
                        };

Answer 3

ToShortDateString() is not supported by Linq to SQL http://msdn.microsoft.com/en-us/library/bb882657.aspx

Answer 4

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 5

Just like the error message tells you, the difference is due to what can be done locally verses remotely while connecting to SQL.

The Linq code has to be converted by Linq to SQL into a SQL command for the remote data pulls - anything that has to be done locally cannot be included.

Once you pulled it into a local object (in the first example), it is not using Linq to SQL anymore, just plain Linq. At that point you are free to do local manipulations on it.