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
– Bryant, Commented Oct 2, 2008 at 16:06

Jon Skeet · Accepted Answer · 2008-10-02 16:14:57Z

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 2, 2008 at 16:14

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lucas Over a year ago

Ooooh I liked that .AsEnumerable() split in there. Nice.

Dave Jellison Over a year ago

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.

Jon Skeet Over a year ago

@DaveJellison: So long as you realize that any filtering etc done after AsEnumerable will be done in-process (rather than in the database)...

Dave Jellison Over a year ago

@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.

Paul Nearney · Accepted Answer · 2008-10-02 16:13:18Z

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

Carlton Jenke · Accepted Answer · 2008-10-02 16:15:36Z

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.

Chris Ammerman · Accepted Answer · 2008-10-02 17:16:54Z

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

rotary_engine · Accepted Answer · 2010-02-05 14:56:58Z

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

Collectives™ on Stack Overflow

LINQ to SQL Peculiarities

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Linked

Related