EntityFramework is great when it works. You almost don't know it's there, doing its magic in the background while you focus on building your application around your models. However, when it breaks or doesn't work in the way you expect it, you're in for a ride.
This morning I spent a bit of time trying to figure out why I was getting the following error on a seemingly straightforward query:
The specified type member 'Date' is not supported in LINQ to Entities Exception
So, why is this failing? The answer to the question is simple:
"LINQ to Entities cannot translate most .NET Date methods into SQL since there is no equivalent SQL".
My LINQ query was pretty simple and went along these lines:
Running this threw the error message shown above. My next thought was that maybe the Created property (which is of type DateTimeOffset) was not well formed or something, so I decided to use the DbFunctions.TruncateTime() in order to ensure that my query was dealing only with the Date portion of the property. I changed my query to look like this:
However, I still got the same error message when I run the code. Then I realized that the answer was right in front of me. I removed the .Date from the WHERE statement and it the query executed just fine. The working code is below for reference:
Hopefully this can help you as well.