LINQ-to-MongoDB - Return list only if values ​​between two columns match

Question

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

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

Here is what I have 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 is 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 for a LINQ query to do this?

Additional Information:

• MongoDB Version: 3.0.4
• C # driver: 2.2.4

• Reset Json document (exported from MongoChef and 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

The loan goes to @Ciro Corvino for the following response:

 var docs = collection.AsEnumerable().Where(c => c.FSTicker.Equals(c.Sedol)); 

Answer 2

Since this explores the problem, when we need to compare two fields from the same document, find the fragment that uses the aggregation structure.

the Datapull class received an additional field for this:

 public bool IsTrue { get; set; } 

A fragment of the C # aggregation structure 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

the problem here is based on the fact that the mongo himself does not have a method for comparing fields on the same documents, which are something natural for peoples coming from the SQL world.

Mongo has a $where clause - this is javascript injection, and we can pass such a fragment to work with each returned document from our dataset, but the linq query is not used for use.

Here is a JIRA ticket: jira

EDIT 2

Please download the example from the repo - maybe something is missing. Below is a screenshot of the working solution.

Git repo here

Any comments are welcome!