0

I'm trying to execute a Find query on a IMongoCollection<CreditEntry>, with a simple comparison of two integer properties. I am using the C# MongoDB.Driver 2.5.1 for this.

The CreditEntry class contains these two integerfields: UsedCredits and TotalCredits and looks like this(simplified):

public class CreditEntry
{
    public CreditEntry(string username, int totalCredits)
    {
        Id = Guid.NewGuid();
        Username = username;
        UsedCredits = 0;
        TotalCredits = totalCredits;
    }

    public Guid Id { get; set; }
    public string Username { get; set; }
    public int UsedCredits { get; internal set; }
    public int TotalCredits { get; internal set; }
    public int Credits => TotalCredits - UsedCredits;
}

The code that should be able to find the user, that throws the System.ArgumentException is(simplified):

string username = "user1";
var user = _collection.Find(c => c.Username == username &&
                                 c.TotalCredits > c.UsedCredits).FirstOrDefault();

The exception looks like this:

{
  "error":{
    "code":"","message":"An error has occurred.","innererror":{
      "message":"Unsupported filter: ({document}{TotalCredits} > {document}{UsedCredits}).","type":"System.ArgumentException"

I have made sure that there is a CreditEntry for user1, where the TotalCredits is more than the UsedCredits, and that the collection exists.

If I execute this Find query, it's working fine, but it lacks the integer comparison condition:

string username = "user1";
var user = _collection.Find(c => c.Username == username).FirstOrDefault();

So, my question is: Why can I not compare two integer properties of CreditEntry in this Find query, and are there possibly other ways to achieve this?

EDIT: Why is a Func accepted as an input for the Find method, where you can compare an integer field i.e. TotalCredits to a specific integer like i.e. 10, while comparing two fields isn't possible?

To illustrate this:

var user = _collection.Find(c => c.TotalCredits > 0 && c.Tenant == tenant).FirstOrDefault();

This query is completely valid, while comparing two fields isn't. Why does the one cause an exception while the other doesn't?

Lennart
  • 752
  • 1
  • 5
  • 18
  • 2
    Why? Bottom line is that unlike SQL from LINQ translations, MongoDB actually uses a separate `aggregate()` method for such "computed expressions", and the LINQ expression needs a `toEnumerable()` ( or similar handling to an "enumerable" ) in order to instruct the driver to actually call `aggregate()` instead of `Find()`. Also even though `$expr` can be used with modern MongoDB releases, this has no LINQ expression equivalent. Use the forms as otherwise demonstrated, or with direct `BsonDocument` expressions for other syntax. – Neil Lunn May 17 '19 at 10:14
  • 1
    Also, your edit does not make this a new question. It's well answered by the existing responses already marked. I suggest reading them and learning how to solve your problem. – Neil Lunn May 17 '19 at 10:15
  • I'd say this query should be very simple to translate, if an integer can be compared to a specific number, it should be able to be compared to another property of the same document. I still don't understand why this can't be translated, nor do I understand why the "ToEnumerable()" method makes it possible to suddenly be able to compare these two fields. – Lennart May 17 '19 at 10:23
  • 2
    It's "simple", but only from the "aggregation framework" and that's how MongoDB does things, as essentially a separate method. The mapping to that requires that you don't actually use LINQ expressions in the `Find()` in particular for this type of comparison. It won't translate as JavaScript expressions for `$where` either, without of course the `BsonDocument` approach. You can see how the different form of expressions translate to aggregation pipelines via examples on [Aggregate $lookup with C#](https://stackoverflow.com/a/50540059/2313887) – Neil Lunn May 17 '19 at 11:34

0 Answers0