omit nullable types in linq-sql query

Question

I have some code like this trying to get some data from a Documents table based on some filters passed in (ContentRef and TypeRef)...

 public IQueryable<Document> GetFilteredDocuments(bool archived, int? ContentRef, int? TypeRef)
            {
                return from document in db.Documents
                       where document.IsArchived == archived
                       && (document.ContentRef == ContentRef)
                       && (document.TypeRef == TypeRef )
                       select document;
            }

if either ContentRef or TypeRef are null then i dont want it to do a check for if its null i just want it ignored.

eg if both are null my method should return the equiavalent of

 return from document in db.Documents
                       where document.IsArchived == archived
                       select document;

how can i do this?

Answer 1

Try this:

public IQueryable<Document> GetFilteredDocuments(bool archived, int? ContentRef, int? TypeRef)
{
    return from document in db.Documents
           where document.IsArchived == archived
           && (ContentRef == null || document.ContentRef == ContentRef)
           && (TypeRef == null || document.TypeRef == TypeRef )
           select document;
}

When ContentRef is null, the document.ContentRef == ContentRef part will not be evaluated.

Answer 2

With deferred execution, you can construct your query like this because the execution only occurs when GetEnumerator is called.

public IQueryable<Document> GetFilteredDocuments(bool archived, int? ContentRef, int? TypeRef)
{
    IQueriable<Document> docs = db.Documents.Where(d => d.IsArchived == archived);
    if (ContentRef != null)
       docs = docs.Where(d => d.ContentRef == ContentRef);
    if (TypeRef  != null)
       docs = docs.Where(d => d.TypeRef == TypeRef);
    return docs;
}