LINQ-to-MongoDB - Return list only when values between 2 columns match

Question

I have a MongoDB collection. Here's a grid (excel) view of the source data.

I only want to return a list if the values from "fstick" column matches the values from "sedol" column in the same collection. In the end, I want this:

Here's what I've tried so far:

var list1 = collection.AsQueryable();
var list2 = collection.AsQueryable();
var docs = list1.Where(c => list2.Any(a => a.Sedol == c.FSTicker));

And this:

var docs = collection.AsQueryable()
        .Where(c => c.FSTicker.Contains(c.Sedol));

Each time, I get the following error:

System.ArgumentException: Unsupported filter: {document}{fstick}.Contains({document}{sedol}).
at MongoDB.Driver.Linq.Translators.PredicateTranslator.Translate(Expression node)
at MongoDB.Driver.Linq.Translators.PredicateTranslator.Translate(Expression node, IBsonSerializerRegistry serializerRegistry)
at MongoDB.Driver.Linq.Translators.QueryableTranslator.TranslateWhere(WhereExpression node)
at MongoDB.Driver.Linq.Translators.QueryableTranslator.Translate(Expression node)
at MongoDB.Driver.Linq.Translators.QueryableTranslator.TranslatePipeline(PipelineExpression node)
at MongoDB.Driver.Linq.Translators.QueryableTranslator.Translate(Expression node)
at MongoDB.Driver.Linq.Translators.QueryableTranslator.Translate(Expression node, IBsonSerializerRegistry serializerRegistry)
at MongoDB.Driver.Linq.MongoQueryProviderImpl`1.Translate(Expression expression)
at MongoDB.Driver.Linq.MongoQueryProviderImpl`1.Execute(Expression expression)
at MongoDB.Driver.Linq.MongoQueryableImpl`2.GetEnumerator()

Just in case, here's my class:

[BsonIgnoreExtraElements]
public class Datapull
{
    [BsonElement("fstick")]
    public string FSTicker { get; set; }
    [BsonElement("sedol")]
    public string Sedol { get; set; }
    [BsonElement("exchange")]
    public string Exchange { get; set; }
    [BsonElement("localtick")]
    public string LocalTicker { get; set; }
    [BsonElement("compname")]
    public string Company { get; set; }
}

What should I change to LINQ query to make this work?

Additional Info:

• MongoDB version: 3.0.4
• C# driver: 2.2.4

• Json document dump (exported from MongoChef & modified)

  [{
          "fstick" : "25881xx",
          "exchange" : "OTC",
          "localtick" : "MSFT",
          "sedol" : "25881xx",
          "compname" : "Microsoft Corporation",
          "currency" : "USD",
          "closedate" : "2016-07-11"
      }, {
          "fstick" : "2046xxx",
          "exchange" : "NASDQ",
          "localtick" : "AAPL",
          "sedol" : "2046xxx",
          "compname" : "Apple Inc.",
          "currency" : "USD",
          "closedate" : "2016-07-11"
      }, {
          "fstick" : "BCBHZxx",
          "exchange" : "NASDQ",
          "localtick" : "BBRY",
          "sedol" : "BCBHZxx",
          "compname" : "BlackBerry Limited",
          "currency" : "USD",
          "closedate" : "2016-07-11"
      }, {
          "fstick" : "BB-CA",
          "exchange" : "TSE",
          "localtick" : "BB",
          "sedol" : "BCBHZ3x",
          "compname" : "BlackBerry Limited",
          "currency" : "CAD",
          "closedate" : "2016-07-11"
      }
  ]
  

Answer 1

As this explores a problem when we need compare two fields from same document, please find snippet which is using Aggregation framework.

the Datapull class got extra field for that:

public bool IsTrue { get; set; }

C# Aggregation Framework snippet below

        var data = collection.Aggregate();
        var a1 =
            data.Project(
                x =>
                    new 
                    {
                        FSTicker = x.FSTicker,
                        Sedol = x.Sedol,
                        Company = x.Company,
                        Exchange = x.Exchange,
                        LocalTicker = x.LocalTicker,
                        IsTrue = (x.Sedol == x.FSTicker)
                    });
        var a2 = a1.Match(x => x.IsTrue);

        var result = a2.ToList();

EDIT

problem here is based on fact, that mongo itself doesn't have method to compare fields on same documents, which for peoples coming from SQL world it is something natural.

Mongo have a $where clause - it is a javascript injection and we can pass such a snippet to operate on each returned document from our dataSet, but linq query is not translated to use this.

There is a JIRA ticket here: jira

EDIT 2

Please download example from repo - maybe something is missing there Below screen shot of working solution

Git repo here

Any comments welcome!