Can some one help me to write C# mongo Query equivalent to SQL

Question

C# Mongo Error:

Unsupported filter: ({document}{FILEDATE} > {document}{SPLITDATE}).

SQL query:

SELECT * FROM CURRENT AS C WHERE C.FILEDATE > C.SPLITDATE

C# code:

db.GetCollection<CollectionName>().AsQueryable()
.Where(a => a.fileDate > a.splitDate)
.Select(b => b.Name).Distinct().ToList()

Answer 1

I'm not sure if there's a better way to achieve it, but I typically use the Find syntax as it seems more versatile than AsQueryable() - that might just be me, however. Try this:

var collection = db.GetCollection<CollectionObject>(CollectionName);
collection.Find(
    (FilterDefinition<CollectionObject>)"{ $where : \"this.fileDate > this.splitDate\" }"
)
.Project<CollectionObject>(Builders<CollectionObject>.Projection.Include(c => c.Name))
.ToEnumerable()
.Select(c => c.Name)
.Distinct()
.ToList()

Something like this ought to work, though I'm given to believe that $where might not be very efficient. Note that the names within the where are the names on your document.