An alternate is the newer expressive form of $lookup with a sub-pipeline statement for MongoDB 3.6 and above.

BsonArray subpipeline = new BsonArray();

subpipeline.Add(
  new BsonDocument("$match",new BsonDocument(
    "$expr", new BsonDocument(
      "$eq", new BsonArray { "$$entity", "$entity" }  
    )
  ))
);

var lookup = new BsonDocument("$lookup",
  new BsonDocument("from", "others")
    .Add("let", new BsonDocument("entity", "$_id"))
    .Add("pipeline", subpipeline)
    .Add("as","others")
);

var query = entities.Aggregate()
  .Match(p => listNames.Contains(p.name))
  .AppendStage<EntityWithOthers>(lookup)
  .Unwind<EntityWithOthers, EntityWithOther>(p => p.others)
  .SortByDescending(p => p.others.name)
  .ToList();

请求发送到服务器:

[ 
  { "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
  { "$lookup" : {
    "from" : "others",
    "let" : { "entity" : "$_id" },
    "pipeline" : [
      { "$match" : { "$expr" : { "$eq" : [ "$$entity", "$entity" ] } } }
    ],
    "as" : "others"
  } },
  { "$unwind" : "$others" },
  { "$sort" : { "others.name" : -1 } }
]

Fluent "Builder" 还不直接支持语法，LINQ 表达式也不支持 $expr 运算符，但是您仍然可以使用 BsonDocument 和 BsonArray 或其他有效表达式进行构造.在这里，我们还键入" $unwind 结果以应用 $sort 使用表达式而不是前面所示的 BsonDocument.

The Fluent "Builder" does not support the syntax directly yet, nor do LINQ Expressions support the $expr operator, however you can still construct using BsonDocument and BsonArray or other valid expressions. Here we also "type" the $unwind result in order to apply a $sort using an expression rather than a BsonDocument as shown earlier.

除了其他用途之外，子管道"的主要任务是减少 $lookup.此外，这里的 $unwind实际上被合并"的目的关于服务器执行的 $lookup 语句，因此这通常比仅获取结果数组的第一个元素更有效.

Aside from other uses, a primary task of a "sub-pipeline" is to reduce the documents returned in the target array of $lookup. Also the $unwind here serves a purpose of actually being "merged" into the $lookup statement on server execution, so this is typically more efficient than just grabbing the first element of the resulting array.

var query = entities.AsQueryable()
    .Where(p => listNames.Contains(p.name))
    .GroupJoin(
      others.AsQueryable(),
      p => p.id,
      o => o.entity,
      (p, o) => new { p.id, p.name, other = o.First() }
    )
    .OrderByDescending(p => p.other.name);

请求发送到服务器:

[ 
  { "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
  { "$lookup" : {
    "from" : "others",
    "localField" : "_id",
    "foreignField" : "entity",
    "as" : "o"
  } },
  { "$project" : {
    "id" : "$_id",
    "name" : "$name",
    "other" : { "$arrayElemAt" : [ "$o", 0 ] },
    "_id" : 0
  } },
  { "$sort" : { "other.name" : -1 } }
]

这几乎是相同的，只是使用了不同的接口并产生了稍微不同的 BSON 语句，实际上只是因为函数语句中的简化命名.这确实带来了另一种可能性，即简单地使用 $unwind 从 SelectMany() 产生:

This is almost identical but just using the different interface and produces a slightly different BSON statement, and really only because of the simplified naming in the functional statements. This does bring up the other possibility of simply using an $unwind as produced from a SelectMany():

var query = entities.AsQueryable()
  .Where(p => listNames.Contains(p.name))
  .GroupJoin(
    others.AsQueryable(),
    p => p.id,
    o => o.entity,
    (p, o) => new { p.id, p.name, other = o }
  )
  .SelectMany(p => p.other, (p, other) => new { p.id, p.name, other })
  .OrderByDescending(p => p.other.name);

请求发送到服务器:

[
  { "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
  { "$lookup" : {
    "from" : "others",
    "localField" : "_id",
    "foreignField" : "entity",
    "as" : "o"
  }},
  { "$project" : {
    "id" : "$_id",
    "name" : "$name",
    "other" : "$o",
    "_id" : 0
  } },
  { "$unwind" : "$other" },
  { "$project" : {
    "id" : "$id",
    "name" : "$name",
    "other" : "$other",
    "_id" : 0
  }},
  { "$sort" : { "other.name" : -1 } }
]

通常放置一个 $unwind 直接跟在 $lookup 其实就是聚合的"优化模式"框架.然而，.NET 驱动程序确实在这种组合中搞砸了，它通过在两者之间强制使用 $project 而不是在 "as" 上使用隐含的命名.如果不是这样，这实际上比 $arrayElemAt 当你知道你有一个"相关的结果时.如果你想要 $unwind "合并"，那么你最好使用流畅的界面，或者稍后演示的不同形式.

Normally placing an $unwind directly following $lookup is actually an "optimized pattern" for the aggregation framework. However the .NET driver does mess this up in this combination by forcing a $project in between rather than using the implied naming on the "as". If not for that, this is actually better than the $arrayElemAt when you know you have "one" related result. If you want the $unwind "coalescence", then you are better off using the fluent interface, or a different form as demonstrated later.

var query = from p in entities.AsQueryable()
            where listNames.Contains(p.name) 
            join o in others.AsQueryable() on p.id equals o.entity into joined
            select new { p.id, p.name, other = joined.First() }
            into p
            orderby p.other.name descending
            select p;

请求发送到服务器:

[
  { "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
  { "$lookup" : {
    "from" : "others",
    "localField" : "_id",
    "foreignField" : "entity",
    "as" : "joined"
  } },
  { "$project" : {
    "id" : "$_id",
    "name" : "$name",
    "other" : { "$arrayElemAt" : [ "$joined", 0 ] },
    "_id" : 0
  } },
  { "$sort" : { "other.name" : -1 } }
]

一切都非常熟悉，实际上只是功能命名.就像使用 $unwind选项:

All pretty familiar and really just down to functional naming. Just as with using the $unwind option:

var query = from p in entities.AsQueryable()
            where listNames.Contains(p.name) 
            join o in others.AsQueryable() on p.id equals o.entity into joined
            from sub_o in joined.DefaultIfEmpty()
            select new { p.id, p.name, other = sub_o }
            into p
            orderby p.other.name descending
            select p;

请求发送到服务器:

[ 
  { "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
  { "$lookup" : {
    "from" : "others",
    "localField" : "_id",
    "foreignField" : "entity",
    "as" : "joined"
  } },
  { "$unwind" : { 
    "path" : "$joined", "preserveNullAndEmptyArrays" : true
  } }, 
  { "$project" : { 
    "id" : "$_id",
    "name" : "$name",
    "other" : "$joined",
    "_id" : 0
  } }, 
  { "$sort" : { "other.name" : -1 } }
]

实际上是在使用 "优化合并" 形式.译者还是坚持要加一个$project 因为我们需要中间的 select 来使语句有效.

Which actually is using the "optimized coalescence" form. The translator still insists on adding a $project since we need the intermediate select in order to make the statement valid.

因此，有很多方法可以基本上得出具有完全相同结果的基本相同查询语句.虽然您可以"将 JSON 解析为 BsonDocument 表单并将其提供给流畅的 Aggregate() 命令，但通常最好使用自然构建器或 LINQ 接口，因为它们轻松映射到同一个语句.

So there are quite a few ways to essentially arrive at what is basically the same query statement with exactly the same results. Whilst you "could" parse the JSON to BsonDocument form and feed this to the fluent Aggregate() command, it's generally better to use the natural builders or the LINQ interfaces as they do easily map onto the same statement.

$unwind 主要显示，因为即使使用单一"匹配，合并"形式实际上比使用 $arrayElemAt 获取第一个"数组元素.考虑到 BSON 限制之类的事情，这甚至变得更加重要，其中 $lookup 目标数组可能会导致父文档超过 16MB，而无需进一步过滤.Aggregate $lookup 匹配管道中文档的总大小超过了最大文档大小上的另一篇文章我实际上讨论了如何避免使用此类选项或其他 Lookup() 语法仅在此时可用于流畅界面的限制.

The options with $unwind are largely shown because even with a "singular" match that "coalescence" form is actually far more optimal then using $arrayElemAt to take the "first" array element. This even becomes more important with considerations of things like the BSON Limit where the $lookup target array could cause the parent document to exceed 16MB without further filtering. There is another post here on Aggregate $lookup Total size of documents in matching pipeline exceeds maximum document size where I actually discuss how to avoid that limit being hit by using such options or other Lookup() syntax available to the fluent interface only at this time.

这篇关于使用 C# 聚合 $lookup的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！