"Cannot call methods on DateTime", and other limitations

Question

Does anyone know of a definitive list of LINQ to SQL query limitations that are not trapped at compile time, along with (where possible) workarounds for the limitations?

The list we have so far is:

• Calling methods such as .Date on DateTime
  • no workaround found
• string.IsNullOrEmpty
  • simple, just use == "" instead
• .Last()
  • we used .OrderByDescending(x => x.WhateverProperty).First()

Answer 1

Basically, that list is huge... it is everything outside of the relatively small set of things that are handled. Unfortunately, the Law Of Leaky Abstractions kicks in, and each provider has different answers...

LINQ-to-Objects will do anything (pretty much), since it is delegates; LINQ-to-SQL and Entiy Framework have different sets of support.

In general, I've had a fair amount of success using the DateTime properties etc - but in reality, you're going to have to ensure that your query expressions are covered by unit tests, so that if you ever change providers (or the provider gets updated) you know it all still works.

I guess one view is to think in terms of TSQL; there is no BOTTOM n, but there is a TOP 1 (re the OrderByDescending); In terms of string.IsNullOrEmpty, you could be quite literal: foo.Bar == null || foo.Bar == ""; and with DateTime.Date you can probably do quite a bit with DATEPART / the various components.

Another option with LINQ-to-SQL is to encapsulate the logic in a UDF - so you could write a UDF that takes a datetime and returns a datetime, and expose that via the dbml onto the data-context. You can then use that in your queries:

where ctx.Date(foo.SomeDate) == DateTime.Today

This approach, however, doesn't necessarily make good use of indexes.

---

Update:

• The supported method translations etc are here.
• The supported query operations etc are here.

For the full gory details, you can look at System.Data.Linq.SqlClient.PostBindDotNetConverter+Visitor in reflector - in particular the Translate... methods; some string functions are handled separately. So not a huge selection - but this is an implementation detail.

Answer 2

I had exactly this issue with DateTimes, and found that currently the following workaround works for me, but I realise that with larger result sets, it could get to be an issue as the processing is now in my app rather than on the database:

BlogPosts post = (from blogs in blogPosts
           where blogs.PostPath == path 
           select blogs)
           .ToList()
           .Where(blogs => blogs.Published.Date == publishedDate.Date)
           .SingleOrDefault();

Note the ".ToList()" in the middle - this casts it to a normal IEnumerable and allows me to use the usual properties I'd expect.

One thing that still confuses me slightly however is the fact that this is legal in EF:

BlogPosts posts = from blogs in blogPosts
           where !blogs.IsDraft
             && blogs.Published.Year == year
             && blogs.Published.Month == month
           orderby blogs.Published descending
           select blogs

So, I can call ".Year" or ".Month" on a DateTime, but not ".Date" - I guess it comes down to types.

Answer 3

LINQ is the language. LINQ-to-SQL compiles your LINQ command down into a SQL query. Thus, it is limited by the normal limitations of the TSQL syntax, or rather the items that can easily be converted into it.

As others have said, the lists of what you cannot do would be enormous. It is a much smaller list of what you can do. A general rule of thumb is to try to determine how the function you want to use would be converted into TSQL. If you have much trouble figuring it out, then don't use that function (or at least test it first).

But there is an easy work around to use LINQ commands that are not in LINQ-to-SQL. Separate the pure LINQ portions of your code from the LINQ-to-SQL portions. In other words, do the LINQ-to-SQL to pull the data in (with any functions you need that are available in LINQ-to_SQL), with the command to put it into an object (ToEnumerable, ToList, or others similar). This executes the query and pulls the data local. Now it is available for the full LINQ syntax.

Answer 4

Marc Gravell's answer is comprehensively right.

I just wanted to add this detail for Date Logic (and string comparisons). Since you are using LinqToSql, you can take advantage of the SqlMethods, to do those things you are used to doing in the database.

Answer 5

I created a Connect issue for String.IsNullOrEmpty():

Feedback: I want to use string.IsNullOrEmpty in LINQ to SQL statements.

Feel free to add your voice or vote to it, or to create other Connect issues for the various other methods that don't work in Linq to SQL. The squeaky wheel gets the grease.