<p>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?</p>

<p>The list we have so far is:</p>

<ul>
<li>Calling methods such as <code>.Date</code> on <code>DateTime</code>

<ul>
<li>no workaround found</li>
</ul></li>
<li><code>string.IsNullOrEmpty</code>

<ul>
<li>simple, just use <code>== ""</code> instead</li>
</ul></li>
<li><code>.Last()</code>

<ul>
<li>we used <code>.OrderByDescending(x =&gt; x.WhateverProperty).First()</code></li>
</ul></li>
</ul>

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