Returning unread records using Linq To SQL

Question

I'm not sure how to ask this question, so I'll start with an example of what I'm doing.

Here is my table structure...

Documents (Primary key = ID)
ID, Title, LatestApprovedRevID

Revisions (Primary key = ID)
ID, DocumentID, RevisionNum, Body

Document_Reads (Primary key = DocumentID, UserName)
DocumentID, UserName, RevisionID

When a user opens a document, the latest approved revision is opened and a record is inserted into Document_Reads showing the document and revision the user had read. I would like to know how to query using Linq to return the documents that have NOT been read by UserName (the current authenticated user).

To get the list of unread documents, there are three cases where I would like to return the document:

• if a document has no records in Document_Reads.
• if a document has records in Document_Reads, but none of them are for UserName.
• if a document has records in Document_Reads and one for UserName exists, but the Document_Reads.RevisionID does not match Documents.LastApprovedRevID.

I have written a filter function, but I'm having problems writing the query for the 2nd requirement. It will return the document if any Document_Reads are found that do not match the UserName.

<Runtime.CompilerServices.Extension()> _
Public Function FilterLatestUnread(ByVal query As IQueryable(Of Document), ByVal userName As String) As IQueryable(Of Document)

    'Documents with no document_reads
    Dim q As IQueryable(Of Document) = From d In query _
                                       Where d.Document_Reads.Count = 0 _
                                       Select d

    'documents with document_reads but none for userName
    q = q.Union(From d In query _
                From dr In d.Document_Reads _
                Where Not (dr.UserName = userName) _
                Select d)

    'documents with document_reads for userName but RevisionID does not match LastApprovedRevID
    q = q.Union(From d In query _
                From dr In d.Document_Reads _
                Where dr.UserName = userName And _
                Not (dr.RevisionID = d.LastApprovedRevID) _
                Select d)

    'Return the combined query.
    Return q

End Function

Then, I have my repository return all documents that have an approved revision and use the filter like...

return _repository.List().FilterLatestUnread("John Doe").ToList()

Thanks for any help and I apologize if this topic can be found on this site already...I wasn't sure what I needed to search for.

Answer 1

I think I figured it out.

For the second query, I'm using this code:

 q = q.Union(From d In query _
                Where (From dr In d.Document_Reads _
                       Where dr.UserName = userName _
                       Select dr).Count = 0 _
                       Select d)

Thanks bpayne for helping me talk it out!

Answer 2

Update: I had run into another error that had to do with trying to Union queries with a different number of expressions. This is what I had to do to fix it in the end...

return From d In query _
       Where (d.Document_Reads.Count = 0) Or _
       (From dr In d.Document_Reads _
        Where dr.UserName = userName _
        Select dr).Count = 0 Or _
        (From dr In d.Document_Reads _
         Where dr.UserName = userName And _
         Not (dr.RevisionID = d.LastApprovedRevID) _
         Select dr).Count = 1 _
         Select d