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

Seems you have two totally different queries. One is selecting from the MyItems in a MyList object. The second is selecting from the MyList objects (not MyItems). Are the data types the same for both? It is pretty hard to debug this without knowing what the table structures are. — Bryant, Oct 02 '08 at 16:06

score 14 · Accepted Answer · answered Oct 02 '08 at 16:14

14

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() : ""
}

answered Oct 02 '08 at 16:14

Jon Skeet

1,421,763
867
9,128
9,194

Ooooh I liked that .AsEnumerable() split in there. Nice. – Lucas Oct 07 '08 at 22:54
You are a king among men, I had no idea AsEnumerable would get me out of this constant pain while essentially respecting an IQuerable in my case...genius. – Dave Jellison Aug 07 '13 at 14:56
@DaveJellison: So long as you realize that any filtering etc done *after* `AsEnumerable` will be done in-process (rather than in the database)... – Jon Skeet Aug 08 '13 at 07:38
@Jon, right, which in my case is fine for this approach (small data set) where I eventually build up the cache to have everything in memory if the user does do a search, or sort, etc. While this may seem silly if 90% of the time your user doesn't search or sort then paging the data is still a valuable tool. Aka, not just doing a .ToList() and storing the entire table in memory up-front. – Dave Jellison Aug 08 '13 at 18:54

score 8 · Answer 2 · answered Oct 02 '08 at 16:13

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

score 6 · Answer 3 · answered Oct 02 '08 at 16:15

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.

score 2 · Answer 4 · answered Oct 02 '08 at 17:16

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() : ""
                        };

score 1 · Answer 5 · answered Feb 05 '10 at 14:56

1

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

answered Feb 05 '10 at 14:56

rotary_engine

559
2
6
17

LINQ to SQL Peculiarities

5 Answers5

Linked