Today I had some problems with Linq-To-SQL. I needed to get the last item from a collection and only return items where the date was older than two weeks from now.
The main items that I want to return from Linq are Revision records. A Revision has a collection of Document_History records that keep track of updates to the revision. The Document_History record has a Date field that is the date and time when the history entry was inserted, or when the revision was modified.
When using the data objects that Linq generates, you are allowed to perform some functions, such as Last and Reverse on the collection of items.
The above code would return the most recent date, or the last time a revision was updated.
But, when you try to use the Last method within a Linq statement, Visual Studio throws an error.
To get around this error, I figured out that you can select the latest date using a sub query and the Max method. Below is an example of the Linq-To-SQL code that worked for me.
Dim revs As List(Of Revision) = From r In dbcontext.Revisions _ Where (From h In r.Document_History _ Select h.Date).Max <= cutoffDate _ Select r